Top 25 Best Excel Dashboard and Chart Tips for 2015

Charts

Tony Robbins and other performance gurus who strive to improve personal performance teach that one of the most powerful ways to improve ourselves is by modeling. Modeling is watching others so you can learn how they perform a task better, then modeling our behavior after theirs.

These are the top 25 best Excel dashboard tips from Critical to Success from 2015. These are Excel dashboard tips that others thought would be valuable to them so you might want to review them as well.

Before you begin scanning this list, put yourself into a “Beginner’s Mind”. The Beginner’s Mind is a Buddhist term referring to

“an attitude of openness, eagerness, and lack of preconceptions when studying a subject, even when studying at an advanced level, just as a beginner in that subject would.”

Zen teacher Shunryu Suzuki said the Beginner’s Mind is critical to success in learning because “in the beginner’s mind there are many possibilities, in the expert’s mind there are few.”

Next week I’ll send you the Top 17 Top Business and Personal Productivity Tips from Critical to Success in 2015.

For now, scan this list and see what might help you. Think about your co-workers and share with them what you find.

1. Creating an Actual vs Target Chart in Excel using Floating Markers

Colored markers show target values against actual values in columns.

Colored markers show target values against actual values in columns.

Almost everyone in business has heard the maxim, “What you measure is what gets managed.” We know that measuring is important, but measuring is only the start. If you are going to improve what you are measuring you need targets. And before you start changing you need to know how far off the target you are. This Excel trick is an easy way to see the actual value as a column with target value shown as a floating bar, as shown in this figure. You might also want to see the top 5 budget vs actual excel charts.

2. How to Create a Static Four Quadrant – Matrix Model in an Excel Chart

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

The Four Quadrant – Matrix Model is a valuable decision making tool. This article will show you how to create a Four Quadrant – Matrix Model in an Excel chart, add multi-line data labels to each data point, add a graphic background, and more. Another article shows you how to create a dynamic Four Quadrant – Matrix Model in Excel that has dynamic quadrants that change. Dynamic matrix charts are great for example, if you need quadrants that demarcate above and below averages.

3. How to Create a Dynamic Chart with Drop-Down List

Begin your dynamic chart by creating a static chart.

Use the INDEX function to retrieve data from a range based on the row you select in the drop-down list.

Using a drop-down list to create a dynamic chart creates a professional look in your dashboard and the drop-down gives a visual reference to the user where they should click. But, this isn’t the only way to create a dynamic chart allowing users to select data from a list. You can also use a data validation list. Check out how to create a dynamic chart using a data validation list.

4. 5 Top Budget vs Actual Excel Charts You Need

Here are five of the easiest to create and cleanest budget vs. actual (target) Excel charts. I spent three hours searching through different Excel techniques and methods on the web and picked these five for being easy to create and cleanly showing the differences between budget and actual values.

5. Creating an Excel Chart with Two Rows of Labels on the X-Axis

When the X-axis is crowded with labels one way to solve the problem is to split the labels for each data point into two parts and use two rows of labels on the X-axis. It’s really easy to do, even if you’ve already created the chart.

6. Show Excel Pie Chart Details with an Exploded Bar Chart

Excel Pie Chart with Exploded Detail Data

Excel Pie Chart with Exploded Detail Data

To get the big picture of how a total is distributed, for examples total sales by country or unit volumes by region, most executives want to see a pie chart. This is one of the few things a pie chart might be good for, showing how 100% of a total is distributed. The problem comes in showing details within small segments of a pie chart.

7. Adding Labels to the Bubbles in an Excel Bubble Chart

You can apply data labels to bubbles to show the data they represent by right-clicking a bubble so the entire series is selected, then choosing Format Data Labels. Initially only Y values are displayed, but you can choose to add X values, the Z values (bubble size), and more.

What would really be powerful is to add data labels of your own design. And even better, how about adding data labels with multiple lines that include data such as project name, date, revenue, etc. – the additional data that might be needed to make decisions.

8. Creating Dynamic Excel Timelines that Scroll

Use the dynamic Excel timeline to scroll through projects or event timelines.

Use the dynamic Excel timeline to scroll through projects or event timelines.

