Holly from Vero Beach gets a report each week with Member ID. She needs to pull in the Member Name from last week’s report. This involves doing a VLOOKUP from one workbook to the other – Holly’s employers is on Excel 2016 instead of 365.
Category: Excel
Excel Number A Filtered List – 2503
Amy from the St. Louis IIA asks: Is there a way to number the visible rows in a filtered list? This video shows how a formula of =SUBTOTAL(3,B$1:B2)-1 in cell A2 solves the problem.
Its Back The Flattened Pivot Table – 2504
Laura from Nashville is looking for a way to make sure that all future pivot tables default to having No Subtotals. This *was* a feature in the PowerPivot Add-in back in Excel 2010, but then was removed. However, thanks to Pivot Table Defaults, you can force all future pivot tables into a flat pivot table that is ready for re-use.
Awesome Way To Replace A Very Long Slicer in Excel – 2509
What if you have an Excel Slicer with hundreds of values? Today, an awesome trick from Tine at Zebra BI that allows you to replace that huge slicer with one cell.
Table of Contents
(0:00) Welcome
Exploring Excel IMAGE Function – 2500
Display pictures on your Excel Dashboard using an Excel IMAGE formula. This episode has a deeper dive into the function and how to use it.
(0:00) Welcome
(0:24) Finding Image Address
(0:54) Building Image URL
(1:30) Using IMAGE function
(1:52) Hover for Pop-out Image
(2:30) Pivot Table
(2:55) Formula to Display All Images
(3:04) FILTER to convert to array
(3:15) IMAGE function
(3:20) TRANSPOSE for 2501 Bug
(3:35) WRAPROWS function
(3:45) Excel MVP by Country
(4:10) Caption under picture but how?
(5:19) Caching images #BUSY! error
(5:57) Insiders beta now
Keep Leading Zeroes When Opening CSV – 2502
D on YouTube asks: How do I stop Excel from removing leading zeros when opening a file?
Changing a column from general to text does not work for me, as the “0” has already been removed.
09805 becomes 9805, for example.
The files I’m opening does not make it clear that the zero has been removed.
I’m working in spreadsheets with 200k rows.
So stopping Excel from removing them in the first place, would be preferable
Bug Or Feature? Excel’s WRAPROWS 2501
While working on video 2500, I ran into something that seems buggy with the WRAPROWS function in Excel. Is it a bug? Or a Feature?
Four New Excel Features Debut!
Wyn’s book for shipment to USA:
https://mrexcel.e-junkie.com/product/1750249/Power-BI-for-the-Excel-Data-Analyst-Print-Book-to-USA-Only
This video covers six exciting things:
1) The Show Changes feature which had been in Excel Online is now in Desktop Excel
2) Data Validation partial matching is working again
3) There is a new look for Excel Options
4) A new IMAGE function comes to Excel to display an image
5) Wyn Hopkin’s new Power BI for the Excel Analyst book… it drops on November 1, but I have 3 cases to ship now…. US only.
6) The 14 functions for text and array manipulation are coming to general availability.
Table of Contents
(0:00) Four new features today
(0:54) Show Continue reading “Four New Excel Features Debut!”
Excel & Power BI Data Analysis Complete Class in One Video – 365 MECS 04
Download Excel Zipped Folder with all files: https://excelisfun.net/files/MECS04DownloadFiles.zip
Course taught by Excel MVP and Highline College Professor, Mike Girvin.
Topics in video:
1. (00:00) Introduction
2. (03:52) What is Data Analysis?
3. (04:37) Sorting and Filtering. Example 1 & 2
4. (13:24) FlashFill. Example 3.
5. (15:58) Standard PivotTable with Summarize Values By and Show Values As calculations. Example 4.
6. (20:25) Power Query to import, clean, transform and refresh data and load to Excel Worksheet. Complete Introduction to Power Query. Example 5.
7. (34:07) First look at Power Query’s Functional Language: M Code
8. (55:05) Power Query to Import and combine 12 tables into one Continue reading “Excel & Power BI Data Analysis Complete Class in One Video – 365 MECS 04”
Excel Switch All Pivot Value Fields To Average or Max 2498
Jeremy asks Is there a way to select multiple headings and change all of their “Summarize value field by” at once? I have hundreds of headings, each time I want to change their value by Max, Min, Average, I have to select one heading at a time, it is extremely labor intensive. Is there a way to change field calculation for multiple headings at once?
This episode shows some simple VBA to do this quickly.
How to create a personal macro workbook: https://www.youtube.com/watch?v=ld2SU2zJvHA
Table of Contents
(0:00) Welcome
(0:41) VBA
(1:55) Looping each field
(2:35) Copying for Sum, Max, Min
(3:10) Other calculations
(3:42) Easier way Continue reading “Excel Switch All Pivot Value Fields To Average or Max 2498”
Create Sequence of Time Values in Excel for Stock Market Price Data – Excel Magic Trick 1798
Download Excel File: https://excelisfun.net/files/EMT1798.xlsx
Learn how to create a sequence of time values for a Stock Price Template. Learn the “New School” Microsoft 365 Excel method and the “Old School” Method that works in any version of Excel.
Learn many important Excel Keyboard Shortcuts.
Topics:
1. (00:00) Introduction
2. (00:25) Microsoft 365 Excel Solution
3. (00:50) Time Values in Excel
4. (02:13) SEQUENCE Dynamic Spilled Array Function
5. (02:55) Build Template
6. (04:33) Copy Template
7. (04:50) Solution that works in any version of Excel
8. (05:48) Make Dates Dynamic too
9. (06:20) Copy Template to new worksheet (Fast Method to Copy Worksheet)
10. (06:57) Summary, Closing, Video Links
Excel Show Symbols For Gamma And Beta – 2497
This actually applies to any character that you want to display in Excel that is in the Unicode character set. Microsoft gave us functions for UNICODE and UNICHAR. It is not intuitive, but you have to use UNICHAR to solve this.
However, the websites showing the unicode are often showing the number in Hex, so you have to use HEX2DEC before passing the value into UNICODE.
Table of Contents
(0:00) What is gamma and beta
(0:13) Results of googling
(0:32) History of CODE and CHAR
(0:54) UNICODE isn’t it
(1:22) UNICHAR of HEX2DEC
(1:51) Displaying Beta in Excel
(2:21) ESPN Wrap up
(2:59) Retrieve Webinar
(3:30) Episode 2500 Continue reading “Excel Show Symbols For Gamma And Beta – 2497”