Excel PivotTable or Formula to Count Unique Dates for Each New Item. 4 Examples. EMT 1767

Download Excel File: https://excelisfun.net/files/EMT1767.xlsx
Learn how to count unique dates for each item four ways: PivotTable and DAX Implicit Measure, Spilled Array Formulas, FREQUENCY Function and LAMBDA function.
1. (00:00) Introduction
2. (00:18) Goal of unique count formula
3. (00:30) Data Continue reading “Excel PivotTable or Formula to Count Unique Dates for Each New Item. 4 Examples. EMT 1767”

Excel Pivot Table Fiscal Year Date Rollups – 2452

Check out my Excel Guides on Retrieve: https://mrexcel.retrieve.com/#/

A question from the Las Vegas IMA: How do you do date rollups in Excel when the company is on a fiscal year that does not end on December 31?

This Continue reading “Excel Pivot Table Fiscal Year Date Rollups – 2452”

Conditional Formatting to Highlight Missing Sequential Incident Numbers. Excel Magic Trick 1766

Download Excel File: https://excelisfun.net/files/EMT1766.xlsx
Learn how to.
1. (00:00) Introduction
2. (00:38) Why we build formulas in cells before pasting formula into Conditional Formatting Dialog Box
3. (01:19) Logic for formulas to find missing numbers in the incident number
4. (02:09) Create Continue reading “Conditional Formatting to Highlight Missing Sequential Incident Numbers. Excel Magic Trick 1766”

Wow! Excel Custom Data Types From JavaScript API – 2451

How to create your own custom data types in Excel using ScriptLab and the Javascript API.

Important Links:

Try out the workbook with my custom data types: https://1drv.ms/x/s!As7G72Sl487JliYRnUs0P4hYYNQj?e=rIL25J

Github for the example using a Custom Function:
<a href="https://gist.github.com/smozgur/a34ad495fb75e19c3e21904875ef0170" Continue reading “Wow! Excel Custom Data Types From JavaScript API – 2451”

Excel Custom Data Types From Power BI – 2450

The data types gallery with Geography, Stocks, Currency, and Wolfram is mildly interesting. But I think the real goal is to get your own company data in the Data Type. I want it to be a very rich experience, just Continue reading “Excel Custom Data Types From Power BI – 2450”

New! Copy Any Status Bar Number To Clipboard In Excel – 2449

The Excel team snuck a new feature into Excel and I didn’t realize it for a while. Thanks to Excel MVP Abiola David for realizing that you can left-click any number in the status bar to copy that number to Continue reading “New! Copy Any Status Bar Number To Clipboard In Excel – 2449”

Excel Statistical Analysis 08: X-Y Scatter Charts for Linear Data: Excel Chart? Analyze Data Button?

Download Excel File: https://excelisfun.net/files/Ch02-ESA.xlsm
Learn about how to create X-Y Linear Data Scatter Charts from X-Y data in Excel using Excel Charts and the Microsoft Artificial Intelligence Data Analyze Button.
Topics:
1. (00:00) Introduction
2. (00:34) First X-Y Scatter Data Set: Continue reading “Excel Statistical Analysis 08: X-Y Scatter Charts for Linear Data: Excel Chart? Analyze Data Button?”

Random Combination Of PBPBBP – 2448

Jon wants to generate 6-letter sequences using only the letters B and P.
So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE.
Formulas Continue reading “Random Combination Of PBPBBP – 2448”

Excel Statistical Analysis 7: Cross Tabulation, Visualizations, AND Logical Tests, Simpson’s Paradox

Download Excel File: https://excelisfun.net/files/Ch02-ESA.xlsm
Learn how to create a Cross Tabulated Report using a PivotTable and the appropriate visualizations. Lean about in the internal calculation in a cross tab report: AND Logical Test. Learn about how a hidden Continue reading “Excel Statistical Analysis 7: Cross Tabulation, Visualizations, AND Logical Tests, Simpson’s Paradox”

Excel Statistical Analysis 06: Frequency Distributions, Visualizations & Skew for Quantitative Data

Download Excel File: https://excelisfun.net/files/Ch02-ESA.xlsm
Learn about creating frequency distributions and visualizations for quantitative data, including Column Charts for Discrete Data, Line and Clustered Column charts for Date Data and Column Chart Histograms for Continuous Data.
Topics:
1. (00:00) Introduction
2. (00:35) Continue reading “Excel Statistical Analysis 06: Frequency Distributions, Visualizations & Skew for Quantitative Data”

Excel Statistical Analysis 05: Frequency Distributions & Visualizations for Categorical Data

Download Excel File: https://excelisfun.net/files/Ch02-ESA.xlsm
Learn about creating Frequency Distributions & Column and Bar Chart Visualizations for Categorical Data. See an Epic Summary of EVERYTHING you need to know about Column and Bar Charts.
Topics:
1. (00:00)
2. (00:22) Download Excel
3. Continue reading “Excel Statistical Analysis 05: Frequency Distributions & Visualizations for Categorical Data”

Excel Statistical Analysis 04: PivotTable & Power Query to Build Frequency Distributions

ESA #4: PivotTable Grouping to Build Frequency Distributions & Power Query to Import CSV Data
Download Excel File: https://excelisfun.net/files/Ch01-ESA.xlsm
Download CSV File: https://excelisfun.net/files/Ch01-ESA-AutoData.csv
Learn about how to use Power Query to import and transform CSV File data Continue reading “Excel Statistical Analysis 04: PivotTable & Power Query to Build Frequency Distributions”