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
Category: Excel
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”
Excel Formula to make First a 1, Second a 2, Third a 3, and so on. 6 Formula Examples. EMT 1797
Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Learn how to convert words such as First, Second and Thirds, to numbers such as 1, 2 and 3..
Topics:
1. (00:00) Introduction
2. (00:20) XLOOKUP solution
3. (01:09) XMATCH solution
4. (01:48) XMATCH Spilled Array Formula solution
5. (02:31) VLOOKUP Old School solution
6. (02:59) MATCH Old School solution
7. (03:31) Summary
8. (03:41) Closing, Video Links
Excel Filter By Month: Filter feature or FILTER function? Amazing Dynamic Formula Solution. EMT 1796
Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Learn how to filter a data set by month using the Filter feature and the FILTER function. See how to create dynamic Data Valication also..
Topics:
1. (00:00) Introduction
2. (00:17) Filter Feature for a data set with just one year of data
3. (01:20) Filter Feature for a data set with multiple years in in data set
4. (02:00) Create Dynamic Data Valication Dropdown List for Year and Month See the functions: SORT, TEXT, UNIQUE
5. (05:16) Continue reading “Excel Filter By Month: Filter feature or FILTER function? Amazing Dynamic Formula Solution. EMT 1796”
Excel Text To Columns or Split to Rows: Worksheet Formula or Power Query? Excel Magic Trick 1795
Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Learn how to use an Excel formula (TEXTSPLIT or FILTERXML functions) to split text from a cell based on delimiter to a vertical Column. This is Like Text To Columns.
Topics:
1. (00:00) Introduction
2. (00:18) TEXTSPLIT function
3. (01:28) FILTERXML function
4. (01:49) Power Query
5. (02:38) Summary
6. (02:43) Closing, Video Links