Jon Acampora - 14/16 - Excel Campus
Jon Acampora

Author Archives: Jon Acampora

4

Review of the Excel Dashboard Course from My Online Training Hub

Are you interested in learning how to improve your charting skills and create world class dashboards in Excel?

My friends Phil and Mynda over at My Online Training Hub have just opened a new session of their highly acclaimed Excel dashboard course, and enrollment is only available for a limited time.

I have personally paid for and taken this course on my own, and believe it is outstanding. I am a firm believer in continually learning and developing my Excel skills, and I feel that this course was a great investment for me.

Video Review of the Course

Checkout my video review of the course below.  I take you into the members area of the course to show you exactly what you get.  I also explain all the benefits of the course and how it helped me.

Why are Dashboards Important to Learn?

The amount of data we collect is growing at a very fast rate, and organizations are looking for employees that have the ability to analyze and present this data in a clear and simple manner. As an Excel user it is extremely important that you know how to utilize the charting functions that Excel offers.  The default charts in Excel don’t always make this easy and there is quite a bit of skill and knowledge required to create a successful chart or dashboard.

The Dashboard Course will help make you an expert at working with charts and creating interactive dashboards that will impress your boss, colleagues, and audience.

What’s Included in the Dashboard Course?

The course is a self-paced online course with videos and sample workbooks to download.  You will get access to over 10 hours of video!  It’s a huge amount of training material, but don’t let this overwhelm you.  The videos are divided by topic and each video is around 5 minutes or less.  The course syllabus is organized nicely, making it easy for you to find a video on a specific topic.  You can watch a few videos during your lunch break, and become an Excel and dashboard wizard in just a few weeks.

The 10 hours of video includes 6+ hours on dashboards and 3+ hours on general Excel topics that will help you build a foundation for the more advanced dashboard techniques.

There are many great benefits to taking this course, and you can read all the details at the following link.

My Favorite Features of the Course

I have taken quite a few different Excel courses, and here are some of the reasons the course from My Online Training Hub stood out to me.

  • 10 hours of video that is divided into small 5 minute increments by topic.  This allows you to focus on learning and mastering one topic at a time.  Long videos can drag on and lose your attention quickly, but Mynda’s videos are quick and to the point.
  • Watch the videos any time and in any order you chose.  You might want to watch a video on a specific topic for a project you are working on, and the course syllabus makes it easy to see all the topics that are included in the course.
  • Excel workbook files are included with each topic so you can follow along and immediately apply what you’ve learned.  This gives you a true hands-on experience in an online course.
  • Learn advanced formulas and functions needed to organize your data for many types of charts.  The foundation of any good chart is its data, and Mynda does an excellent job of explaining some of the advanced techniques needed to create charts that will display your findings.
  • Professional quality throughout the entire course keeps you focused on learning.  Having produced online content myself, I have a great appreciation for how much hard work and attention to detail goes into this course.  It is really well organized and contains some of the highest quality content I’ve seen in online training courses.
  • Learn how to structure your data for use in charts.  If you read my last post on structuring data for pivot tables, you know that it is extremely important to understand how to structure your data before you can analyze it properly in Excel.  The same methodology applies with creating charts, and can get even more complex.  Mynda does an excellent job of explaining this throughout the course with various types of charts.

No matter what you current Excel skill level is, I believe you will find this course extremely beneficial for your career.  Personally, I consider myself to be at an advanced level, but I definitely don’t know it all.  I take every opportunity I can to develop my skills and learn.  I’m helping promote this course because I believe it is well worth your money and will provide a tremendous value to you.

FREE Bonus Just For You

Right now I’m giving away my PivotPal Add-in as a free bonus when you register for the course. That’s a $37 value.

PivotPal will help you work with pivot tables and save you time when creating dashboards.  You will also get my video series on how to create interactive dashboards using Power Query.

So there are a ton of great bonuses for you!

To get the bonuses:

  1. Click this link to go to the Dashboard Course registration page.
  2. Select one of the course bundles.
  3. At checkout, enter the promotion code: tabs
  4. I will send you PivotPal and the bonuses a few days after you register.

Key dates for the course:

  • 20% Discount ends this Thursday, February 18th at 8pm PST
  • Registration closes Thursday, February 25th at 8pm PST

The course will be closing registration soon, so get signed up before it’s too late.  I also have an article where I answer some frequently asked questions about the course.

Excel Dasboard Course

Click Here to Register for the Dashboard Course

 

*I want to let you know that I am an affiliate partner with MyOnlineTrainingHub, which means I make a commission if you click the links above and purchase the course. I promote this course because I feel that it really helped me improve my Excel skills, and it’s an excellent value.

20

Quarter Sum Formulas Add-in

This post provides a solution for quickly creating formulas to sum monthly data into quarters and full year totals using a free add-in.

The Problem

When using the SUM function to sum monthly data into quarterly totals, you can NOT create the formula for the 1st quarter and then copy/drag the formula to the right.  

Quarter Formulas Incorrect with Drag Right

Click to Enlarge

The cell references will only move one column to the right instead of three columns.  If we copy the formula above for Q1 to the right, the resulting formula will give us the total for Feb-Apr.  The correct formula should be a total for Apr-Jun.

The Solution

The Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks.  And it’s FREE!

 Excel Quarter Formulas Demo

And it Gets FASTER!

You can add the Quarter Formulas button to the Quick Access Toolbar to launch it with a keyboard shortcut.  Then you will be creating all the formulas in under 2 seconds!

Quarter Formulas Button Keyboard Shortcut Demo

These articles will explain how to setup the Quick Access Toolbar (QAT) and use the QAT for keyboard shortcuts.

Benefits

  • Four quarter and full year SUM formulas created in 2 seconds.
  • No typing or complex formulas needed.
  • Creates easy-to-read SUM formulas.
  • Automatically determines 1st month cell when Q1 cell is selected.

Download

Quarter_Sum_Formulas_Add-in.zip (758.5 KB)

The add-in file and installation guide are included in the zip file.  The add-in is compatible with Excel 2007, 2010, 2013.  Please let me know if you are interested in a version compatible with Excel 2003.

Subscribe to our email newsletter below to stay updated with changes.

Related Help Articles

Instruction Guide

This add-in will save you lots of time when creating quarter and full year formulas, and it is very easy to use.

See the guide on how to install an add-in (included with download).  After the add-in is installed, you will see the Formula Tools menu on the Add-ins tab of the ribbon.

Quarter Formulas Ribbon Screenshot

The Formula Tools button is a drop-down menu that includes a button for the Quarter Formulas function.  More functions will be added to this menu in the future.  See below for instructions on how to setup a keyboard shortcut to launch the function.

Report Structure Requirements

The Quarter Formulas add-in requires all the month data be in 12 consecutive columns.  The four quarter and year total formulas will be created in five consecutive columns.  You will choose the starting cell (Q1) that the formulas will be created in.  The created formulas do NOT have to be next to the monthly data.

Quarter Formulas Report Structure Requirements

Creating the Formulas

  1. Click the Quarter Formulas Button.
  2. Select the 1st Quarter cell where you want the formulas to be created and click OK.
    – Formulas for Q2-Q4 and Full Year sum will be created to the right of this cell.
    – The selection defaults to the active (selected) cell in the worksheet.  Selecting the Q1 cell prior to clicking the Quarter Formulas button will save you a step.
  3. Select the cell that contains month 1 data and click OK.
    – This is the 1st month in the 1st quarter (January if you are on a calendar year).
    – The selection defaults to 12 cells to the left of the Q1 cell selected in the previous step.  If the quarterly total cells are directly to the right of the monthly data then you will not have to select the month 1 cell.  This will save you another step.
  4. The SUM formulas will be created in the Q1 cell and the four cells to the right of it.  
    – Copy the formulas down to fill all the rows in your report.

What do you think?

Please leave a comment with any questions or suggestions.

116

Keyboard Shortcuts to Change Font & Fill Color or Cell Style

Excel Font Fill Color Keyboard Shortcut KeysHave you been searching Google to find keyboard shortcuts to apply a font or fill color to a cell?

Well, you can stop your search… 🙂

I have spent a lot of time researching this topic as well.  This page will hopefully help you with some answers and solutions to this question.

No “Good” Built-in Shortcuts

Unfortunately, there are no built-in keyboard shortcuts in Excel for font or fill colors.  There are a few “workaround” methods that I explain in another blog post, 5 Excel Keyboard Shortcuts for Font & Fill Colors.  But these built-in methods are either slow, or do not provide a direct solution.

Keyboard Shortcuts for Font & Fill Colors

So I developed an add-in that allows you to create keyboard shortcuts to change the font & fill colors.  This add-in also lets you create keyboard shortcuts to apply other cell formatting properties like number formatting, borders, font size, protection alignment, etc.

