Selecting Special Cells

How to only select “special” cells.

Do you ever have a large chunk of data with random blanks that you want to fill out?  After filtering, do you ever want to just copy the filtered data and paste it elsewhere?  Or, if you just want to select the cells that contain formulas within a larger group of cell values?  Performing these functions is actually quite simple.  Just highlight all your data, then click Edit>Go To>Special (F5>Special).  After hitting special, you get a menu that allows you to specify the types of cells within your range you want to select.  

Blank Cells
For example, if you are only interested in the blank cells, then check off “Blanks”.  If you wanted to fill all the blank cells with a formula or some value, then make sure you copy that formula or value beforehand, then select all the blank cells, then paste.  

Visible Cells
If you wanted to copy all the filtered data and skip all the other data in between, first filter out your data accordingly with a filter.  Then select the data you want to copy.  Then go to Edit>Go To>Special.  Then check off “Visible cells only”.  This will ensure that only the visible part of the filter is selected.  Then copy and paste to wherever you want it.

Other Special Cells
As you have probably already seen, you have many options to choose from when selecting special cells.  You can select only the current array, cells with conditional formatting, row and column differences, cells with constants, cells with comments, etc.  Feel free to play around with selecting various cells.

Special Uses for Special Cells
You may ask why we need to select special cells.  Sometimes when I look at a huge spreadsheet of numbers, there may be a lot of blanks that represent 0s.  Suppose I want those blanks to appear as 0s.  Then I could use the Go To Special menu.  Suppose I want to quickly take snapshots of different cuts of data.  I can filter accordingly and use the Go To Special menu to select visible cells only and paste them into another location. The Go To Special menu can come in surprisingly handy in many cases. Always keep in mind that it is available.

Quick, check your work

Quick, Check Your Work

After I’m done collecting data and inputting all the necessary formulas, or after building out some type of exhibit, it is best to check my work.  A quick way to spot check whether or not all your formulas and data are flowing through is with the status bar at the bottom right of the screen.  If you highlight a group of cells with numbers, the status bar in Excel 2010 will give the average, count, and sum of those numbers (below).  In Excel 2003, only one of those items is given.  To switch the item that is displayed, simply right click on the status bar and check off that item. 

The status bar can save the user a lot of time by quickly running calculations without the use of formulas.  For example, if you had the exhibit below:

You can quickly check to see if the total compensation is being summed up correctly by highlighting (CTRL+Click) each of the elements that make up Total Compensation (Salary, Bonus, Stock), then looking at the sum that is calculated in the status bar.  In this case, Total Compensation is not being summed up correctly.

Sometimes, you may want to quickly run summary statistics or check existing summary statistics on a group of data.  Below, I have an example.

You can quickly tell whether or not the Count, Average, Max, or Min is correct by highlighting the cells in a column and checking the status bar.  If you wanted find out the average profit for the East region, simply highlight all the cells corresponding to profit in the East region and check the status bar.

The status bar can be a real time saver for checking your work or running quick calculations.  I encourage using the status bar as much as possible in your day-to-day tasks, whether for responding to a request from your supervisor about calculations or checking a few exhibits before they get sent out.

Call me superficial, but aesthetics do matter (part 2)

Formatting – call me superficial, but aesthetics do matter (part 2)

Last week, I focused on spreadsheet aesthetics pertaining to client-ready exhibits and reports for upper management.  However, aesthetics should also be kept in mind when producing work to be used by other analysts.  Whenever I work on spreadsheets with a lot of data, there are certain things I like to do to make it easier for other analysts to read, understand, and manipulate my work.

Bold Your Headings/Titles

Bolding the titles at the top of your spreadsheet makes it easier for the reader to recognize what the data means.

Don’t Merge Cells

Merging cells makes it more difficult to add or remove columns.

Group Instead of Hide

If you hide certain rows or columns, you may forget they are there.  Grouping them makes it easier to see what you have hidden, and to ungroup when you need those columns/rows.

Don’t Use Formulas that Reference Across Rows

If you have data broken out by rows, it is very easy to low track of formulas that reference across rows.  Once you start copying, cutting, and pasting across rows, the formulas can easily get mixed up.  Stick to formulas that stay within the same row.

Add in a Filter

Filters are a quick way for the user to look at different cuts of the data.

Sort

When you are done with your data, sort it in a meaningful way (alphabetical, by revenue size, by region, etc).

Right Align Numbers and Left Align Text

I think it is much easier to read numbers and text when they are consistently aligned.

Format Data Types

Currency should be formatted as currency, text as text, numbers as numbers etc.

Highlight or Use Red for Hard Coding

Whenever you need to hard code a value that would otherwise be a formula, make sure you mark it for future reference.

Use Comments

Other users can’t read your brilliant mind, use comments so they understand.

Use Borders

Borders below titles make it easier for the user to differentiate between the titles and data.  Borders between sections of data may be helpful to categorize your data.  Borders between cells may make printouts easier to read.

Freeze Panes

Add in freeze panes at the top or left to make it easier for the user to scroll and peruse your spreadsheet.