Category Archives: Worksheet Design

Sort Left to Right

Who knew how many different ways there are to sort?

Let’s talk about changing the Sort Option Orientation.

Typically you don’t have to change the orientation of sorting as the default works just fine. However, everyone in awhile, you have that one spreadsheet that has a mind of its own.
I have a sales report that is generated every month and the columns are never in the correct order. So, I would cut and paste every month to get everything to display the way I wanted, however, it was  very time-consuming and annoying.

Below are the steps to do this in a more efficient manner and forget all about that cutting and pasting.

Insert a column above your column headings and number them in the sequence you want them to appear.
Then select Data>Sort.
In the Sort Dialog box click Options and select Sort left to right.
Voila- your columns are sorted in the way you want them and it took you all of 30 seconds.

sort left to right

The default order is Smallest to largest.

Notice that you can also sort by Case Sensitivity as well.

This is a tip from my Must Know Excel Tips Tricks and Tools for CPAs course.

Dashboard Design

I just finished updating my Excel Dashboards course, Drive Your Dashboard with Excel,  and thought I would share an excerpt.

Dashboard Design

Creating a dashboard is NOT the same things as creating an Excel spreadsheet. By this, I mean that people tend to open an Excel spreadsheet and just start entering data. They worry about formatting and layout later – if at all.  (No, I am not talking about you.)
This is not true of creating a dashboard. The design of the dashboard is key.  Now, don’t roll your eyes. If you don’t know who your audience is and what the purpose of the dashboard is, it will not be successful. You will have simply wasted your time and probably confused your audience.

 To design a dashboard properly you need to know:

  • Who will be viewing the dashboard?
  • Who will be using the dashboard?
    • Viewers and Users may have different purposes
  • What they want to dashboard for – what measurements?
  • What metrics do you want to track?
  • What do users want to learn from dashboard/ how do they plan to use it?
    • High Level overview or detailed operational needs

Once you understand who your audience is and why you are designing it, you need to consider:

  • Where is the data located?
  • How is the data going to be retrieved?
  • Who will maintain and update the original data?
  • Who is going to update the dashboard’s data once it is created?
  • How should the dashboard be structured?

Design Layout

KISS- Everyone knows the Rule – Keep It Simple….

Dashboards, with their colors and graphics, usually resemble a magazine layout. Appearance is important but don’t get so focused on the graphics and layout that you lose the data.  This is becoming more and more important as dashboards gain in popularity and everyone is trying to make them look so pretty.
Edward Tufte, the acknowledged “founder” of dashboards, discusses basic techniques for improving the display of quantitative information. He has an interesting website which can be found at http://www.edwardtufte.

Below are some basic rules to keep in mind as you design a dashboard:

  • Plan and design a mockup so that you know what you are doing with your space.
  • If there is too much data, it is difficult to see what is really going on so keep it clean and simple.
  • Do not use a lot of different colors.
  • Do not use a lot of different fonts.
  • Format consistently – For example, do not use accounting format in one section and currency format in another section.
  • Select a single background color for charts or make the chart areas transparent.
  • Use custom formats for large numbers and/or abbreviate where possible.
  • Use white space to indicate sections to create the idea of more space (no borders).
  • Only include needed graphics such as charts.
  • Don’t get cute- it is not a PowerPoint presentation.
  • Generally, the top of your dashboard will contain overview information and more specific information should be found underneath it.

Dashboard Structure

dashboard design

Basically, you need to break your dashboard area into sections.
At an absolute minimum, you should have 3 sections in your workbook/files:

1.Data/Input Section

  • The Data section is where the raw data is. If you are importing from other programs, such as Access or QuickBooks, this is where you would want the data to reside

2. Analysis Section

  • The Analysis section contains the formulas, which pull the data from the Input section, and organizes it so that it becomes information. This is the section that will feed the Presentation section.

3.Dashboard (Presentation) Section

  • The Presentation section pulls and/or reshapes the key information from the Analysis section into the actual Dashboard.

Other sections to consider adding include:

  • Control Section
    • Many people recommend having a Control section or sheet that would contain informative that would be used multiple times in a dashboardsuch as listing of dates, listing of products or sales regions. In other words, data that you could reference or link to multiple times.
  • Help Section
    • Some people recommend having a Help section or sheet, which can be very useful, if one or more people are maintaining the dashboard You can also include comments and explanations of calculations there as well. You may think you will remember why you put a formula together a certain way, however, there is a good chance that you may forget what your reasoning was a year later.
  • Table of Contents or Reports Page
    • A Table of Contents can be very useful if you are using a lot of worksheets. You may also want to consider having a Reports Page and then pull the Dashboard from that page.   Make sure your set-up allows for flexibility.

