Category Archives: Copying and Moving

Go To Special

The Special Uses of Go To Special in Excel

Hidden on the Home ribbon in Microsoft Excel, within the Find & Select Button, is an Excel feature that has some very special and generally unknown uses for the CPA.  The feature is Go To Special.  It’s the fourth option on the list when the Find & Select button is clicked.
Go To Special

More…

Copying Only Visible Cells

Go To Special

Have you ever subtotaled data or produced a pivot table and wanted to use the summarized information for further calculations?  Normally if you copy filtered or subtotaled data, all the data, including the hidden rows, is copied.There is an easy way  around this. It’s in the Go To Special feature of Excel and the option is labeled Visible cells only.
Assume that you have the following simplified data:
fig-1
First, use subtotal to accumulate the sum of sales by product line. After sorting the data by product line and using the subtotal feature, the following is obtained:
fig-2As you can see, the subtotaled data hides some columns. Continuing to work with the subtotaled data may cause a mess, especially if you want to create a new subtotal.
An easy way around this issue is to copy the data. If a basic copy/paste was performed in an area that wasn’t within the same set of rows on the worksheet or was on another worksheet, you would have all the detail data rather than just the summarized data. However, you can avoid this by using the Go To Special feature and the Visible cells only option.

First, select the subtotaled data. fig -3

Next, click on Find & Select in the Home ribbon and select Go To Special.

Go To Special dialog box

 

 

 

 

 

When Go To Special has been selected, a dialog box appears with more options.
go special dialog box

Select Visible cells only, then click OK.

result of using visible cells only

Notice that each cell now appears to be highlighted by a box. Right click anywhere in the area and select copy. The box highlights around each cell appear as if they’re moving. Right click on cell A20 and select Paste Special then Values (This is important. It tells Excel to only paste the values in the cells rather than the formulas.)

paste optionsAfter the Values option has been selected from Paste Special, the summarized data, without all the detail, is now available to work with.

 

excel worksheet result

 

I use this technique a lot with pivot tables. Many times, I want to reference a summarized column of information on a pivot table for subsequent calculations. This can be done with pivot table data. However, if the pivot table is large and you want to copy formulas involving pivot table data, it’s not always easy as pivot table data contains absolute cell references (eg. Copying a formula down that contains the first piece of summarized information in a pivot table will continue to only use the data in that first cell.)
So, if I want to work with pivot table data, I generally isolate the visible cells using Go To Special, then copy only the visible cells and use Paste special-Values to copy the data to another worksheet and perform further analysis.

Flash Fill – Leaves Text Functions in the Dust!

flash fill drop down icon

FLASH FILL FEATURE

Excel 2013 has a cool feature that will save a lot of time for those of you that are always using Text functions such as =LEFT and =RIGHT to extract information.
Flash Fill can recognize patterns and extract the information you need with a click.
For example, in the worksheet below I have Employee Name concatenated together in one cell. If I wanted to extract Last Name from Column B  I would have to nest the text function with at least one other function as all the entries are of different lengths. With Flash Fill all I had to do was type the last name Harris into cell C2 and then select C2 through C5.
flash fill example -#1
I then clicked on Fill, in the Edit Group, and selected Flash Fill.
Excel filled in C3 through C5 with the same pattern as in C2 which was the last name of the employees.
In G2, I typed 2013 as I want to only display year from the data in Column F. How would you finish it? Select G2 through G5 and then select Fill>Flash Fill and the years will display in G2 through G5.
You can also undo it or take Excel’s suggestions if it does not follow the pattern as you select by clicking on the Flash icon that displays beside the new data. In the screenshot below you can see the Flash icon at G3.flash fill - undo

Grouping Data by Date

calendarTechniques for Grouping Data by Date in Excel

If you have a large data set that includes transaction dates, you may want to aggregate amounts by certain dates.  The problem is that you have a separate date for each day of the year.  This makes any meaningful analysis difficult.
It would help if just the month or year could be extracted so that data could be accumulated.  Well, there are functions for that.  There’s a year function, a month function and a day function that will extract the year, month and day from a date.
The syntax is the same for all functions.  Using =year as an example, the syntax is =year(cell with date).  The example below uses 7/15/2015. More….

 

Only Copy Cells with Data

A Method to Copy Only Cells with Data

Have you ever had a couple of columns of data that you wanted to combine into one column?  What do you do when there are blank spaces in some of the columns of data?  Many will cut and paste the data from one column to the other, then manually delete the rows containing blanks.  This begs the question; Is there an easier way?
One way is to use the GoTo Special feature in Excel.  This feature is located in the Home menu, under Find & Select on the far right hand side of the toolbar.jfog-1a

 

As an example, assume that the following data in in Excel and you want it in one column for analysis:
jfig2

Let’s copy only the cells that are not blank from B1:B7 to column A, under A7.
Select cells B1:B7
Click Find & Select and choose GoTo Special.

This menu will appear.

go to special dialog box

 

Select the radio button next to Constants and press OK.

 

Notice how only the cells with data are now shaded.  Hover over any of the three shaded cells and right click.  Select Copy.  Then, select A8, right click and select Paste.

Only the non-blank cells are now pasted into column A.

This technique can save quite a bit of time if you’re working with a large amount of data that requires manipulation into one column.  It also works with any data that needs to be moved and contains unneeded blanks.

  • “The course on ethics was one of the best correspondence courses I ever used.  It was well organized and the examples used were extremely helpful.”

    - Tommy R.

  • Great course! Price was right and the material was very thorough.

    - Alex

  • ” This was my first experience with CPASelfStudy.com and I was very impressed. I will definitely consider taking CPE through CPASelfStudy.com in the future.”   12/2017

    - Christopher Q.