Tag Archives: Excel 2016

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




The AGGREGATE Function

The AGGREGATE function is a very powerful function for summarizing an array of data.
It combines 19 different ways to summarize data into one function!
While it is a powerful function, my favorite reason for using AGGREGATE is its ability to SUM data with errors. 

The syntax for the AGGREGATE function is =AGGREGATE(function #, options, array).
The function #’s are listed below:

aggregate function



















As you can see, your options permit you to ignore nested subtotals, hidden rows as well as error values.
One of my biggest pet peeves is trying to sum data that has error values.  The AGGREGATE function makes this easy.
Assume yousum column with errors have data in cell A1:A6, and that you want to sum it.
However, because there is an error value in A5, the SUM function will return an error as shown in the screenshot on the left.
You can easily work around this by using the AGGREGATE function.



In this example, I clicked in A7 and  typed =AGGREGATE(

  • A list of the various function #’s appears.  Note that if you elect to use the Insert Function dialog box, this handy list of functions does not appear.  We want to SUM the data, so function # 9 should be used, followed by a comma.



  • After the comma, a list of options is presented.
  • AGGREGATE DIALOG BOX OF OPTIONSIn this case, use option number 6, Ignore error values and place a comma after this.





  • Lastly, identify the array to be summed.  This will be accomplished by using the cursor to highlight A1:A6.
  • Close the parenthesis.
  • Press Enter.








The “Must Know” component of the AGGREGATE function is how to SUM data with errors.  It is a very powerful function, though.  You may find other uses for it in your professional assignments.


This is an excerpt from “Must Know Functions for CPAs”.

  • “Excellent Regulatory Review Course. The self-study format (E-Book) provided an excellent overview of the regulatory requirements for California CPAs. I will be using the E-Book as  a reference material. Thank you for providing a very comprehensive yet affordable option for this required CPE.”

    - Constance

  • ” 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.

  • I loved the course as it had current cases which I am familiar with. Best ethics course I have taken in recent memory. Thanks!”

    - Kathy