MrExcel Excel Videos

Rick from NJ asks if one slicer can control pivot tables that come from multiple sources See episode 2104 for a VBA solution But this can be done with the Data Model First - delete any pivot table that are not based on the data model Find the field(s) in common between your two data sets. Copy each field to a new table and use Remove Duplicates to get a unique list of that field Make each of the data sets in to a table using Ctrl+T Build a relationship between each data set and the new Slicer Source table When you build each pivot table, choose Add this Data to the Data Model Build a Slicer - click the All tab - choose from the Slicer Source Figure out which pivot table is not tied to the slicer. Select a cell in that pivot table and...

Alex from Houston asks: Can you do a Median in a Pivot Table The traditional answer is No! But thanks to the Data Model, you can create a Median Ctrl+T to make your data into a Table Insert, Pivot Table, Add this Data to the Data Model Right-click the Table name and choose New Measure The Measure will be =MEDIAN([Sales]) To download the Excel workbook used in this video, see the link near the end of Pivot Table Median

The Pivot Table tools in Excel offers Drill Up and Drill Down buttons that are always greyed out. How can you use those? You have to find someone who has the Power Pivot tab in their Excel (Office 365 E3 or Office 365 Pro Plus) Power Pivot, Manage, Diagram View. Select fields for hierarchy. Right click and Create Hierarchy. If you don't have Office 365, you can download a workbook with a hierarchy from Creating a Hierarchy in a Pivot Table

For decades, some pivot tables give you a Count instead of a Sum. The problem was usually caused by a few empty cells in your data. Starting in version 1804 of Office 365, the problem is fixed. To read more about this: Improved Handling of Empty Cells in Pivot Table Source Excellers have been annoyed by Count of Revenue in pivot tables for years It happens when you have one or more empty cells in the revenue column Starting with version 1804 of Excel in Office 365, the behavior is fixed. You will still get a count if someone puts spaces instead of a number.

How to wrap three columns of data in to two sets of columns per page Super-Important step: Do all of the Page Setup things first! Rows to Repeat at Top, Margins, Header/Footer Add numbers 1 to 80 Ctrl+P for Print Preview. How many rows per page? Alt+F11 for VBA Editor Insert, Module Type the code shown & Run To copy the code used in this video, visit: How to Wrap Data to Multiple Columns in Excel

Is there an equivalent of MROUNDUP? How to round up to next increment of 20? Review of rounding tricks & techniques ROUND(Number,Digits) and Digits can be negative to round to 10's or 100's ROUNDUP is great for killing weeds in your driveway, but also to round up to next 1, 10, 100 MROUND will round to nearest 20, but there is not a MROUNDUP Instead, use CEILING.MATH The opposite function is FLOOR.MATH to MROUNDDOWN

Which of the five techniques shown this week is your favorite? This week, I covered Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables. Vote here: Microsoft Forms

Summarizing Data in Excel Using Subtotals This is the fifth of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with a pivot table 1. Select one cell in your data. 2. Insert, Pivot Table, OK 3. Checkmark Customer, Quantity, Revenue, Profit, Cost 9 Clicks and you will have your result. Stop back tomorrow for a chance to vote for your favorite method.

Summarizing Data in Excel Using Subtotals This is the 4th of a five-part series on Summarizing Data This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables How to summarize with Consolidate: 1. Select a top-left corner cell in a blank area of your worksheet 2. Data, Consolidate. Specify D1:H564. Choose Top Row, Left Column OK