Category: Excel

Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/14-M365ExcelClassVideoFiles.zip
This video teaches how to unpivot a cross tabulated table and convert it to a proper table so you can perform data analysis.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:25) What is a Cross Tabulated Table and What are Benefits of Converting to Proper Table?
3. (01:14) UnPivot feature in Power Query.
4. (01:52) Table.UnpivotOtherColumns M Code Function
5. (02:21) Sort with Resulting Table
6. (02:32) PivotTable Continue reading “Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14” →

Excel How to Sum Non Contiguous Cells #shorts

Posted on by Annie D

How to use the AutoSum when you need to sum non-contiguous cells in Excel.

This video was created to answer these search terms:
How To Sum Non-Contiguous Cells In Excel?
How To Sum Specific Cells In Excel Formula
How To Count Sum For A Particular Cell In Excell
How To Count Sum Of Certain Cells In Excel
How To Select Cells In Excel For Sum
How To Sum Certain Cells In Excel
How To Sum Different Cells On Excel
How To Sum Different Columns In Excel
How To Sum Different Rows In Excel
How To Sum Select Cells In Excel
How To Sum Two Cells In Excel

The Excel Status Bar Uses Number Format From Active Cell – 2386

Posted on by Annie D

Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom.

Also in this episode: the four zones of Custom Number Formats.

And, the winner(s) of a free Excel PDF.

Consolas Font For Excel 2362

Posted on by Annie D

What is your favorite font in Excel? Anna and Lisa showed me the Consolas font. It is great because you can tell zero from O and one from lower case L.

Table of Contents
(0:00) Axel font for Excel
(0:19) Typewriters without a 1 key
(0:54) People who type L instead of 1
(2:04) How I met ExcelisFun
(2:37) Make a custom Office Theme with Consolas font
(3:32) Applying a custom office theme
(3:45) Making a theme be default
(4:09) Default Font in Excel Options

Add YTD To An Excel Pivot Table – 2385

Posted on by Annie D

Diane has a fairly complex pivot table. She is using GETPIVOTDATA to pull data from the pivot tables to a summary report in another workbook. In another section of the report, she needs to pull YTD. But this complicates the formula, because she has to add an expanding range as the months continue.
I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.

Power Query and M Code to Import &Transform Data: 20 Epic Examples! 365 MECS 13

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/13-M365ExcelClassVideoFiles.zip

This video is a comprehensive lesson in Power Query Importing & Transformation Data in Excel & Power BI. This video shows extensive M Code to create formulas to convert bad data into proper data sets that can be used for analysis.

This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

This video covers.
1. (00:00) Introduction and video topics
2. (02:24) On-Premise Folder and File Path
3. (03:11) Importing TXT and CSV files
4. (08:08) Create Dynamic File Continue reading “Power Query and M Code to Import &Transform Data: 20 Epic Examples! 365 MECS 13” →

Adding Subtotals In Excel

Posted on by Annie D

subtotal a column in excel
how to add subtotal function in excel
how to formulate subtotal in excel
how to subtotal sum in excel
how to use subtotal in excel for sum
using subtotals in excel to sum
how do you do a subtotal in excel
how to automatically subtotal data in excel
how to automatically subtotal rows in excel
how to calculate subtotals in excel
how to insert subtotals into excel
excel how to make subtotals
excel how to subtotal
excel how to use subtotals
how create subtotals in excel
how do i add subtotals in excel
how do i do a subtotal in excel
how do i do subtotal in excel
how do i use the Continue reading “Adding Subtotals In Excel” →

How To Remove Subtotals In Excel

Posted on by Annie D

excel how to get rid of subtotals
how do i remove subtotals in excel
how remove subtotals in excel
how to do subtotal remove all in excel
how to remove a subtotal in excel
how to remove all subtotals in excel
how to remove excel subtotals
how to remove subtotal excel
how to remove subtotal in excel
how to remove subtotals in excel
how to remove the subtotal in excel
how to undo subtotals in excel
remove subtotal in excel
how to take out a subtotal in excel
how to eliminate subtotals in excel
how to remove – from excel to show subtotal

Excel Challenge How Did Bill Manage To BREAK The Excel Status Bar Sum #shorts

Posted on by Annie D

There was a scary post online today… the Excel Status Bar sum was WRONG! But it is not wrong… it is a great trick that will win you bar bets from your Excel guru friends!

The Status Bar sum is CORRECT when you select from bottom to top!
But it is INCORRECT when you select from top to bottom!

Down in the YouTube comments, tell me how I managed to do this.

It required no VBA. You can easily do it yourself and freak out your Excel guru friends!

Don’t feel bad… when I first saw it, I could not figure it out Continue reading “Excel Challenge How Did Bill Manage To BREAK The Excel Status Bar Sum #shorts” →