MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


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.

Excel New Threaded Comments Allow Conversations. Photo Credit: Mihai Surdu at Unsplash.com

Excel 2019: New Threaded Comments Allow Conversations »


March 12, 2019

Threaded comments debuted in 2018. When you insert a comment, Excel stores the comment, the author, the date, and time. When a co-worker sees your comment, they can click Reply and add a new comment to the same cell. Each set of comments lists the author, date, and time.

Excel Undo an AutoSave. Photo Credit: Miguel A. Amutio at Unsplash.com

Excel 2019: Undo an AutoSave »


March 11, 2019

What if you have to undo an AutoSave? Your manager said to merge two regions and then 20 minutes later calls the merger off. AutoSave has been saving after every change in your workbook.

AutoSave is Necessary, But Turn it Off When Not Co-Authoring. Photo Credit: Frank Okay at Unsplash.com

Excel 2019: AutoSave is Necessary, But Turn it Off When Not Co-Authoring »


March 8, 2019

The reason that co-authoring is possible is because of AutoSave. Every time that you make a spreadsheet change, that change will be saved to OneDrive so that others can (almost) instantly see what you just typed. AutoSave is necessary if you want ten accountants editing a budget worksheet at the same time.

Excel Simultaneously Edit the Same Workbook in Office 365. Photo Credit: Perry Grone at Unsplash.com

Excel 2019: Simultaneously Edit the Same Workbook in Office 365 »


March 7, 2019

After Google's spreadsheet product began offering the ability for multiple people to edit the same worksheet, the Excel team spent over two years developing a feature that they call co-authoring. The feature was released to Office 365 customers in the summer of 2017.