Category Archives: Miscellaneous Excel Tips

Find Files Easily

Are you an organized person? Is everything at your fingertips? If so, good for you. I, unfortunately, am a bit more unorganized and can spend quite a bit of time looking for a specific Excel file. I have found that adding keywords to a file when I create it, can help me locate it later if I don’t remember the name file. It is very easy to do.

use keywords

 

 

Click on File and then select Info.properties
Click the Properties drop-down arrow located over on the far right side of the screen and then select Advanced Properties.

Click on the Summary Tab if necessary and enter keywords that relate to the file.  Click OK.

keywords in file

 

 

 

 

 

 

 

 

 

 

 

 

To search, simply go to the Windows Explorer window and in the search box, located on the far right of the window, type in one of your keywords.  Your file should display. You can also search on author and title as well.

search with keywords

Lots of options to find your file.

SUBTOTAL() and AGGREGATE() -TimeSavers

subtotal and aggregate - time saversMany people are only aware of the Subtotal icon and have never looked at the Subtotal function itself. After all, the icon, lets you add, average and count a list so who needs more? Well if you are one of the people who subtotal data a lot then you may want to look at the Subtotal function or the AGGREGATE() function.

Let’s start with SUBTOTAL().
There are 3 advantages of using the SUBTOTAL function rather than the icon.

  • First, the subtotal function allows you to display the subtotal wherever you want it to display on the worksheet rather than in the list itself.
  • Second, you can specify if you want Excel to subtotal all of the data in the list, even the hidden values, or to only subtotal the visible data in the list. That is powerful.

The syntax for SUBTOTAL is =SUBTOTAL(function_num, ref1,[ref2]…). The function_num indicates the mathematical operation you want Excel to do. For example, =Subtotal(1,B2:B50) tells Excel to Average the data in cells B2 through B50. Apparently the programmers sequenced the function_nums alphabetically rather than on frequency as you would normally expect that 1 would be to add – or at least I did. Anyway, I digress. 1 means to average, 2 to count and 9 means to sum.  By using the single digit function_number, Excel automatically includes all hidden rows and columns in the list.  If you use the corresponding 3 digit function_number, Excel subtotals only the visible list in the data. So, the function_num 1 includes the hidden rows, while 101 excludes them.  Below is a list of the most common function_num:

subtotal()

  • The third advantage of the Subtotal function is that it excludes other subtotals. So, for example, if you had a monthly report that has a lot of report sections such as Sales, Cost of Goods Sold, Operating Expenses, Non-Operating Gains(Losses) , typically you add up each grouping and then create a grand total. If you used =SUBTOTAL() on each of these groupings than all you need to do is use =SUBTOTAL() at the end of the report to get a grand total because SUBTOTAL() does not include subtotals when it totals!  How cool is that?  This is also a time saver if you need to create a new section for something new within the report as the grand total will automatically update if =SUBTOTAL() was used.

AGGREGATE()

If you are excited about =SUBTOTAL() then check out =AGGREGATE() if you have Excel 2010 or higher. AGGREGATE() does everything that SUBTOTAL() does and more. While =SUBTOTAL() offers 11 functions, AGGREGATE() offers 19 functions. Some of the more useful new functions include SMALL() and LARGE(). In addition to offering you the option of ignoring hidden rows of data, it also allows you the option of ignoring  error values and/or subtotals.  This is key – how often have you tried to sum a column of data that has a #Div/0! Or a #N/A? Well, if you use AGGREGATE() you will actually get an answer instead of an error message in the total line.

The syntax of AGGREGATE() is similar to SUBTOTAL() but gives you more control over what you are totaling so it is more powerful.. It is AGGREGATE(function_num, options, ref1,[ref2],..]. A function_num of 1 still means Average while a 9 means Sum. The difference here is the options section. The option number tells Excel which values to ignore in the data list you are testing.

AGGREGATE()

 

 

 

 

 

 

SUMMARY

So, why didn’t I lead with AGGREGATE()? I started with =SUBTOTAL() because a lot of people are familiar with the Subtotal Icon and it is a bit simpler. However, if you are a manager of lots of data, I suggest you jump over SUBTOTAL() and use AGGREGATE().
Below is a simple example comparing SUM(), SUBTOTAL(), AGGREGATE().

Subtotal() aggregate()

 

 

 

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.

Using TRUNC()

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs.

TRUNC()
No Decimals
TRUN - no pennies

If you don’t want the pennies in your calculation you can use =TRUNC(), short for TRUNCATE, or =INT(), short for INTEGER,  to turn your data into integers.

 
The two functions work differently but return the same value when working with positive numbers. You will get different answers from these two functions with negative numbers. TRUNC() removes the fractional part of the number whereas INT() rounds down to the nearest integer based on the value of the fractional part.

In the screen shot below, -6.45 returns a different value using INT as it -7 is the lower value.

TRUNC

 

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.

X Axis Units in Excel Charts

X Axis Units- Excel Charts

I thought I would share this problem I ran into charting so that you don’t start banging your head against the wall as I considered doing.
I have used this same data in a many different versions of Excel and never had a problem until Excel 2016.
I charted some data and I went merrily on my way until at some point I noticed big problems with my X axis.
Do you see the problem? Take a look at the X axis. At first glance, it looks okay and then… hmm.. issues here.

Excel 2016, by default, apparently uses days so it is showing the first day of the month.
Unfortunately, most businesses are more interested in the last day of the month.chart units - dates

To fix this, right-click on the X axis and select Format Axis and change the base from Months to Days.
x Axis - format axis

Your chart should now correctly reflect the data from Column A.

revised chart - x axis

 

 

  • Thank you Patricia. I really love your courses. I learned so much about excel this weekend!”

    - Connie K.

  • Thank you for providing very informative CPEs. I have enjoyed your courses for years now. Yours is truly my go-to CPE sources.

    - Lily

  • “I am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

    - Janet