Category: Excel

Excel Power Query Import And Clean Fixed Width Text Files 2359

Posted on by Annie D

I was doing a seminar recently and many people at the company were getting reports downloaded as fixed width text files.

I encouraged them to start using the Get & Transform tools in Excel for cleaning this date. After the seminar, I created a text file with many of the issues that were in their workbooks.

Table of Contents
(0:00) Fixed Width Text File for Excel
(0:50) Excel Text Import Wizard UI for Marking Column Locations
(1:19) Finding column start locations for Power Query
(1:59) Importing to Excel from Text/CSV
(2:18) Power Query incorrectly guesses comma as delimiter
(2:42) Power Query make column wider
(2:55) Power Query removing Continue reading “Excel Power Query Import And Clean Fixed Width Text Files 2359” →

Remove All Excel Format to Help Fix Reports and Data Errors. Excel Magic Trick 1814 #Shorts

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1814-1815.xlsx
Learn how to remove all formatting, but leave the content in an Excel Worksheet.
Topics:
1. (00:00) Introduction
2. (00:05) Why Remove Formats?
3. (00:18) Apply Normal Style
4. (00:31) Clear Formats
5. (00:38) Keyboard shortcut #1
6. (00:56) Keyboard shortcut #2
7. (01:04) Keyboard shortcut #3
8. (01:24) Find and Fix “Numbers Stored As Text” Data Error
9. (01:45) Keyboard to convert Text Numbers back to Numbers
10. (01:49) Closing, Video Links

#shorts

https://excelisfun.net/files/12-M365ExcelClass.pdf

Excel Average Of Non-Zero Rows In Pivot Table – 2358

Posted on by Annie D

There are 7 pivot table secrets in this video, and you haven’t seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows.

The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted.

Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows.

I can not post the final formula here Continue reading “Excel Average Of Non-Zero Rows In Pivot Table – 2358” →

Average Difference Between Ship Date Column & Order Date Column – Excel Magic Trick 1813 #Shorts

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1813.xlsx
Create Single Cell Formulas to calculate the average number of days to ship an order based on a Ship Date Column and a Order Date Column. Learn a Microsoft Excel 365 Formula and a formula that works in any version.
Topics:
1. (00:00) Introduction
2. (00:10) Helper Column Method.
3. (02:13) Microsoft 365 Excel and Excel 2021 Formula
4. () Formula that works in all versions of Excel
5. (02:34) Summary, Closing, Video Links

Excel Keeps Inserting A Space In the Name WITHA – 2356

Posted on by Annie D

We have a person in our roster with the first name of “Witha”. But Excel constantly changes this to “With A”. We are being stung by one of the thousands of entries in the Excel AutoCorrect Options dialog.

This video will show you how to stop Excel from autocorrecting Witha to With A.

Extract All Digits from Range & List in Column – Excel Magic Trick 1812 #shorts

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1812.xlsx
Learn how to Extract All Digits From Range & List in Column. Learn a Microsoft Excel 365 Formula and a formula that works in any version.
Topics:
1. (00:00) Introduction
2. (00:10) Microsoft 365 Excel Formula. See the functions: MID, SEQUENCE, SUM, LEN.
3. (01:25) Formula that works in any version of Excel. See the functions: INDEX, ROWS, MID, SUM, LEN, ROW and INDIRECT.
4. (02:13) Convert Text Numbers To Numbers
5. (02:34) Summary, Closing, Video Links

#Shorts

ChatGPT Versus 2 Humans Writing Power Query – 2355

Posted on by Annie D

Laura B has a tricky Power Query question today. She needs to delete all rows where B=C and D=E. Bill solves this in the Power Query Editor but knows it is not efficient.

He turns to ChatGPT – an Artificial Intelligence tool that is in a free research preview. Bill is amazed at how quickly ChatGPT writes M code for Power Query. But then he bangs his head against the wall trying to get it to work.

See why Stack Overflow has banned ChatGPT because it generates code that is usually wrong but looks like it could work.

Finally, we turn to Continue reading “ChatGPT Versus 2 Humans Writing Power Query – 2355” →

Visualizing Data and Building Dashboards in Excel & Power BI – 365 MECS 11

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/11-M365ExcelClassVideoFiles.zip
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
This video covers the basics of visualizing data, then shows how to create five different types of Dashboards.
Topics in video:
1. (00:00) Introduction
2. (00:21) Topics
3. (01:06) Why Visualize? Table or Visualization?
4. (03:44) Edward R. Tufte and High Data/Ink Ratio Rule and “No Chart Junk Rule”
5. (05:56) Tables Formatting Rules
6. (12:03) Conditional Formatting
7. (15:41) Column and Bar Charts to compare differences across categories
8. (24:00) Cross Tab Chart: Clustered Column / Bar & Stacked Column / Bar
9. Continue reading “Visualizing Data and Building Dashboards in Excel & Power BI – 365 MECS 11” →

Compare Two Lists, Extract Customers Not in Both: Excel Formulas or Power Query; EMT 1811

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1811.xlsx
Learn how to extract customer names who did not buy products from a specified list with worksheet formulas and Power Query.
Topics:
1. (00:00) Introduction
2. (00:41) Worksheet Formulas and Functions: FILTER, XMATCH, ISNA, AND, and COUNT.
3. (03:27) Formula Bonus #1: LAMBDA
4. (03:56) Formula Bonus #2: Use COUNT function to extract names when they bought no products, one or more products or all products
5. (04:35) Power Query using Group By feature and List.ContainsAny M Code function
6. () Summary, Closing, Video Links

Three Way Lookup with Power Query & XLOOKUP (Two-Way Lookup too). Excel Magic Trick 1810

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1810.xlsx
Learn how to do a 3-way or 2-way lookup in the Excel worksheet with XLOOKUP or in Power Query (in Excel and Power BI) using UnPivot and Merge.
Topics:
1. (00:00) Introduction
2. (00:05) 3-way lookup in the Excel worksheet with XLOOKUP
3. (01:45) 3-way in Power Query (in Excel and Power BI) using UnPivot and Merge
4. (03:46) Summary, Closing, Video Links

The Year 2023 is 7 Times 17 Squared – 2354

Posted on by Annie D

Happy New Year! I was scrolling through Facebook when Maria O pointed out that 2023 is 7*17^2. That seems interesting. Are there other times in our lifetime that the year is N*NN*^N?

It last happened in 2020. We have a run of them, coming up:
2023: 7*17^2
2025: 9*15^2
2028: 3*26^2

But even cooler is 2025, which is the perfect square of 45^2. Unless you were alive in 1936 or will be alive in 2116, the square year of 2025 will be the only square in our lifetimes.