Three Excel tricks today: Fill in the empty cells when you have multiple row fields in a pivot table. Fill in the empty cells because there were not any sales. And Change the Pivot Table Defaults for all future pivot tables.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
An awesome trick from Candace & Robert in Topeka of how to copy the path to your workbook using Document Location on the Quick Access Toolbar in Excel.
Revisiting a topic from Episode 1995 - how do you bring the customer name field down into the subtotal rows? Kimberly from Oklahoma City and Sarah from Omaha collaborate for a faster way.
There is a well-known trick in Excel to fill just weekdays. But what if your work week is not Monday through Friday? Today, a trick with WORKDAY.INTL in Excel to fill Monday, Wednesday, Friday.
An awesome tip from Kris and Taylor in New Mexico - how to use the AutoFilter to filter to all rows above $20K in Excel.
Sankey Charts, Tornado Charts, Word Clouds are coming to Excel along with many other Power BI Custom Visuals. Take a spin through these three chart types.
To learn more about custom visuals:
https://microsoft.github.io/PowerBI-visuals/docs/building-for-excel/testing-in-excel/
How to get a unique combination of all values in Excel columns B & D? Normally, use Advanced Filter or, Remove Duplicates. But today, how to use UNIQUE and CHOOSE to solve the problem.
Table of Contents:
(0:00) Intro to Getting a UNIQUE from two adjacent columns in Excel
(0:35) Advanced Filter for Unique Combinations
(1:19) Remove Duplicates for Unique Combinations
(2:36) Download the Dynamic Arrays book
(2:45) The concept of Lifting in Excel
(3:10) How CHOOSE normally works
(3:41) Lifting CHOOSE for two columns
(4:25) Wrapping in the UNIQUE function
(4:47) Testing Our Knowledge: Returning 3 columns
(5:43) Array constants and Broadcasting
(5:53) Sorting Results using SORT and pairwise lifting
(6:38) Testing our Knowledge: Sorting by...
John Michaloudis and Bill Jelen discuss new features in Excel on John's podcast. Thanks to John for allowing me to cross-post this here.
All the links John Mentions in the Show Notes are here:
https://www.myexcelonline.com/podcast/022-new-microsoft-office-365-excel-features-with-mr-excel-bill-jelen/
It is a long episode. Use this Table of Contents to jump to any point in the video:
(0:00) Introduction & Theme Song
(2:10) Difference between Office 365 & Excel 2019
(6:44) Insider, Monthly, Semi-Annual Office 365
(7:40) Insider broke Total Visible
(10:25) Intro to New Features
(10:45) Geography and Stock Data Types
(15:40) Stock Data Types
(19:56) Artificial Intelligence with Ideas
(28:24) Dynamic Arrays
(38:10) Why SEQUENCE is not as...
Office 365 customers will soon be seeing a new Data Types gallery in the Data tab in Excel. Convert stock symbols to a Stock Data Type. You can easily return price, CEO, Volume, and other statistics.