Timelines are excellent for showing relative time frames for tasks or events. Tip #13 describes how to create a static event timeline in an Excel chart. This tips show you how to change the static timeline into a dynamic timelines so you can scroll the timeline through a database of events or due dates.

9. Creating Dynamic Excel Chart Titles that Link to Worksheet Cells

Dynamic Excel chart titles link to cells and show multiple text lines.

Dynamic Excel chart titles link to cells and show multiple text lines.

You don’t have to settle for Excel’s static chart titles. Your Excel chart titles can be dynamic and link to cell contents. When the worksheet changes, the title changes. Charts can even have multiple lines that include dynamic data.

10. Excel’s Four Quadrant – Matrix Model Chart : Don’t Make a Decision without It!

The Four Quadrant or Matrix Model is one the most valuable and widely used tools for decision making. This article has short descriptions of a few famous Four Quadrant – Matrix Model charts like a SWOT analysis or Steven Covey’s time management matrix. The related article shows you how to create the two most used types using Excel charts. You can’t be a successful consultant or product manager without a four quadrant matrix in the top drawer of your toolkit.

11. How to Create a Dynamic Chart Using a Data Validation List

This could very well be the fastest and easiest way to create a dynamic chart. It’s a great way to impress the boss when you need to quickly make a dynamic chart to look at lots of data. If you know a faster method, please let me know so I can share it on Critical to Success.

12. Creating Dynamic Excel Lists and Custom Reports from One Master Excel List

A highly valuable Excel skill is being able to create dynamic sub-lists, custom reports, or chart data from a larger master Excel list or database. Use these techniques to create dynamic sub-lists and custom reports that will match almost any report writer. Add a simple macro and you can automate work that would take hours.

13. Create an Excel Timeline Chart to Manage Your Projects and Events

Excel timeline charts are a great tool in any professional’s toolkit. You can insert Excel timelines in your Excel workbooks where you manage projects, show resources and expenses, schedule marketing events, etc. They make communication and detection of conflicts much easier.

Don’t miss #8 in the list on how to create Dynamic Excel Timeline Charts that scroll through time.

14. Creating Dynamic Excel Chart Legends that Link to Worksheet Cells

Create dynamic legends that link to cells and show multiple lines.

Create dynamic legends that link to cells and show multiple lines.

Want to create Excel chart legends that show additional information? Wouldn’t it be great to have a multi-line legend that has notes in it. Just add a dynamic legend that updates when worksheet data updates. It’s pretty easy to make a chart like this one that shows custom legends in a cell and that has multiple lines.

15. Using VLOOKUP with multiple criteria from drop-down lists

VLOOKUP cannot accurately retrieve data from tables where the leftmost column does not have unique text values. But, with this trick you can use two (or more) Data Validation lists to select unique data from a table.

16. When data points are bad or missing, how do you create a usable and decent looking Excel Chart?

You may have faced the problem of having a chart where some of the data doesn’t exist or it’s entered as zero to represent missing data. When faced with these “holes” how can you create a valid and decent looking chart?

By the way, click here if you’d like to see more Excel troubleshooting tips in Critical to Success.

17. How to Combine Bubble and Column Excel Charts – Creating a Dynamic Four Quadrant – Matrix Model

Dynamic Four Quadrant Matrices use quadrants that change size to reflect a changing mid-point such as an average of the data points. They are valuable in seeing relationships.

Dynamic Four Quadrant Matrices use quadrants that change size to reflect a changing mid-point such as an average of the data points. They are valuable in seeing relationships.

static Four Quadrant – Matrix Model is fine if you’re working with something like the Steven Covey – Eisenhower time-management matrix or a SWOT (strengths, weaknesses, opportunities, and threats) matrix that just contain lists and there is no difference between item weightings. But, what about Four Quadrant – Matrix Models where the quadrants change size? That’s where you need a dynamic four quadrant matrix.

To learn how to create multi-line labels like the ones shown here, check out tip 23 below.

18. Creating Dynamic Charts in Excel that Resize Using the OFFSET Function and Named Ranges

