MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Add Meaning to Reports Using Data Visualizations. Photo credit: Simone Hutsch at Unsplash.com.

Excel 2019: Add Meaning to Reports Using Data Visualizations »


March 28, 2019

Three easy visualization tools were added to the Conditional Formatting dropdown in Excel 2007: Color Scales, Data Bars, and Icon Sets.

Excel Plotting Employees on a Bell Curve. Photo Credit: Dose Juice at Unsplash.com

Excel 2019: Plotting Employees on a Bell Curve »


March 27, 2019

Rather than creating a generic bell curve, how about plotting a list of employees or customers on a bell curve?

Create a Bell Curve. Photo Credit: Chris Barbalis at Unsplash.com

Excel 2019: Create a Bell Curve in Excel »


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.

Excel Create Filled Map Charts in Office 365. Photo Credit: Arthur Edelman at Unsplash.com

Excel 2019: Create Filled Map Charts in Office 365 »


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.

Create Funnel Charts in Office 365. Photo Credit: YIFEI CHEN at Unsplash.com

Excel 2019: Create Funnel Charts in Office 365 »


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.

Create Waterfall Charts. Photo Credit: Jonatan Pie at Unsplash.com

Excel 2019: Create Waterfall Charts »


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.

Excel Show Two Different Orders of Magnitude on a Chart. Photo Credit: Mikael Kristenson at Unsplash.com

Excel 2019: Show Two Different Orders of Magnitude on a Chart »


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.

Excel Create Interactive Charts. Photo Credit: Alex Knight at Unsplash.com

Excel 2019: Create Interactive Charts »


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.

Excel Paste New Data on a Chart. Photo Credit: Cassie Matias at Unsplash.com

Excel 2019: Paste New Data on a Chart »


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.

Create Perfect One-Click Charts. Photo Credit: rawpixel at Unsplash.com

Excel 2019: Create Perfect One-Click Charts »


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?

Add a Tooltip to a Cell with Validation. Photo credit: oldskool photography at Unsplash.com.

Excel 2019: Add a Tooltip to a Cell with Validation »


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.

Excel Old Style Comments Are Available as Notes. Photo Credit: Kelly Sikkema at Unsplash.com

Excel 2019: Old Style Comments Are Available as Notes »


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.