Select All Cells

There are times when you want to select all the cells in a worksheet. Perhaps you want to change the font or font color. Perhaps you want to copy all the cells. Well there is an easy way to do this. 

Clicking the small grey bot in the top left corner of the sheet will select all the cells in the worksheet

This box is located right above the row numbers and to the left of the column letters. You can also use CTRL + A to accomplish the same result.

Cell References – Rename a Cell or Range

Large worksheets can be difficult to navigate. This is especially true when there are multiple tabs and formulas. It does not help that Excel defaults cell names as A1 or Q17. That’s not very intuitive. So you’re now thinking that there has got to be a better way. And you would be correct.

So today I am going to take a page out of my book (shameless plug) on cell references and how to rename a cell.

Brown Bag Workshop’s Pocket Guide

Top Excel 2007 Skills You Need To Succeed

When you look at most formulas we create, such as =E2-H2 you will notice that it is not reader friendly.

Fortunately you can assign easy names to individual cells or even a range of cells. This makes it easier to understand calculations.

Think about which formula is easier to understand…

=ThisYearProfit – LastYearProfit  or  =E2 – H2.

To convert cell reference(s) into easy to read names start by selecting the cell you want to rename, in this example cell B1. Then mouse click on the cell name box and change B1 to TotalSales.

Now you can reference the cell B1 in formulas as TotalSales.

Example:

=(.08 * TotalSales) instead of =(.08 * B1).

There are some rules to be aware of when naming cells. For instance you cannot use a blank space between words. If you do you will receive an error.

To accommodate this, use a – or _ or just type the words together without spaces.

Total-Sales       Total_Sales      TotalSales.

You can also name a range of cells for future reference in calculations.

Just as before select the range of cells you want to name. In this example I have highlighted cells B2 through B16.

Next select the cell name box and type Items. 

Now if you click on any one of the cells inside your named range, Excel will return the cell name. However, if you select the entire range, Excel will show Items in the cell name box.

You can also go to an assigned name range by pressing the F5 key.

If you want to delete a Name, go to Formulas then select Name Manager. This will pull up your defined names. Just select the name and then click on the Delete button.

So now you know. Try using Cell and Range names to make your worksheets Wow you clients.

Tornado Chart

Let's have some fun today.

Here I have a sample of inventory of items that you would have on your desk. I have created a tornado chart by sorting the items by inventory largest first. I then use the =REPT function in combination with the pipe character on the keyboard to create the tornado chart. To finish the graphic I simply change the font to Playbill and center the column.

Take a look at the formulas and see if you can recreate this simple graph technique.
Play with the resizing factor in column I and see what it does to the graph. This resizing factor allows you to scale your chart to your data.

Note: The font in Column C is Playbill

Whiteboarding a Dashboard

Today I want to take some time to examine dashboards and the role white boarding plays when developing a dashboard.

Dashboards are one of the areas where Excel shines. However a poorly planned dashboard is at best inefficient; at worst, wrong or unable to meet the minimum requirements for the dashboard.

Fortunately we can alleviate most of this by planning what the dashboard will accomplish and then by coding what we planned.

Many people will rush into the design of the dashboard without logically thinking through the separate elements of a dashboard and integrating them into a seamless design.

This is where white boarding comes into play.

Spending some time drawing and arranging / rearranging the elements of the dashboard on a whiteboard will help the overall continuity of the design.

Now there are many theories as to what comprises the best dashboard elements.

Some will advise using a heat map similar to web site development. What’s a heat map? Well web developers use heat maps to determine what parts of their websites a reader focuses upon. This helps them optimize content and advertising. Then again, others will argue that heat maps are not valid with dashboards.

Most will agree that a unified color scheme is a necessary element of a dashboard (as is standardized font types and sizes). Documentation of all formulas, assumptions and instructions on how to use the dashboard are also necessary.

Here is an example of a final whiteboard that I created for the ACMC Company and the finished result to the right (now don’t nominate me for a design award based on my excellent drawing skills).

White boarding a design does not need to be fancy; it just needs to provide clarity to a layout. It allows you to think through the design process. Indeed the majority of the time you will spend creating a dashboard will be the preliminary work. Identify what the dashboard will accomplish. Targeting the source data and developing the methods required to parse out the data into logical components.

Finally even though I advised to plan what you are going to code then code what you planned. Don’t get locked into a concept. You may find that when you actually create the dashboard that re arranging components may make sense when conveying the data or end up move visually appealing. You may notice that in my above whiteboard I had supporting data at the bottom. However, in this example it proved redundant and just cluttered up the design, elongating the dashboard forcing it to not fit all on one screen (causing the user to have to scroll to see it). So it was removed.

So how do you plan your dashboards? What tips can you pass along to help others “Wow” their co workers?