For example, your raw data section may contain Sales by month by sales person by product for last year and this year as well as budget numbers.  In the Analysis section, you may have created a pivot table that summarizes sales by product for the month and quarter. The presentation (dashboard) section may display the quarterly sales by product as compared to budget or last year.

Keeping all the data together in one workbook is obviously the easiest method; however, these sections can all be in separate workbooks depending upon your preference and the volume of data and analysis. This is one of the reasons that preplanning and layout are so important. You don’t want to be in the middle of your creation and suddenly realize that it won’t work or that you forgot an important component that now needs to be incorporated in somehow.

When I reread this section, I realized that most of the information included here was also included in a course I wrote earlier on designing a database.  I cannot emphasize enough that preplanning and design are the most important aspects of a dashboard.  If you don’t know who your audience is and what they want the information for then you have just wasted a lot of your time as well as theirs.

Worksheet Design Tips

Excel Tips on Entering Data and Formatting

Brian Cane’s Excel Tips show how to effectively enter information and to format more effectively.
His source – a public domain Ibbotson (the famous Nobel Laureate) workbook. Please click on the attached sheet to view.
A go-with Tip1 is when a formula references the cell above.
So the cell above is the header and causes the formula to #error! and that is why users have a different formula for the first cell in the column. In my example tip1 in the prior attachment I show how to deal with the situation when the general formula requires a 1. When the formula needs a zero (most often the case) then use the N function.

So if for example you have a common BoY to EoY (beginning of year in say column B to end of year in column F) progression where you code B2=0 and B3=F2 and fill down because coding B2=F1 will give a #Value!. But if you code B2=N(F1) you can fill that down because N(“EoY”) will return 0 and N(any number) will return the number. Thus avoiding different row1 formulas.
Thanks Brian for the tips.
I would like to add to Brian’s tips and emphasize that you should always check your formulas and make sure they are consistent and correct.
If you make the first entry a different formula, it is easy to forget that and you may try to copy that first formula down at a later point in time which could result in an incorrect answer. People sharing your worksheet may copy or use the original formula not realizing that it changes after the first entry.  Another problem is that people frequently create a formula, copy it  down and never check any of the other entries to make sure they are correct. Often- due to a lack of absolute references the formula in the third or fourth row may be incorrect even though the original formula in the first row is correct.- This happens to a lot of people using Vlookup tables.  Also, sometimes people find an error and fix that cell but forget to go back to the original cell and fix the problem there and recopy it. Finally, hard coding numbers in can be problematic as those numbers might change.
It is good worksheet design to make sure that formulas are correct and consistent and that cell references are used  instead of hard coding numbers into formulas.

Formatting Alternate Rows

Formatting alternate Rows

Thanks to Diana Lawless for sending in this Excel Format painter icontip.

Using the Shift key and the Format Painter icon together allows you to format an entire worksheet quickly. If you are formatting alternate rows or columns quickly and do want not mess around with Conditional formatting rules this is a great shortcut.
Diana wanted to format every other row of her 1,000+ row worksheet with a variety of formats to enhance readability.

To change formatting of the spreadsheet:

  • Format line(s) as needed
  • Highlight formatted line(s)
  • Left click Format Painter (paintbrush)
  • Hold ‘Shift’ key
  • Tap ‘End’ key
  • Tap down arrow key
  • Release ‘Shift’ key
    And voila! The formatting was automatically copied to every line in the spreadsheet.
    If you have inserted new rows in an already formatted worksheet, this is a really quick way to fix any formatting problems.

Conditional Formatting and =MOD()

An alternative method, as mentioned above, is to use conditional formatting. If you use conditional formatting and the =MOD() function it is a quick way to create a general ledger type of effect with rows displayed in alternate colors.

Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want – just make sure that it is light enough that that the data can be seen through it.

Formatting dialog box

Formatting dialog box

1. Select your data

2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format.
7. Select a light green fill – this is usually associate with accounting general ledgers.
8. Click OK.
9. Click OK.

It should look something like the image below. It really does improve readability.

general ledger formatingt with =MOD

general ledger formatingt with =MOD

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

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

    - Alex

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom