Category: Power Pivot

Prevent Excel From Changing Numbers On Import – 2494

Posted on by Annie D

I love this feature so much, I turned it off!
For decades, Excel has been performing default data conversions:
Remove leading zeroes
Keep first 15 digits of long numbers and convert to scientific notation
Convert digits surrounding an E to scientific notation.

New in Insiders beta, Excel will ask if you want to do all three of those things. The first time, I was happy to click Don’t Convert.
But I think the real solution is to embrace all three options and turn all three of them off! They have been annoying us for so long. Why would you want Microsoft to ask, “Hey, should we Continue reading “Prevent Excel From Changing Numbers On Import – 2494” →

Validation BUG & More on Excel Shapes & VBA

Posted on by Annie D

Episode 2493
Why is the drop-down arrow not showing up when I create the Excel validation with VBA? It appears after I click Save. But I don’t want to save since these numbers are a temporary scenario and I plan on closing without saving.
Also: Using the Selection Pane in Excel to see all shape names
How Excel names shapes.
Why Excel renames Shapes during workbook copy
How to use VBA to detect which shape was selected.

Table of Contents
(0:00) Bug with Shapes in VBA
(0:31) Excel Validation drop-down arrow missing
(3:05) VBA code to delete shapes
(4:07) How Excel Names Shapes
(4:44) Selection Pane in Excel
(5:47) First Validation arrow Continue reading “Validation BUG & More on Excel Shapes & VBA” →

Excel Improvements To TEXTBEFORE

Posted on by Annie D

Episode 2492
The Calc team at Microsoft released three changes to new functions in late May 2022. The improvements to TEXTBEFORE and TEXTAFTER give you options for dealing with situations where the delimiter that you are searching for is not found.

Table of Contents
(0:00) Former problem with TEXTBEFORE
(0:37) New syntax
(1:11) Using Match End
(1:31) Using If Not Found
(1:47) Why both?
(2:12) Thanks Excel Team
(2:27) Nancy Faust
(2:34) Like & Subscribe
(2:41) Excel Video Courses

Data From Picture is here in Excel!

Posted on by Annie D

Episode 2490:
The Android phone has had it for a long time, but the new Data From Picture has finally reached the Office Insiders beta channel on desktop Excel. Bill gives this new feature a spin with various pictures of data.

Table of Contents
(0:00) Speech Recognition 1989 to today
(1:10) From Picture near Power Query tools
(1:35) Creating data picture
(2:00) Data From Picture no gridlines
(3:40) Cleaning the results
(4:15) The numbers all match
(4:30) Gridlines don’t help
(5:35) Right-align headings
(7:20) Need to proof
(7:50) With real photo test 1
(8:40) From clipboard test 2
(10:03) This will improve

Excel To Schedule Prison Haircuts Only On Saturday – 2484

Posted on by Annie D

This video is all about the Excel function for WORKDAY.INTL. It allows you to make sure that you are always scheduling an event to occur on a Saturday, or Tues/Thurs or Friday.
The secret here is the alternate syntax for the Weekend argument.
Table of Contents
(0:00) Find a particular Saturday in Excel
(0:25) WORKDAY introduction
(1:37) Find a particular Saturday in Excel
(2:32) Find the next Tuesday
(5:02) Find next Tuesday or Thursday
(5:46) Handling holidays
(6:58) Wrap-up

Excel XLOOKUP Return Non Adjacent Columns – 2488

Posted on by Annie D

Another question from the UCF Accounting Conference. JoAnne asks… XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?

Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487

Posted on by Annie D

A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low.
The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET.
This lets you create something that is like a pivot table, but there is no need to refresh as the data changes.
Table of Contents
(0:00) Welcome
(0:16) Formulas to Values
(1:08) UNIQUE list of products
(1:35) SUMIFS by Continue reading “Sorted Summary Report WIth Totals In Excel Without A Pivot Table – 2487” →

VLOOKUP Into “Vendor – URL” With TEXTBEFORE – 2486

Posted on by Annie D

Today’s Excel question from Willie at the UCF Accounting Conference:
I am using a vlookup to get some information from a vendor info database that comes from the credit card website.
 
My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso – cadet.biz” and in the table where I do the lookup the name is just “Contoso”.
 
I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit Continue reading “VLOOKUP Into “Vendor – URL” With TEXTBEFORE – 2486″ →

Security Risk Microsoft Has Blocked Macros From Running – 2485

Posted on by Annie D

A new pink bar warning is appearing in Excel, Word, and PowerPoint telling you that there is a security risk and that your VBA macros have been blocked.
This warning is designed to make you ask, “Is there a valid reason for this person to be sending me a macro?” If there is, I will show you the easy steps to unblock the workbook.
This is called the Mark of the Web.
Kevin Lehrbass Word Game is here: https://www.myspreadsheetlab.com/word-game/

Table of Contents
(0:00) Kevin Lehrbass has a Wordle game in Excel but the macros are blocked with the pink bar.
(1:08) Continue reading “Security Risk Microsoft Has Blocked Macros From Running – 2485” →

Excel Can You Merge Cells In A Table – 2483

Posted on by Annie D

Uncle S is trying to have a formula copy to new rows. So a table seems like a great way. But he also has merged cells and the table unmerges those.

In this video, two surprising Table tricks:
First, a table can contain Center Across Selection, which is better than Merging Cells in Excel.
Second, a surprising discovery from the Excelforo channel about new headings in a table.

Table of Contents
(0:00) Auto-copying formulas to new rows in Excel
(2:31) Can a table have a merged cell
(4:00) New table columns using custom lists in Excel

Excel Pivot Table Months Or Weekdays Are Alphabetical – 2482

Posted on by Annie D

You have a pivot table in Excel. The months and weekday names are appearing alphabetically instead of in calendar sequence. This happens when your pivot table is based on external data or the data model.
There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays.
If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.