MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Sorting the Line Items »


February 25, 2020

One of the problems with my solution is that the final sequence of the categories did not necessarily match the original sequence of the columns. I realized this at the very end of my video, and since it was not particularly important, I did not worry about it.

Power Query: Isolating the Rows that represent Quarters »


February 25, 2020

There is a point in each solution where you need to figure out which rows contain employee names and which rows contain quarters.

Power Query: Delete this, Delete those, or delete nothing? »


February 25, 2020

I am having a great time going through the 29 different solutions to the Data Cleansing challenge. In my solution, and in many solutions, I came down to the point where I wanted to delete the subtotal row.

Power Query: Dealing with Multiple Identical Headers »


February 25, 2020

In my original data shaping problem, I ran into a problem very early in the process. The incoming data was going to have many columns with a heading of Q1.

Power Query: Using Else If Clauses in Conditional Columns »


February 25, 2020

In my solution to reshaping the data, I wanted a way to see if a column contained an employee name or a value such as Q1, Q2, Q3, Q4. I assumed that no one would have a name with 2 characters, and so I added a column to calculate the length of the text in the column.

Power Query: Adding a Total Column »


February 25, 2020

In my original solution, I had added a calculated column with the formula Total = [Q1]+[Q2}+[Q3]+[Q4].

Power Query: Extracting Left 2 Characters From a Column »


February 25, 2020

In my original video about reshaping data, I arrived at a point where I needed to get the first 2 characters from a column. My method involved Split Column to generate the first 2 characters and everything else. I then deleted everything else.

Power Query: Number Groups of Records as 1 through 5 repeatedly »


February 25, 2020

In my Power Query Challenge, one of the steps was to take the name field from every 5th record and copy it down to the five records. My original solution was clunky, counting on the fact that the length of the name would be longer than 2 characters.

Bill's

Bill's "How Would You Clean This Data" Challenge »


February 25, 2020

When I do a live Power Excel seminar, I offer that if anyone in the room ever has an odd Excel problem, they can send it to me for help. That is how I came to receive this data cleansing problem. Someone had a summary worksheet that looks like this.

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

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


February 24, 2020

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 2020: Simultaneously Edit the Same Workbook in Office 365 »


February 20, 2020

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.

Excel Recover Unsaved Workbooks. Photo Credit: Gary Chan at Unsplash.com

Excel 2020: Recover Unsaved Workbooks »


February 19, 2020

The Auto Recover feature is a lifesaver. It is turned on automatically in Excel 2010 and newer.