MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


LET: Storing Variables Inside Your Excel Formulas »


March 31, 2020

Formulas in Excel are already a programming language. When you build a model in Excel, you are essentially writing a program to calculate a set of outputs from a set of inputs. The Calc team Redmond have been working on a couple of enhancements to the Excel formula language to make Excel a bit more like a programming language.

Excel Sort East, Central, and West Using a Custom List. Photo Credit: Luiz Centenaro at Unsplash.com

Excel 2020: Sort East, Central, and West Using a Custom List »


March 30, 2020

At my last day job, we had three sales regions: East, Central, and West. The company headquarters was in the East, and so the rule was that all reports were sorted with the East region first, then Central, then West. Well, there is no way to do this with a normal sort.

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

Excel 2020: Add Meaning to Reports Using Data Visualizations »


March 26, 2020

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 2020: Plotting Employees on a Bell Curve »


March 25, 2020

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 2020: Create a Bell Curve in Excel »


March 23, 2020

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 2020: Create Filled Map Charts in Office 365 »


March 19, 2020

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 2020: Create Funnel Charts in Office 365 »


March 18, 2020

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 2020: Create Waterfall Charts »


March 16, 2020

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 2020: Show Two Different Orders of Magnitude on a Chart »


March 12, 2020

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 2020: Create Interactive Charts »


March 11, 2020

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 2020: Paste New Data on a Chart »


March 9, 2020

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 2020: Create Perfect One-Click Charts »


March 5, 2020

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?