Use Excel's OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Use Excel’s OFFSET and COUNTA functions to create dynamic range names automatically adjust as data is added or deleted.

Dynamic charts using Excel’s OFFSET and range names resize when data is added or deleted. Unlike using Tables for dynamic charts, this method is backwards compatible with early versions of Excel.

19. Troubleshooting Problems in Excel Charts: X-Axis Doesn’t Chart Correctly

You’re in a little hurry so you knocked out a hand-entered table in the worksheet, dragged across the data and inserted a chart. But, the chart looks weird. The Y-axis isn’t right. You wanted a quick and simple table to compare months of different years so you typed in the month’s (text) across the top and years (numbers) down the left. What is going wrong?

By the way, click here if you’d like to see more Excel troubleshooting tips in Critical to Success.

20. How to Create Dynamic Range Names for Automatically Expanding Menus

Range names are words you assign to cell references or formulas. You can use them instead of a cell reference or formula to make your worksheets more understandable and to give you added power. They are one of the powerful Excel features that separate the novice user from power users. And dynamic range names, names that adjust to new data automatically, belong to the upper-level of power users.

21. Inserting a Background Picture into the Chart’s Plot Area to Create Static Quadrants

You have already seen the four-colored quadrant inserted behind the chart in tip 2 but, you can be far more creative and insert semi-transparent pictures behind a chart. For example, a semi-transparent picture of a family behind a chart on changes in family size? Be sure to make the picture semi-transparent so the chart is easy to read.

22. Adding a Data Series to an Excel Chart

It happens to everyone, you spend a lot of time building a chart and then someone comes along with a new series of data to add to the chart. When you know these tips you just smile and add the new data with a few keystrokes. There are three ways to add data to an existing chart and each has its own unique advantages.

23. Adding Labels with Multiple Lines to the Bubbles in an Excel Bubble Chart

To make your chart one you can make decisions from it may need more information than normally shows in an Excel chart. With the following trick you can add two or more lines of valuable information to each label, like the bubble chart in tip 17. (Remember, don’t create cluttered charts. You have to promise to only use this to add data needed for decision making.)

24. Using INDEX and MATCH to Retrieve Excel Data

Use INDEX and MATCH to retrieve data from an Excel table when you want the greatest flexibility and speed in retrieving data. INDEX and MATCH takes a little more work to create than VLOOKUP, but it is far more flexible and powerful. It also enables you to animate charts.

Two tips that use INDEX were not in the top 25 dynamic Excel charts list, but they are personal favorites I have used in report building. The first uses INDEX to Scroll the X-Axis in Excel Charts.

Use INDEX to scroll the X-axis in an Excel chart.

Use INDEX to scroll the X-axis in an Excel chart.

The second tip also uses INDEX to scroll data lists through a “virtual” data window on the worksheet. Dragging the scroll bar on the right in column I scrolls the data up and down.

Using INDEX to Scroll through a Financial Statement

Use INDEX to create a scrolling window that looks onto a larger database.

More than once when managers see this I have heard, “How did you do that in Excel?” It looks like you’ve created a transparent window that looks into a large worksheet that is scrolling behind. The scrolling window does display data from a MUCH larger database on the same or different worksheet but it just appears like it is in a window. To display really large sets of data you can use two INDEX formulas, one vertical and the other horizontal, to scroll vertically and horizontally.

25. Creating Cascading Drop-Down Menus in Excel

Create cascading drop-down menus in Excel without VBA.

Create cascading drop-down menus in Excel without VBA.

This is a very esoteric solution, bordering on an interactive database query. But, when you have long lists or categories of multiple lists in Excel you can help your users select data by creating two cascading drop-down menus. When you select from the first drop-down menu, the contents of the second drop-down changes. You need to know range names for this, but you don’t need to program with macros or VBA!

 

So that’s the list of the top 25 Excel Dashboard tips for the past 15 months. Just about every one of these tips can be created in earlier version of Excel back to at least Excel 2007.

Did you keep your Beginners Mind open to new ideas?

Did you see tips you can pass on to help others? If you saw something that might help someone else, please pass this list on.

 

 

Share the power...

Leave a Comment:

Leave a Comment: