May 8, 2024 - by Bill Jelen
Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation:
1. Drag Revenue to the Values area twice.
2. Double-click on the heading Sum of Revenue2. Excel opens the Value Field Settings dialog.
3. Click on Show Values As and select % of Column Total from the dropdown.
4. Type a new name in the Custom Name field, such as % of Total.
Excel 2024: Create a Year-over-Year Report in a Pivot Table »
May 6, 2024 - by Bill Jelen
Let's say you have two years worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table.
Excel 2024: Create Your First Pivot Table »
May 2, 2024 - by Bill Jelen
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 2024: Consolidate Quarterly Worksheets »
April 30, 2024 - by Bill Jelen
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.
Supercharge Power BI - 3rd Edition
February 2021
Released in 2021, this book will teach you the DAX language from the ground up, using Power BI as your canvas.
Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition
May 2022
Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, "MrExcel," and Oz do Soleil during their careers run as financial analysts.
Power BI for the Excel Data Analyst
July 2022
A step-by-step guide on how to use Power BI. Written with Excel fans in mind, but with every Power BI student at its heart.
MrExcel 2021 - Unmasking Excel
February 2021
This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.