MrExcel Excel Videos

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

Summarizing Data in Excel Using Subtotals This is the first 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 Advanced Filter 1. Copy the headings to an Output Range 2. Select the data in the customer column. 3. Data, Filter, Advanced. Copy to another Location. Specify J1 as the output. Choose Unique Items Only. OK. 4. Select K2:M17. Type a SUMIF formula. Ctrl+Enter

Summarizing Data in Excel Using Subtotals This is the first 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 Remove Duplicates 1. Copy the data to a new location 2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK. 3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter.

Summarizing Data in Excel Using Subtotals This is the first 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 subtotals: 1. Sort the data by customer 2. Data, Subtotals. At each change in customer, choose four columns. OK. 3. Click #2 Group and outline button 4. From Customer heading, Ctrl+Shift+Down+Right 5. Alt+; to select visible cells. Ctrl+C to copy. Ctrl+N for New. Ctrl+V to paste