Excel Advanced

In this lesson, I demonstrate how to use PowerQuery in Excel to Remove and Keep duplicates. I show you how to input your data into PowerQuery after creating a table, and then two examples for removing duplicates as well as two examples for keeping duplicates first in the whole table, and secondly just in one column. Finally, I show you how to get this data back into an Excel worksheet so you can continue working with it.

Grouping Text Field in a PivotTable is easy. You simply select the fields, right-click and select Group. For example, you may want to group certain employees by department, certain car brands by country, and customers by consumer vs. commercial customer. You usually group text fields in a PivotTable when the data source doesn't have the grouping field you need.

Filter in PivotTables only allows one filter by default. To filter by more than one field, you need to turn on the PivotTable Option - Totals and Filters tab and check Allow multiple filters per field.

In this lesson, we will use the XLOOKUP to group text for our PivotTable. In a past lesson I showed how to group text directly inside the PivotTable, but today we're going to use XLOOKUP to add the text to our data and then create the PivotTable. I'll also cover why I prefer XLOOKUP to VLOOKUP.

When you share a PivotTable in Excel, even if you delete the data source worksheet, the data source is still attached to the PivotTable. It is called PivotTable cache. It is possible to share PivotTable results without the underlying data. You need to use Paste Special along with Paste Formats.

Excel PivotTables are great for analyzing data and summarizing data. You can change your PivotTable Field List to make PivotTables easier to use. You can also search and change the order of your fields. Join me as I demonstrate six tips for working with the field lists.

The IMAGE function inserts images into cells from a source location along with the alternative text.

This function only works in

• Excel for Microsoft 365

• Excel for Microsoft 365 for Mac

• Excel for the web

• Excel for iPhone

• Excel for Android phones

The GCD Function finds the greatest common denominator. For example, divide 6 by 3, and the GCD is 3. Three divides into 3 evenly and into 6 evenly.

Other examples:

150 / 50 = 3

325 / 20 = 5

90 / 9 = 9

In this lesson, I'll be discussing three Excel functions that help you join text together: CONCATENATE, TEXTJOIN, and the CHAR function.

I start by demonstrating how to use CONCATENATE to combine the last name and first name columns with a space in between. If you want to use a different delimiter, such as a comma, you can easily do that too.

Next up is TEXTJOIN, a powerful function that combines data from multiple cells with the added option to ignore empty cells and specify a delimiter.

Finally, I cover the CHAR function, which allows you to insert line breaks in your text. I'll show you how to use CHAR in combination with TEXTJOIN to put address one above address two, with a line break in between.

Scottish woman checked her bank account after six decades. Her grandmother's $3.35 initial deposit, made back in the 1950s, has grown to $335. While visiting her grandmother, her grandmother opened an account for her in TSB Bank in 1954. I became curious, we know her initial deposit, and we see the balance now is $335 after 68 years. No additional payments were made. I was wondering what annual interest rate did she earn? Excel's RATE function gave me the answer. It was slightly over 7%.

Excel has dynamic array functions. Two of the dynamic array function are SORT and UNIQUE. I will combine SORT and UNIQUE and also combine them with COUNT and the COUNTA functions.

In this lesson, I introduce and explore the new 'GROUPBY' function in Excel, currently available only for beta channel users on both Windows and Mac. We dive in-depth into the features of the function with examples, such as grouping by car brands, summing up the revenue, and applying various Excel functions. I also discuss all the available arguments, how to format them correctly, and how to include additional fields like the cost column. I show how to play around with other attributes like sort order and filters. This real-time exploration allows you to see immediate changes without the need to refresh like in pivot tables. Stay tuned to further explore the 'PIVOTBY' function in my upcoming video.

In this detailed lesson, I demonstrate how to use and combine the 'IF', 'AND', and 'OR' functions in Excel. I start by explaining the data setup and subsequently illustrate the use of these functions in varied score scenarios. My goal is to accomplish a conditional setup based on attendance at training and the scores achieved in a test. I show real-time testing of these function combinations in multiple scenarios to ensure easy understanding.

I demonstrate how to efficiently remove duplicate data in Excel using Power Query. I start the tutorial by explaining why traditional methods, like conditional formatting, might not always be effective for finding duplicates, specifically when there is no unique primary key.

I then discuss how to use the 'Remove Duplicates' feature under the Data tab, highlighting its benefits and limitations.

For a more dynamic solution, I guide you through the application of Power Query to delete duplicate records. This approach allows the file to continuously update itself even when new data is introduced, thus automatically maintaining the removal of duplicates.

