March 28, 2019
Three easy visualization tools were added to the Conditional Formatting dropdown in Excel 2007: Color Scales, Data Bars, and Icon Sets.
March 27, 2019
Rather than creating a generic bell curve, how about plotting a list of employees or customers on a bell curve?
March 26, 2019
A bell curve is defined by an average and a standard deviation. In statistics, 68% of the population will fall within one standard deviation of the mean. 95% falls within two standard deviations of the mean. 99.73% will fall within three standard deviations of the mean.
March 25, 2019
Early in 2017, Map Charts appeared on the Insert tab in Office 365. A Map chart shades closed regions on a map such as countries, states, counties, even zip code boundaries.
March 22, 2019
In 2016, Office 365 introduced Funnel charts, as well as Treemap, Sunburst, Box & Whisker, Pareto, and Histogram charts. A Funnel chart is great for showing a sales funnel.
March 21, 2019
For 12 years, I worked at a company doing data analysis. One of my regular tasks was to analyze the profit on sales proposals before they went out the door. I did this with a waterfall chart. For me, the waterfall chart never would have to dip below the zero axis.
March 20, 2019
It is nearly impossible to read a chart where one series is dramatically larger than other series. In the following chart, the series for Year to Date Sales is 10 times larger than most of the monthly sales. The blue columns are shortened, and it will be difficult to see subtle changes in monthly sales.
March 19, 2019
It is easy to create interactive charts without using VBA. By default, if you hide rows in Excel, those rows will be hidden in the chart. The technique is to build a chart with every possible customer and then use a slicer or a filter to hide all except one of the customers.
March 18, 2019
You might be responsible for updating charts every month, week, or day. For example, in my last job, a collection of charts were updated during the month-end close process. The charts would track progress throughout the year.
March 15, 2019
One-click charts are easy: Select the data and press Alt+F1. What if you would rather create bar charts instead of the default clustered column chart?
March 14, 2019
In a previous tip, I suggested using Notes for a help system. The problem with notes: it is possible to arrow in to a cell without ever hovering over the red triangle and the note might be missed. You can use the Data Validation dialog to set up a tooltip for a cell. The tooltip is only visible when the cell is the active cell.
March 13, 2019
While the new threaded comments are cool, there are some great tricks that the old legacy comments offered that are lost with the threaded comments. Luckily, if you have a situation requiring one of the special tricks, the old comments are still available as Notes.