Category: Power Pivot

Its Back The Flattened Pivot Table – 2504

Posted on by Annie D

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.

Exploring Excel IMAGE Function – 2500

Posted on by Annie D

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

Posted on by Annie D

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

Four New Excel Features Debut!

Posted on by Annie D

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 Switch All Pivot Value Fields To Average or Max 2498

Posted on by Annie D

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” →

Excel Show Symbols For Gamma And Beta – 2497

Posted on by Annie D

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” →

MrExcel’s Favorite Excel Tricks & Tips – 2022

Posted on by Annie D

Special ESPN Pricing:
https://www.mrexcel.com/news/special-ocho-deals-on-training-books-to-celebrate-excel-being-on-espn/

The Excel All Star Battle will air on ESPN2 at:
Friday August 5, 2022 at 5 AM Eastern. (2 AM Pacific. 10 AM in London.)
Sunday August 7, 2022 at 9AM Eastern. (6 AM Pacific, Noon in London.)
Monday August 8 (8/8!), 2022 at 11:30 PM Eastern. (8:30 PM Pacific – Prime Time, Baby! And 4:30 AM Tuesday in London).

Bill’s favorite Excel trick: Fast Formula Copy, as well as a lot of other items.

Table of Contents
(0:00) Historical re-enactment
(0:20) Data description
(1:00) Begins typing
(1:38) Adding text is #VALUE error
(1:57) & to Concatenate
(2:10) FORMULATEXT function
(2:36) Adding a space while joining Continue reading “MrExcel’s Favorite Excel Tricks & Tips – 2022” →

Generate Excel Formulas Using Artificial Intelligence – 2496

Posted on by Annie D

ExcelFormulaBot.com is a free A.I. bot that generates the Excel formula for any problem. You type a sentence. It suggests a formula. Free from David Bressler.

Table of Contents
(0:00) This is cool
(0:36) Formula for last word
(1:15) Formula for Prime Numbers
(2:23) Excel All Star Battle on ESPN8 The Ocho

Learn Excel From Mr Excel: SmartRoster Doesn’t Work, Microsoft Blocks Macros

Posted on by Annie D

Episode 2491 – While using SmartRoster software, you try to export a report to Word. The export does not start and you get a message that says, “Microsoft Word Security Notice. Microsoft Office has identified a potential security concern. Microsoft has blocked macros from running because the source of this file is untrusted.”

I recently posted episode 2485 that attempted to allow macros by using the Unblock checkbox in the workbook properties. That method won’t work here. Instead, you can temporarily set up a trusted location. This video shows you how.

Table of Contents
(0:00) Microsoft is blocking macros from running.
(0:53) Continue reading “Learn Excel From Mr Excel: SmartRoster Doesn’t Work, Microsoft Blocks Macros” →