Formatting Shortcuts Lite Excel Userform

The add-in is named “Formatting Shortcuts” and there is a free version available for download.

Click Here to Jump to Download Section

Time Saving Shortcuts

The Formatting Shortcuts add-in will save you a lot of time if you are currently using the mouse to format cells.  Again, this includes any type of formatting and is not just limited to font and fill colors.

The image below shows some common tasks that we all do in Excel to format cells.  It includes the buttons on the ribbon that we press over and over again to apply formatting._

Common Formatting Mouse Actions - Excel

The Formatting Shortcuts add-in will allow you to create keyboard shortcuts for any of these actions.

The keyboard shortcuts are quick and easy to press in one step.  This will save you a ton of time over having to navigate to the ribbon (toolbar) with the mouse.

Video Demo

Here is a video that explains how the add-in works.  You will see that it is fast to setup and easy to press the shortcut keys.

Click Here to Jump to Download Section

In the video above I demonstrate a few different ways to apply different formatting types with the custom keyboard shortcuts.

Keyboard Shortcuts Are EASY to Press

The keyboard shortcuts you create all start with Ctrl+Shift.  Then you get to choose the letter at the end of the key combination.

For example, in the screenshot below I have setup the shortcut Ctrl+Shift+S to apply the yellow fill color to a cell or range.

Formatting Shortcuts Lite Excel Userform

The shortcut Ctrl+Shift+S is very easy to press because the keys are close together and can all be pressed at one time with your left hand.

Keyboard Shortcut Diagram Ctrl+Shift+S Yellow Fill

This beats alternative methods of having to press Alt+H+H, then pressing the arrow keys to find the color on the palette.  That method is slow and difficult to perform.

But the Ctrl+Shift+{letter} shortcuts you use with the Formatting Shortcuts add-in are easy and efficient.  They will save you a lot of time.

Click Here to Jump to Download Section

Undo Your Mistakes

The Formatting Shortcuts add-in also allows you to undo your mistakes.  If you have ever recorded your own macro and assigned a shortcut key to it, you know that you lose the undo history when you press the shortcut key to run the macro.

This is not the case with the add-in.  The full version of the Formatting Shortcuts add-in allows you to retain the full Undo History in Excel.  So you can use Ctrl+Z or the Undo button if you accidentally press one of the shortcut keys.

The screencast below shows an example of this.  In this video I setup a shortcut key for a cell style that contains number formatting, borders, font, and fill colors.  I then use the keyboard shortcut Ctrl+Shift+A to apply the formatting to a few cells. Finally, I undo my actions using the Undo button.

 

Formatting Shortcuts Add-in Setup Demo

Click Here to Jump to Download Section

Features

Here are a few additional features of the add-in:

  1. Easy to Use – The shortcut keys and cell formatting can be changed with the click of a few buttons.
  2. Unlimited Options – Any combination of formatting properties can be applied with a keyboard shortcut.  This includes custom styles and all formatting options.
  3. Full Undo History – The undo history is retained so you can undo any changes made by the keyboard shortcuts.  If you have recorded or written your own formatting macros, you know that undo history is typically lost when you run a macro.  This add-in works around that to retain the undo history.  More details about undo on the help page.
  4. Save Time – Improve your efficiency and style worksheets in a fraction of the time it would take with mouse actions.
  5. Consistency – The add-in saves your styles so you can start using the keyboard shortcuts when you open a new workbook.  No setup needed (even for custom styles).  Using the same styles throughout your models will make it easier for users to read and understand.  Read more about styles on the help page._

Click Here to Jump to Download Section

Formatting Types

  1. Font Color – Applies the font color to the selected cell(s).
  2. Fill Color – Applies the fill color (cell shading or background) to the selected cell(s).
  3. Font+Fill Color – Applies both the font and fill color to the selected cell(s).
  4. Cell Styles (full version only) – Applies the cell style to the selected cell(s).  Formatting for cell styles include:
    – font color
    – fill color
    – text formatting (bold, italics, underline, etc.)
    – border properties (color, weight, line style, etc.)
    – alignment (left, right, centered, etc.)
    – cell protection
    – The image below shows the Cell Styles menu.  Cell styles can be added and modified (customized) in Excel.

    Excel Cells Styles Menu

    Click to Enlarge

    Click Here to Jump to Download Section

Versions

The add-in is available in two versions.  Both versions are compatible with Excel 2007, 2010, and 2013.

Lite Version

