Excel Olympics YouTube Channel

Search Youtube lessons, enter address lessons

Search
Generic filters
Exact matches only
Search in title
Search in content
Search in excerpt

Biggest Boost To My Excel Game in 2021

There are two tips hidden within this video and both have made me better and quicker in Excel.

Biggest boost to my Excel game in 2021.xlsx

This is the file I was using during the making of this YouTube tutorial. You can download it and follow along.

This video and the two tricks I describe are my gifts to the viewers for a great intro into a productive 2022. These two tricks sure made my life easier and hopefully will be an excellent attribute to your Excel game.

Combine Win Language Code Identifier – LCID and Excel

This YouTube video will show you how to use Cell Formatting and Microsoft Windows Language Code Identifier (LCID) to display month names and names of weekdays in any language you prefer and, in doing so - learn a foreign language :).

Learn a foreign language with Excel

The Excel file used in making of this YouTube tutorial.

Cell Formatting in Excel can do a lot of things. And we mainly use it to ensure Cells display the same value on any computer. And the biggest impact on the way cells display comes from Regional settings. Regional settings tell Excel how to display Dates. How to display time. what is the “short Date” format like in this region? And nowhere is this as visible as it is in the date formatting. Month names or names of weekdays differ all over the globe. And so do the displayed values of cells containing date values. It is for this reason alone, that LCID really matters in Excel. It is important as it allows you to set the region of the cell. So in effect even though you open an Excel with date format “January 1st 2022” in France or Italy, you can make sure it still says  “January 1st 2022”. But as a format of the cell!

Power Pivot vs regular Pivot Table

There are multiple differences between the Data model (Power Pivot) based Pivot Tables and regular Excel data based Pivot Tables. Most of them come from the fact that the Power Pivot (or the Data Model) is, in fact, an offline OLAP cube. This fact alone unlocks great potential in Pivot Table capabilities. In this video, we will focus on three of those capabilities.

Battle of the Pivots

All Excel files used in making of this YouTube tutorial.

Three differences between regular Pivot Tables in Excel and those that are based on the Power Pivot model, that we will show in this video:

  1. Different Calculations on SubTotal and Grand Total levels
  2. Column-based or Row-based Sets
  3. OLAP tools – convert to formulas command

Xlookup vs Power Query Joins

Three main differneces between using Power Query Joins and the new XLOOKUP function in Excel.

Xlookup vs Power Query Joins

The Excel file used in making of this YouTube tutorial.

A while back, I was talking about VLOOKUP alternatives, and even though everyone was focused on the VLOOKUP vs. INDEX&MATCH story, I proposed Power Query Joins were far superior to any function solutions in Excel. And then along came XLOOKUP. It was (out of the box) superior to VLOOKUP and INDEX MATCH combination. What about Power Query Joins. In this Video, I explore three reasons (or features) that still make Power Query Joins the most powerful solution for Joining two tables in Excel.

Year To Date Calculations on Fiscal Years in Power Query

Year To Date Calculations on Fiscal Years in Power Query

Working Excel File

This is the file I was using during the making of this YouTube tutorial. You can download it and follow along.

This is a video that is part of the “Your Questions Answered” Series, and as far as I’m concerned, this is one of the main reasons why I created this Channel. In this video, we will be answering a question that Dennis proposed. How do you create Year To Date (Running Totals in a Year) Calculations by Fiscal Years in Power Query? This comes down to a whole lot of Indexing Magic, Custom Year-Month Indexing, and Mod function.