I demonstrate how to locate the last day of each month, leap years included, and even how to look forward and backward in terms of months and years. I show how to utilize EOM to find the first day of the month, a task for which Excel lacks a specific function.

In this lesson, I'll guide you through the basics and then combine both functions to master text manipulation.

Here's what I cover:

* TEXTBEFORE function: Learn how to isolate text segments before a specific delimiter in Excel, with hands-on examples.

* TEXTAFTER function: Flip the script and extract text following your chosen delimiter, understanding the function's versatility.

* Combine TEXTBEFORE and TEXTAFTER: Discover how to nest these functions to capture the essence of text lying in the middle of your data strings.

* Using the FORMULATEXT function: Keep track of the formulas applied.

In this lesson, I demonstrate how to use the DATE function in Excel. I take you through two examples: how to combine three cells into a single date and how to calculate 65 years from a certain date using the DATE function.

Syntax: DATE(year,month,day)

Assume that cell A2 has the month, B2 has the day, and C2 has the year. A2 contains August as the number 8, B2 contains the ninth as the number 9, and C2 contains 2024. In D2, = DATE (C2, A2, B2) would return 8/9/2024. Notice the function goes year, month, and day.

In this lesson, I demonstrate the process of separating date and time in Microsoft Excel when they're combined in a single cell. I begin by introducing the concept of using the INT function for this purpose, highlighting that in Excel, dates and times are essentially numbers.

I then showcase how to generate a date and time stamp using the NOW function and proceed to separate the date from this combination by employing the INT function, which isolates the date as a whole number corresponding to that date. Additionally, I share a unique tip on how to extract just the time portion by subtracting the integer value of the cell containing both date and time from its total value.

In this tutorial, I introduce you to converting text into numbers and dates in Excel using the VALUE function, the multiply by one trick, and the T function.

I explain how to identify text masquerading as numbers or dates by alignment, the count indication in the Status Bar, and filter options. I demonstrate how to convert text to dates using the VALUE function and how to ensure correct date formatting.

I also show an alternative method using multiplication by one and provide a bonus tip using the T function for distinguishing text from numbers.

In this lesson, I demonstrate two powerful Excel functions – UNIQUE and FILTER. Learn how to use these functions on both Mac and Windows as well as in Excel for the web and Excel 2021. I walk through practical examples, including finding unique departments and filtering training sign-ups. By the end, you’ll know how to combine these functions to streamline your data analysis.

In this lesson, I demonstrate the powerful VSTACK and HSTACK functions in Microsoft Excel 365. Join me as I show you how to efficiently combine and manage your data vertically and horizontally. I'll walk you through practical examples, including dynamic arrays, copying headers, and even merging data from different worksheets. Perfect for enhancing your Excel skills and boosting productivity. Don't miss out on this essential tutorial!

In this lesson, I explain the concept of the Geometric Mean using Microsoft Excel. I compare it to the Average and walk through three detailed examples, including a financial investment scenario. Learn how the Geometric Mean provides a more accurate central tendency in certain cases by using multiplication rather than addition, unlike the simple Average. This includes demonstrating Excel functions like GEOMEAN and PRODUCT to calculate the Geometric Mean efficiently.

The geometric mean is particularly useful in various scenarios because it provides a more accurate measure of central tendency for data sets that are multiplicative or vary exponentially.

Here are a few reasons why you might use the geometric mean:

1. Proportional Growth: It’s ideal for data that grows or shrinks proportionally, such as interest rates, population growth, or investment returns.

2. Normalization: It helps in normalizing data that spans several orders of magnitude, making it easier to compare.

3. Multiplicative Relationships: When dealing with ratios or percentages, the geometric mean gives a better central tendency than the arithmetic mean.

4. Logarithmic Data: For data that is log-normally distributed, the geometric mean is more representative of the central location. For example, if you’re comparing the performance of different investment portfolios over time, the geometric mean will give you a better sense of the average rate of return, accounting for the compounding effect.

In this lesson, I demonstrate how to use Microsoft Excel's TOCOL function to effectively combine data from multiple columns into one. I'll show you the function's application in Excel for M365, Excel M365 Mac, and Excel for the web. Learn how to handle blanks and errors in your data, and see how to adjust the function to scan by columns instead of rows. Plus, get a bonus tip on changing the order of data collection.

In this lesson, I demonstrate how to utilize the TOROW function in Excel. Starting with a list of cities displayed from left to right, I highlight how to enter the function in cell B6. By using the required array argument, I show how the TOROW function handles blanks and errors, and adjust the function to ignore them using different argument values. Additionally, I explore the scan by columns feature, demonstrating how the data arrangement changes.

In this lesson, we look at joining text with dates. The problem with joining text and dates is the dates do not come as a date format. For example, joining the employee ID 2025, with the date November 7, 2022, will display as 202544872. What we may want to see is 2025/11-07-2022 or 2025/2022-11-07. The Text function allows us to format the date correctly. We will also look at filtering data and building a PivotTable with our helper column.

In this lesson I demonstrate the requirements for formatting data to enable the use of Copilot in Excel.

The focus of this lesson is using Copilot in Excel to perform data analysis and ask questions. I demonstrate this by exploring various data like car brand, dealerships, revenue, and cost. I ask Copilot questions about total revenue per month, total revenue per quarter, top three car brands by revenue, and group revenue by rep. Copilot not only gives accurate results but also generates charts and graphs for better data visualization.

Get Started with Copilot in Excel - Let me walk you through data setup and Copilot-assisted formulas. In this lesson, I demonstrate how to use Copilot in Excel, specifically focusing on setting up data and using formulas. I show how to save data as a table and the necessity to have your data in Microsoft OneDrive or SharePoint for Copilot to function. Then we look at how Copilot can generate formula suggestions and how to add these to your table. Copilot can interpret your questions to create new formulas and columns.

Here's a detailed analysis of Microsoft Copilot in Excel. I delve into Copilot's functionalities with a series of tests, emphasizing its precision and capability in handling a variety of tasks within Excel.

There's a need for accuracy in Copilot's operations, particularly in data filtering, a distinguishing feature from its application in other Microsoft tools like Word or PowerPoint. Throughout my review, I systematically examine Copilot's features through numerous tests, including sorting, filtering, chart creation, PivotTables, and more.

In this lesson, I test Copilot by giving it three conditional formatting requests in the same prompt. I ask Copilot to highlight three different items in the same column, using three different colours.

In this lesson, I test Copilot in Excel with over 90,000 records to see if it handles large data sets effectively. I demonstrate sorting, conditional formatting, and formula applications including dollar and percentage differences between fiscal years.

In this lesson, I demonstrate Copilot's latest feature in Excel that allows us to work with ranges instead of just tables. While the feature is still evolving, it's already showing great promise for streamlining our Excel workflows. I'll walk you through the process step-by-step:

1. Paste some sample data into Excel

2. Use Copilot without saving or creating a table

3. Follow Copilot's prompts to enable Autosave

4. Perform a complex sort using Copilot

5. Analyze the results to confirm accuracy

In this lesson, I demonstrate how to highlight any row where the state is Georgia, and the FY24 value is over 1,000 with a yellow background. Copilot handles the entire process, from selecting the data to using the AND function in the Conditional Formatting rule. After applying the rule, I'll quickly test it by changing a value, and the row instantly updates with a yellow background. I'll also review the Conditional Formatting manager to show the rule that was created.

In this lesson, I demonstrate how to leverage the advanced capabilities of Microsoft 365 Copilot in Excel to convert revenues from various currencies to USD using XLOOKUP. I walk you through creating a table and utilizing Copilot to automatically generate the necessary formula for currency conversion

In this lesson, I demonstrate the new features of Copilot Wave 2, released in October 2024, and how to use Copilot in Excel for some advanced conditional formatting. I'll show you how to color-code car brands in column B without needing to reference the column or 'Brand', thanks to Copilot's improved intelligence. You'll see how easy it is to filter and sort by color after using this feature.

In this lesson, I demonstrate a new feature in Excel's Copilot, released as part of Wave 2 in October 2024.Copilot now remembers previous chat histories. I show how to separate first names and last names using Copilot and test if it retains the chat history after closing and reopening the file. Additionally, I illustrate another example by calculating profit from invoice and cost, and confirm that Copilot remembers the steps.

I demonstrate how to use Copilot in Excel for advanced text splitting and data manipulation. Specifically, we'll split text in column B which includes countries after a comma, with an exception for USA states. I'll show you how to use Copilot to add a new column and automatically place 'USA' for states, or the respective country name for other entries. This includes employing a nested IF statement with the LET function. Follow along to see how Copilot handles this task efficiently.

I demonstrate how Copilot can separate a list of attended classes into individual columns using a custom prompt with a delimiter. I walk you through my scenario of transforming training data into a clear yes/no column format. Watch as Copilot uses advanced nested IF functions to accurately categorize multiple classes. Plus, see a quick verification of the output and learn about the benefits of Copilot's efficiency.