The lite version is free to download below and allows you to create up to three keyboard shortcuts for font color, fill color (cell background), or both font & fill color.  It has a single undo feature, which allows you to undo your keyboard shortcut action one time.

Formatting Shortcuts Lite Ribbon Button Userform

 

Click Here to Jump to Download Section

Full Version

The full version is $14.99 US and allows you to create  12 keyboard shortcuts.  

It includes the addition of the cell styles format type to create shortcuts for all cell properties (number formatting, borders, font, alignment, etc.)

It also includes the ability to preserve the full undo history.  This means you can undo any of the formatting you applied with the keyboard shortcuts using the Undo button or Ctrl+Z.  The Cell Styles and Undo History are two great features that I think you will find really useful.

Formatting Shortcuts Excel Userform 2.4

_

The full version also includes a Key List feature.  Clicking the Key List button will create a new workbook with a list of all your shortcut keys.  You can print this list and use it as a reference to learn and memorize your shortcuts.

Formatting Shortcuts Key List

The full version includes a full money-back guarantee.  If you are not satisfied, you may get a full refund within 90 days of purchase.

Both versions include free upgrades and support.

Product Comparison

Formatting Shortcuts Product Comparisons 2.4

Download

Lite Version – FREE
Full Version – $14.99 US

Add to Cart

After purchase you will be redirected to a secure download page.  You will also receive an email with a link to download the zip file.

Compatible with: Excel 2007, 2010, 2013, 2016 for Windows Only

All purchases are backed by our 100% Money-back Guarantee.

What’s in the Zip?

The zip file contains: the add-in file, installation guide, and user guide.

Formatting Shortcuts Zip File Contents

Online Help Pages

How to Install an Excel Add-in Guide

Formatting Shortcuts Add-in Help Page

What do you think?

I want you to love this add-in, so please leave a comment on how it works for you or how it can be improved.

11

How to Install an Excel Add-in – Guide

What’s an Excel Add-in File?

An add-in file is an Excel file that contains macros (code) and has the extension “.xla” or “.xlam” (2007 and later). When opened, the worksheets in the file are hidden and the macros are typically accessed through buttons that are added to the toolbar or ribbon. Once installed, an add-in will automatically open every time Excel is opened.

Installation

Here is a video that walks through how to install the add-in.

Please see my video below on how to add the add-in’s folder location to the Trusted Locations list.  This is now a critical step due to a July 2016 Office Security update.

Installing the add-in is pretty easy, and should only take a few minutes.

  1. Save the add-in file to your computer.
    – Pick any folder on your hard drive that is easy for you to find.
    – My suggestion is to create a folder named “Excel Campus” and place it in your Documents folder.
  2. Open Excel and go to the Options Menu.
    – This varies depending on your version of excel.
    – 2007 – Office Button > Excel Options
    – 2010 & 2013 – File > OptionsExcel Options Menus 2007 2010 2013 2016
  3. Click the “Add-Ins” option on the left-side menu, and then click the “Go…” button.
    – You may have to wait a few seconds for the window to appear.Excel Options Add-Ins Menu
  4. Click the “Browse…” button on the Add-Ins Menu.Browse for Excel Add-ins
  5. Locate the add-in file you saved in Step 1, and click OK.Select Excel Add-in File
  6. The Add-in will appear in the list and should be enabled, click OK.Enable Excel Add-in
  7. Installation Complete (almost) – The add-in is now installed.  However, there is now an additional step due to an Office Security Update in July 2016. Please see the section below for instructions.

Trust the File Location

Important Note: There is one additional step to this installation due to an Office Security Update released in July 2016.  Here is a video that explains it in more detail.

The folder that the add-in file is saved in needs to be added as a Trusted Location in Excel.  The instructions on how to trust the folder location are below.  I also have an article and video that describes this issue in more detail.

  1. Open the Excel Options menu
    1. File > Options
      Excel Options Menus 2007 2010 2013 2016
  2. Open the Trust Center menu and Add a new location
    1. 1. Trust Center > 2. Trust Center Settings… > 3. Trusted Locations > 4. Add new location.
      Add New Trusted Location Folder in Excel
  3. Browse for the folder that you saved the add-in file in.
    Add Trusted Location Folder
  4. Press OK, the folder should now appear in the Trusted Locations list.
    Trusted Location Added for Documents Folder
  5. Press OK on the Trust Center and Excel Options menu to close the menus.
  6. The add-in is now in a trusted location and the XL Campus tab will appear every time you open Excel.

Unblock the File

