Grouping in GROUPBY & PIVOTBY Functions!?!? Yes!!!! Frequency Distribution with GROUPBY EMT 1858

Download Excel File: https://excelisfun.net/files/EMT1857-1858.xlsx
Learn how to group in the GROUPBY and PIVOTBY functions to create Frequency Distributions.
Topics:
1. (00:00) Introduction to Frequency Distribution
2. (00:38) Start, Increment and Last Value for frequency distribution using MAX and CEILING.MATH functions
3. (01:12) Build Bins using SEQUENCE function
4. (01:42) Build GROUPBY Formula using LET and GROUPBY
5. (02:37) Look at PIVOTBY Formula
6. (02:43) Understanding Categories
7. (03:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #groupby #pivotby

Free Excel Webinar March 20 2024 from MrExcel #ExcelWebinar #Free

Hey Excel enthusiasts! 📊 Join me, Bill Jelen, from MrExcel, for an epic free Excel webinar on Wednesday, March 20th, 2024, starting at noon Eastern time in the US. 🚀 Excited to unveil my latest book, “Mr. Excel 2024: Igniting Excel,” packed with 33 new topics! 🔥 Tune in to my YouTube channel as I dive into some of my favorite highlights. Let’s excel together! Don’t miss out, mark your calendars! 📅 #ExcelWebinar #MrExcel2024 #IgnitingExcel
Watch the Webinar here on YouTube: https://www.youtube.com/watch?v=1LXvuSt2D5I

Generate Random Data Sets with Dates & Sales Numbers: RANDARRAY Function. Excel Magic Trick 1857

Download Excel File: https://excelisfun.net/files/EMT1857-1858.xlsx
Learn how to create a data set with dates and sales numbers using an Excel Formula.
Topics:
1. (00:00) Create Inputs for Randon Data Set
2. (00:37) Create Random Dates using RANDARRAY function
3. (01:21) Create Random Sales amounts using RANDARRAY & ROUND function
4. (02:00) Paste Special Values Trick!
5. (03:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #randomdata

Power Query Table.Group Function Fifth Argument: Comparer Function: Group without Key. EMT 1856

Download Excel File: https://excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using Power Query and the Table.Group fourth and fifth arguments: groupKind and comparer function. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:28) Group By Date Column with two aggregate calculations: join descriptions and sum amount.
3. (01:00) Text.Combine with Column Lookup to join descriptions
4. (02:10) Table.Group fourth argument: groupKind using GroupKing.Local or zero, 0
5. (02:56) Table.Group fifth argument: comparer function
6. Continue reading “Power Query Table.Group Function Fifth Argument: Comparer Function: Group without Key. EMT 1856”

GROUPBY Function to Group Transactions with No Transaction Number. EMT 1855

Download Excel File: https://excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using dynamic spilled array formulas. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:27) Teammates!
3. (00:38) Create Unique Identifier with SCAN
4. (03:06) GROUPBY Function
5. (03:28) HSTACK, SUM and ARRAYTOTEXT
6. (04:44) DROP Function to finish report.
7. (05:29) Summary
8. (05:45) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #reporting #groupby #groupbyfunction #PivotBy #pivottable #pivot

Excel Formulas to convert to Upper, Lower, or Proper Case #excel

Microsoft Excel Tutorial: Upper, Lower, Proper Case with a Formula in Excel.

In this tutorial, learn how to effortlessly convert text to upper, lower, or proper case using Excel formulas. Discover three essential functions that streamline the case conversion process. Whether you’re transforming text to lowercase with LOWER(A2), uppercase with UPPER(B2), or proper case with PROPER(C2), these functions have you covered. But beware of nuances – even PROPER doesn’t always nail the interior ‘C’ in McCartney, necessitating manual adjustments.
But what about real-world scenarios where your data extends beyond the conversion zone? Fear not! We’ll guide you through inserting a temporary column Continue reading “Excel Formulas to convert to Upper, Lower, or Proper Case #excel”

Excel Number of Days To End of Year for Reaching a Goal

Microsoft Excel Tutorial: Calculate Number of Days to End of Year for Reaching a Goal.

Thanks to Kikas for this idea!

Unlock the secrets of Excel productivity with our latest tutorial! In this comprehensive guide, we unravel the complexities of Excel calculations to streamline your task management process. Are you struggling to determine the number of items you need to complete daily to meet your year-end goals? Look no further! Speaker 1 expertly guides you through the process, breaking down intricate formulas into simple, actionable steps.

Discover how to calculate the precise number of items you need to tackle each day to ensure Continue reading “Excel Number of Days To End of Year for Reaching a Goal”

Worksheet Formulas to Group Transactions with No Transaction Number. Magic Trick 1854

Download Excel File: https://excelisfun.net/files/EMT1854-1856.xlsx
Learn how to group transactions with no key or invoice number using worksheet formulas. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:31) Key Column
3. (01:35) SEQUENCE function 1 to 5
4. (01:43) FILTER function to get dates
5. (01:59) FILTER and TEXTJOIN functions to get descriptions
6. (02:40) SUMIFS to add amounts based on key column
7. (03:03) Summary
8. (03:11) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel Continue reading “Worksheet Formulas to Group Transactions with No Transaction Number. Magic Trick 1854”

Excel Join First Name and Last Name as Proper Case

Microsoft Excel Tutorial: Join First Name and Last Name as Proper Case in Excel.

Buy my new book here: https://www.mrexcel.com/products/latest/

Welcome back to another Excel tutorial! Today, we’re diving into the efficient method of joining first names and last names in your spreadsheet while ensuring they are displayed in proper case. Join MrExcel as he walks you through the simple yet powerful techniques to achieve this task seamlessly.

Key Points Covered:
• Learn the fundamental formula using the CONCATENATE operator to merge first names and last names from different cells.
• Discover how to convert the combined text into proper case using Continue reading “Excel Join First Name and Last Name as Proper Case”

Excel Fast Way to Copy Formula to Bottom of Data in Five Keystrokes #excel

Microsoft Excel Tutorial: Fast Way to Copy Formula to Bottom of Data in Five Keystrokes.

This tip is from the book MrExcel 2024 – Igniting Excel. Buy the book here: https://www.mrexcel.com/products/latest/

Check out this video to learn how to copy formulas to the bottom of your data in Excel using just five keystrokes! 🚀
If you’re a fan of keyboard shortcuts, this trick will save you tons of time. Here’s a quick rundown:
1. Left arrow to B2.
2. Ctrl + down arrow.
3. Right arrow.
4. Ctrl + Shift + up arrow to select to the top.
5. And the magic: Ctrl + D Continue reading “Excel Fast Way to Copy Formula to Bottom of Data in Five Keystrokes #excel”

Formula for Sales Rep by Month Report: The Amazing GROUPBY Function! Excel Magic Trick 1853

Download Excel File: https://excelisfun.net/files/EMT1853.xlsx
Learn how easy it is to take daily dates and roll them up into a monthly sales rep report with GROUPBY and EOMONTH functions!
Topics:
1. (00:00) Introduction
2. (00:19) Sales Rep by Month Report with EOMONTH & GROUPBY & HSATCK Functions.
3. (01;52) Summary
4. (02:00) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp, #groupby #lambda #etalambda #eomonth #monthlyreport