MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Excel Rearrange fields in a pivot table. Photo Credit: Pierre Châtel-Innocenti at Unsplash.com

Excel 2019: Rearrange fields in a pivot table »


April 22, 2019

The power of pivot tables is the ability to rearrange the fields. If your manager decides you should put Regions across the top and products down the side, it is two drags to create the new report.

Create Your First Pivot Table. Photo Credit: Roman Bozhko at Unsplash.com

Excel 2019: Create Your First Pivot Table »


April 18, 2019

Pivot tables let you summarize tabular data to a one-page summary in a few clicks. Start with a data set that has headings in row 1. It should have no blank rows, blank columns, blank headings or merged cells.

Excel Get Ideas from Artificial Intelligence. Photo Credit: Thomas Chan at Unsplash.com

Excel 2019: Get Ideas from Artificial Intelligence »


April 17, 2019

A new artificial intelligence feature debuted in Office 365 in early 2018. Originally called Insights, the feature was re-branded as Ideas and moved to the far right side of the Home tab of the Ribbon by September of 2018. Excel Insights will analyze your Excel data to search for patterns and return a series of interesting facts or trends about the data.

Consolidate Quarterly Worksheets. Photo Credit: Pierre Châtel-Innocenti at Unsplash.com

Excel 2019: Consolidate Quarterly Worksheets »


April 15, 2019

There are two ancient consolidation tools in Excel. To understand them, say that you have three data sets. Each has names down the left side and months across the top. Notice that the names are different, and each data set has a different number of months.

Excel Sort and Filter by Color or Icon. Photo Credit: Mat Reding at Unsplash.com

Excel 2019: Sort and Filter by Color or Icon »


April 11, 2019

Conditional formatting got a lot of new features in Excel 2007, including icon sets and more than three levels of rules. This allows for some pretty interesting formatting over a large range. But once you format the cells, you might want to quickly see all the ones that are formatted a particular way. In Excel 2007, sorting and filtering were also updated to help you do just that!

Excel Format the Subtotal Rows. Photo Credit: Nicholas Punter at Unsplash.com

Excel 2019: Format or Copy the Subtotal Rows »


April 10, 2019

It is a little odd that Subtotals only bolds the customer column and not anything else in the subtotal row. Follow these steps to format the subtotal rows:

An Easier Way to Fill in a Text Field on Subtotal Rows. Photo credit: Denise Chan at Unsplash.com.

Excel 2019: An Easier Way to Fill in a Text Field on Subtotal Rows »


April 8, 2019

Kimberly in Oklahoma City and Sarah in Omaha combined to provide a faster solution to getting the sales rep to appear on the Subtotal rows. Provided you only need the data in the #2 Summary View, this works amazingly well.

Excel Fill in a Text Field on the Subtotal Rows. Photo Credit: Jan Antonin Kolar at Unsplash.com

Excel 2019: Fill in a Text Field on the Subtotal Rows »


April 4, 2019

Say that each customer in a data set is assigned to a single sales rep. It would be great if you could bring the sales rep name down to the subtotal row. Here are the steps:

Excel Sort Subtotals. Photo Credit: Nathan Lindahl at Unsplash.com

Excel 2019: Sort Subtotals »


April 3, 2019

For those of you who have never used subtotals, here is how to set them up.

Excel Sort Left to Right. Photo Credit: Nolan Simmons at Unsplash.com

Excel 2019: Sort Left to Right »


April 1, 2019

Every day, your IT department sends you a file with the columns in the wrong sequence. It would take them two minutes to change the query, but they have a six-month backlog, so you are stuck rearranging the columns every day.

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

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


March 29, 2019

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 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.