Excel Tips
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 "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.
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 2020: Replace the Comma Style in Book.xltx »
February 17, 2020
The Excel team offers Currency, Percent, and Comma icons in the center of the Home tab of the Ribbon. The tooltip says the Comma Style formats with a thousands separator. I despise this icon. Why do I despise this icon?
Excel 2020: Changes to Book Template are Cumulative »
February 13, 2020
Anything you do to Book.xltx (default workbook) is cumulative. Build the workbook with your favorite settings today. If you discover some new settings that you would like to add to Book.xltx in the future, follow these steps:
Excel 2020: Use INDIRECT for a Different Summary Report »
February 10, 2020
Say that you want to build the following report, with months going down column A. In each row, you want to pull the grand total data from each sheet. Each sheet has the same number of rows, so the total is always in row 12.
Excel 2020: Create a SUM That Spears Through All Worksheets »
February 6, 2020
You have a workbook with 12 worksheets, 1 for each month. All of the worksheets have the same number of rows and columns. You want a summary worksheet in order to total January through December.