Some users still have issues with the add-in’s ribbon disappearing after trusting the folder location.  If this happens to you, you will need to Unblock the file by changing a file property.

  1. Locate the Add-in file (.xla, .xlam) in Windows Explorer.
  2. Right-click the file and select Properties.
  3. At the bottom of the General tab you should see a Security section.  Check the box that says Unblock.
    Security Unblock Property for Excel Add-in - Ribbon Disappears
  4. Press the OK button.
  5. Close Excel completely and re-open it.  The add-in should now load and any custom ribbons will appear.

You will only need to do this unblock one time.  However, if you download an updated version of the file then you will have to repeat the steps above to unblock it.

Hopefully these additional security steps will be fixed in a future update to Office.  If you completed all the steps above then you should see the add-ins ribbon tab load every time you open Excel.

Download PDF

Installing An Excel Add-in - Guide (672.2 KB)

Add-ins

Paste Buddy – Create custom keyboard shortcuts for the paste special commands like paste values.

Formatting Shortcuts Add-in – Keyboard shortcuts to change font & fill color and cell styles.

More Add-ins

12

Move Excel Charts with Arrow Keys

This post and video contain lots of tips for moving your charts and shapes with the arrow keys.  This is very useful when you need to arrange and align multiple shapes on your worksheet.  I also have a free Chart Alignment add-in that allows you to move the objects inside a chart (titles, labels, legends) with the arrow keys and alignment buttons.

Video

Move Chart with Arrow Keys

Typically when you select a chart with your mouse, the box surrounding the chart looks like the following.

Chart Single Click No Arrow Keys

When the chart is selected like this, nothing happens when you press the arrow keys to try and move the chart around the worksheet.  Instead, the objects within the chart are cycled through and selected.  Not what we want.

Solution #1

Hold Ctrl and Left Click the chart –  This selects the shape object that the chart resides in.  Circles will appear on each corner of the chart.

Chart Bounding Box Ctrl Click

The chart can now be moved around on the sheet with the arrow keys, making it easy to align it with other shapes, charts, or cells in your worksheet.

Solution #2

Select multiple charts or shapes – Hold the Ctrl key and select at least two charts using left click.  The bounding box will still look like the same as when you only have one chart selected, but you are able to move the charts with the arrow keys.

Select Multiple Charts

Move or Create a Shape Inside a Chart

Moving shapes inside a chart can get a bit confusing.  Shapes, such as a text box or rectangle, can reside in two places:

  • either inside the chart object,
  • or on the worksheet (outside of the chart).

The confusing part is that a shape residing outside the chart on the worksheet can be placed on top of the chart.  This makes it look like the shape is inside the chart even though it is not.  In this case, when you move the chart, the shape will NOT move with it unless you have both the shape and the chart selected.

A shape located inside the chart will move with the chart.  This makes it easier to move and align the chart because you don’t have to worry about selecting multiple objects when doing the alignment.

There are two ways to get a shape inside a chart:

  1. Create the shape inside the chart – First select the chart so the bounding box appears around it, then draw the shape inside the chart object area (Insert tab
    > Shapes menu).
  2. Paste a shape inside the chart – Cut or Copy an existing shape on your worksheet, then select the chart and paste the shape.  This will put the shape inside the chart.

Move Shape Inside Chart with Arrow Keys

Now that your shape is residing inside your chart, you can use the arrow keys to move it around and align it with the other objects in your chart (title, axis, legend, etc.)  See instructions below the screencast.

Move Shape Inside Chart with Arrow Keys

To move a shape inside the chart with arrow keys:

  1. Ctrl+Left Click the border of the chart so you get the circles on the corners.
    Ctrl Select Chart with Shape
  2. Then Ctrl+Left Click the shape inside the chart.  You will notice that the circles remain on the top left corner of the chart.
    Ctrl Select Shape Inside Chart
  3. Keyboard arrows are now enabled so you can move and align your shape (text box) within the chart.

Conclusion

You should now be able to get all your charts and shapes lined up to perfection using the incremental movements of the arrow keys.  This is very useful if you read my post on Panel Charts as an alternative to Stacked Charts and want to create panel charts.  In a future post I will share more tips and shortcuts for aligning and viewing your charts.

What are some shortcuts you use to align your charts?

17

Stacked Column Bar Chart Alternatives – Find the Missing Trends

Stacked bar or column charts are used a lot by the media and corporate world.  I believe they are popular because they display a lot of information in one chart, and are relatively easy to create in Excel.  However, I’m not a big fan.  I’m going to explain why I consider them to be “bad charts”, and present some alternatives.