In this lesson, I demonstrate how to use Advanced Conditional Formatting in Excel to highlight cells based on specific criteria using Copilot. Follow along as I set up the prompt to highlight cells green when there's a 'yes' in two or more columns, and verify the results.

In this advanced Excel tutorial, I show you how to remove duplicates that are running horizontally from left to right using PowerQuery. We start with a data range containing multiple email entries and redundant tags. I'll guide you through converting the range to a table, unpivoting the necessary columns, and removing the duplicates. This easy step-by-step process ensures your data is clean and accurate.

In this video, I demonstrate how to add and use checkboxes in Microsoft Excel, applicable to Microsoft 365 for Windows, PC, and Excel on the web. Learn how to insert checkboxes, remove them, use Autofill for multiple checkboxes, and count the number of checked and unchecked boxes using the COUNTIF function. I also share tips for effective use and provide a quick demonstration.

Can Microsoft Copilot in Excel work with Advanced Functions, like XLOOKUP? XLOOKUP is one of my favorite functions. In this lesson, I test how well Copilot can utilize XLOOKUP. I demonstrate this using a lookup table to convert revenue from various country codes to USD.

Join me as I demonstrate some cool features of Copilot in Microsoft Excel. Learn how to utilize Copilot to discover intermediate keyboard shortcuts, apply Data Validation effectively, and find out about lesser-known yet powerful Excel functions like LET.

In this lesson, I demonstrate how to use Copilot to perform an advanced data comparison task. I have two lists of names in column A and column C, and my goal is to extract names from the second list that are not in the first list and display them in column D. I walk through the process of using Copilot, evaluating its output, and making slight adjustments to ensure the accuracy of the extracted data.

I demonstrate five different methods for calculating the compound annual growth rate (CAGR) with Excel, including manual calculations and Excel formulas. I also explain the rule of 72, an essential financial rule useful for estimating the time it takes for an investment to double. As a bonus, I show a formula to figure out the future value of an amount without manual calculations.

In this lesson I cover 7 tips that I often get asked about, they are all extremely easy but great time savers.

In this advanced Excel tutorial, I dive into how to leverage Excel's Scenario Manager for forecasting the impact of changing interest rates on house prices, an idea sparked by real estate expert Barbara Corcoran. Starting with naming ranges, I walk through the process of calculating the potential change in the purchase price of a house using actual market data from Metro Atlanta as of April 2024.

I then show how to create different scenarios reflecting various interest rate drops to assess how they might influence housing demand and pricing. This tutorial highlights the practicality of distinguishing between input and dependent cells. Additionally, I discuss the crucial elements of PITI (Principal, Interest, Taxes, and Insurance) in understanding mortgage payments and point out other additional costs like PMI and HOA fees.

I wrap up by demonstrating how to summarize these scenarios on a new worksheet, providing a detailed analysis of how adjustments in interest rates could affect home pricing, down payments, and monthly mortgage payments.

In this lesson, I dive deep into Market Capitalization, demonstrating how to use Excel for stock analysis and exploring the potential of long-term S&P 500 returns. Whether you're a finance enthusiast or just curious about stock market metrics, this tutorial offers valuable insights and practical Excel techniques. Learn how to leverage Excel's stock data type to quickly analyze company financials, understand market capitalization categories, and see how compound interest can potentially grow your investments over time. I also explain the Rule of 72 and provide insights into the actual composition of the S&P 500 index.

In this lesson, I demonstrate how to pull the list of S&P 500 companies from Wikipedia into Excel and then use Excel's Stock Data Type with those companies. Here's what we'll cover: I show you how to use Excel's 'From Web' feature to import the S&P500 list, navigate through multiple tables on a web page, and load the data into Excel. Then, I demonstrate how to use Excel's Stock Data Type with the imported symbols, including how to troubleshoot any symbols that aren't automatically recognized. I also explain why there are 503 companies in the S&P 500 index and show you how to access various stock data points using Excel's Stock Data Type.

In this lesson, I demonstrate how to download 30-year mortgage loan data from the Federal Reserve Bank and analyze it using Excel. I guide you through the steps of formatting the data, creating charts, and performing various calculations including averages, medians, and conditional formatting. I also discuss the impact of changing interest rates on mortgage payments and home prices, and provide insights into historical mortgage rates over the past 53 years. Stay tuned for more updates following the Federal Reserve's upcoming meeting.

Learn how to use the UNIQUE function to find out the number of unique employees who took courses, combined this with the COUNTA function to get precise numbers, and effectively utilize the AutoSum feature. Discover how to efficiently move columns and rows using simple keyboard and mouse actions and learn to handle the RANDBETWEEN function to generate numbers that won't change after sorting or filtering.