Stacked Column Chart - BAD CHART

Bad Charts

I consider the stacked column chart above to be a “bad chart” because it doesn’t do a good job of displaying the trends in the data.  There are a total of six data series displayed here; the five regions plus the total.  But we are really only able to see trends in two of the six series.  We can see trends in the North America and Total (height of entire column) series because the baseline for these series is flat.  The baseline is basically the x-axis at $0.  This is very important when creating a chart that is displaying a trend.  Our eye is able to distinguish the baseline as the starting point for each column, and then compare the top point of each column moving from left to right.  This works great for the North America and Total series, but it is very difficult to see the trends for the other four regions.

Stacked Column Chart - Baseline Example

Uneven Baseline

Stacked Column Chart - Uneven Baseline Example

The image above shows the difficulty in quickly seeing the trend in sales for Asia because the baseline is uneven.  I emphasize “quickly” because we want our chart to clearly and easily communicate a story or trend.  It would probably be easier to just look at the actual numbers to see a trend versus trying to determine if the bars are increasing or decreasing in size over time.  And these bars for Asia get hidden when you consider all the other regions that are also stacked above and below it.  It’s an absolute mess! 🙂

Here is a line chart that shows the actual trend for Asia.  It’s much easier to quickly see that sales are declining over the last three quarters of the year.  This would probably draw attention to the performance of the region, and spark some action to be taken to correct the problem.

Asia Line Chart

When to Use Stacked Charts

Stacked charts can work if there are drastic changes in your data over time, and you want to only display the trend of one or possibly two series.  It’s best to highlight these series in a color that stands out, and add some text to describe the trend.  You also want to move the series to the bottom of the chart so it sits on the baseline, making it easy for the reader to see the trend.

Acceptable Stacked Column Chart

Alternative 2 below also makes use of the stacked chart by giving it a dynamic baseline to quickly analyze trends.

Solutions

Now that we have an understanding of when NOT to use stacked charts, we can start thinking about alternative methods that clearly and quickly tell the story.  The use of these will depend on what trends you find in your data, and how you want to convey the message.

Alternative #1: Panel Charts

Panel charts are a group of small charts organized together in a panel.  This is a good way to break out each region into its own chart.

Panel Chart - Alternative 1

Now we can start to see trends within each region.  These trends are not possible to see in the stacked chart because of the uneven baselines, and scale of the chart.  Sales in North America and Europe are much greater than Asia and Africa, so the trends get lost.  There are some important trends getting lost if you don’t look at them closely.  You can highlight those trends in a panel chart as I did above.  We can see that sales in Asia are declining, while sales in Africa have almost doubled over the year.  The dollars are small compared to total sales, but there might be great opportunities being missed if this is only analyzed in a stacked chart.

If you really want to impress your boss, you could add zoom buttons to each panel using the Zoom on Charts Macro (available for free download).  This is a great feature to add to an interactive dashboard.

Zoom on Panel Charts

Alternative #2: Dynamic Baseline Stacked Column or Bar Chart

You might like this solution if you want to keep the stacked chart, or don’t want to freak out your audience with something drastically different than they are used to seeing.  I call this one the dynamic baseline stack chart because it allows you to quickly choose the series to display at the bottom using a drop-down menu.  It is pretty easy to implement.  NO macros or VBA are required.  Just two different formulas and a drop-down form control.  The workbook that contains the form is available for free download below.  Instructions on how to create this chart are included in the file.

Dynamic Stacked Chart - Alternative 2 GIF

When the West region is at the top of the column stack it is difficult to see any trends.  But when the West is moved to the baseline (bottom) series, we see that there is a steady decline in the last half of the year.

There are also some correlating trends between South and West in the second half of the year.  This is easier to see when we move East to the top of the stack.  This might be another way to present your data depending on the message you want to convey to the readers.

Dynamic Baseline - West Example

Alternative #3: ???

I’d love to hear about some alternatives from you.  Make it as simple or complex as you like, there’s no right or wrong answer.  Just remember that the goal is to clearly and quickly tell the story to the reader.

Thanks!

Download

Dynamic Baseline Stacked Column Chart.xls (130.6 KB)

Additional Resources

If you’re looking to learn more about charting, I highly recommend the dashboard course from My Online Training Hub.  I have taken this course and it really helped me improve my charting skills.  Checkout my video review of the course to learn more about it.