This step-by-step video shows how to unpivot data in Excel, using Power Query. This creates better source data that you can use to build flexible pivot tables. And it doesn’t change your original data – you can leave that as is!

Video: Unpivot Excel Data with Power Query

Unpivot your Excel data, if needed, to change it from a horizontal report layout to normalized column layout. You can build better pivot tables from the revised data, and the original data won’t be affected. Win – Win!

In this step-by-step video, I show how to unpivot using Power Query, and there are written steps and a sample file on the Unpivot With Power Query page on my Contextures site.

Tip: For other ways to unpivot Excel data, without Power Query, go to the Fix Pivot Table Source Data page on my Contextures site.

Video Timeline

• 00:00 Introduction
• 00:27 Named Excel Table
• 01:24 Start Power Query
• 02:02 Rename Query
• 02:19 Delete Step
• 02:42 Remove Column
• 02:57 Unpivot Data
• 03:31 Rename Columns
• 03:50 Detect Data Type
• 05:10 Load Data
• 05:41 Refresh Data
• 05:59 Get the Sample File

Why Unpivot Data?

Before you can build a flexible pivot table in Excel, you might need to rearrange your source data.

For example, in the screen shot below, there is a separate column for each month’s sales amounts.

This layout makes it easy to enter data, but the monthly columns cause problems if you need to create pivot tables.

For example, in the screen shot below, you can see a pivot table built from that monthly sales data.

Instead of one pivot field for amounts, there is a separate pivot field for each month’s amounts.

It will be a painful process to get an annual total!

Unpivot the Data

To avoid that pivot table problem, you can “unpivot” the data, to get all the amounts in one column.

The screen shot below shows an example of rearranged data. Now,

• all the sales amounts are in a single column
• all the month names are in a single column
• each row has details for a single product sale

Follow the steps in the video above, to fix your data, if it’s in a horizontal layout.

And, for written steps, you can go to the Unpivot With Power Query page on my Contextures site.

Get the Sample File

To test the Power Query Unpivot technique, you can go to the Unpivot With Power Query page on my Contextures site, and download the Excel sample workbook.

The file is in xlsx format, and is zipped. There are no macros or queries in the file.

____________________________

Unpivot Excel Data with Power Query Step by Step Video

____________________________

Where does the time go? Only 556 days ago, we were celebrating Excel Day 44444, and suddenly it’s Excel Day 45000! And we’re still cleaning up from yesterday’s Pi Day Party! Who says numbers are boring?

What Day Number Is It?

How did we know it’s day 45000? How can you tell what day number it is in Excel?

First, type any date in an Excel  worksheet cell. Or, type this formula, to see the current date:

• =TODAY()

Then, change that cell to General format, to see the serial number for that date.

In the screen shot below:

• I typed today’s date in cell E2 – March 15, 2023 (I used the short date format 3/15/2023)
• Next, in cell F2, I typed this formula, to link to the date cell:
• =E2
• When I formatted cell F2 as General, it showed that today, March 15, 2023, is Excel Day 45000.

Multiplication Coincidence

And here’s a strange coincidence about Excel Day 45000:

• Today is in month 3
• Today is the 15th day of the month.
• Multiply those 2 numbers, and the result is 45!

I know, right!

Use the TEXT Function

You can do things the other way around too.

• Type a number in a cell
• Then, format that cell as a date.

Or, use the Excel TEXT function to format the number as a date, with a specific format.

In the screen shot below, I showed the weekday, month, day, and year.

• =TEXT(B2,”ddd mmm dd, yyyy”)

There are more TEXT function examples on my Contextures site.

Excel Milestones

We love Excel milestones, here on the Contextures Blog.

Our first Excel date celebration was Excel Day 40000, way back on July 6, 2009.

We also celebrated Day 44000, and another special day was Excel Day 44444.

Upcoming Excel Day Milestones

As you can see from the below list of Excel milestones, we have almost 2 years to get ready for the next special day:

• Excel Day 45678 falls on Tuesday, January 21, 2025.

And later that year, there’s another special date:

• Excel Day 46000 falls on Tuesday, December 9, 2025.

Be sure to mark those days in your calendar, and come back here to celebrate again!

_______________

Happy Excel Day 45000

________________

Since the beginning of (Excel) time, people have asked questions about the big grey “Page 1” that sometimes appears on a worksheet. How did it get there? Is it a watermark? How do I get rid of it? Will it show up on printed copies? My short video, below,  answers those questions for you!

Video: Grey “Page 1” on Excel Worksheet

The screen shot below shows an example of the “Page 1” text, in the middle of an Excel sheet with an order data entry table.

Watch this short video to see what that mysterious big grey “Page 1” watermark is, and how you can turn it off and on.

Bonus: In the video, I also share a few tips on using Excel’s Page Break Preview feature, if you plan to print your worksheet.

Turn Off Page 1 Watermark

If you’ve watched the video, you know that “Page 1” text is not really watermark. It’s a sign that you’re using Page Break Preview setting to view your worksheet.

Here are the steps to change that setting:

• On the Excel Ribbon, go to the View tab
• In the Workbook Views group, click on Normal

The “Page 1” will disappear from the worksheet view.

Icons in Excel Status Bar

Another way to switch between workbook views is to use the icons on the Excel Status Bar, at the bottom right of the Excel window.

If you don’t see the icons there, right-click on the Status Bar, and then click View Shortcuts, to add a check mark

When those icons have a check mark, they should be visible in the Excel Status Bar.

Get More Printing Tips

There are more Excel printing tips and videos on my Contextures site. That page also has written steps, and a full transcript for this video.

And for special types of printing in Excel, try these pages on my site:

Printing Excel Comments

Print Excel Bingo Cards

Pivot Filter Pre-Printing Diagnostic

___________________

Turn Off Grey Watermark Page 1 on Excel Worksheet

__________________

Do you ever use Excel to calculate a ratio? In business, you could compare income to expenses, and at home, you could use ratios to bake a cake, without a recipe. In my latest video, I show a quick Excel formula for the ratio between two numbers, in x:y format. And no, Excel doesn’t have a RATIO function, even though one overly-confident AI helper tool said it does!

Video: Calculate Ratio – 3 Steps

In this 2-minute video, see how to create a ratio formula in Excel. The result shows the ratio between two numbers, in x:y format.

The formula is created in 3 steps, using division first, then the formatting that result with the TEXT function, and finally, adding the SUBSTITUTE function.

Video Timeline:

• 00:00 Introduction
• 00:23 Step 1 – Divide
• 00:37 Step 2 – Fraction
• 01:17 Step 3 – Ratio

NOTE: There’s a longer Ratio video at the end of this page, with a GCD function example too.

Finding Help for Excel Ratio Formula

Yesterday, just for fun, I tried to find help on how to write an Excel ratio formula, posing as someone who had no idea how to do that.

First, I used Excel’s built-in help, and it didn’t look too helpful. When I searched for “ratio formula”, there were five links in the results, and none of them mentioned ratio. The closest was the fifth link, Calculate percentages.

So I clicked that link, and the tutorial didn’t mention ratio, but it did show how to calculate percentage by dividing.

Help Me Obi Wan Keno-bot

There are a few Excel formula helper bots now, so I tried one of those next. (I’ll leave the tool’s name out of this story.)

To use this formula helper, I had to sign up, using my email, but there is a free plan, and that gives you a few uses per month.

First, you enter a text description of the problem that you’re trying to solve, and then the AI tool suggests a formula.

1) This was my first prompt, asking for help:

• Get the ratio between numbers in cells B4 and C4 in x:y format

And here was the suggested formula:

• =CONCATENATE(B4,”:”,C4)

Well, that was technically correct, I guess. But…we usually want the smallest numbers possible, in the ratio results.

2)  Next, I entered another prompt, with more details on the result that I wanted:

• Get the ratio between numbers in cells B4 and C4 in x:y format, reduced to lowest terms

And here’s the suggested formula for that:

• =TEXT(B4/C4,”x:y”)

Things were getting worse!

3) And here’s my final prompt, in case the bot was confused by my “x:y” format request:

• calculate the ratio between numbers in cells B4 and C4

And here’s the formula – a simple division:

• =B4/C4

Another Bot, More Disappointment

Next, I tried one of the AI writing tools, which shall also remain nameless.

Here was my prompt:

• Please write 4 formula examples to calculate a ratio in Excel, showing the ratio in a:b format

In the screen shot below, you can see the 4 formulas that it suggested, and the results of those formulas.

• =A1:B1
• =CONCATENATE(A1, “:”, B1)
• =TEXT(A1/B1, “0:#”) & “:1”
• =SUBSTITUTE(TEXT(A1/B1,”0.0″), “.”, “:”) & “:1”

The last two suggestions were better than the formula helper’s, but they used the wrong format in the TEXT function.

For this solution, TEXT needs a fraction format, like “#/####”, instead of a colon (which creates a time), or a period.

• Tip: The fraction format does some of the heavy lifting – it reduces the result to the lowest terms

Imaginary Excel Function

After that experiment failed, I made one final attempt to get help from the writing AI helper, and used the following prompt:

• Please define what a ratio is and how it’s calculated in Excel

The response started sensibly, with a good definition of ratio, and a simple division formula.

But then, in a shocking twist, the bot invented a new Excel function to solve my problem. Here’s the final paragraph:

• Excel also has a built-in function for calculating ratios called “RATIO”.
• This function takes two arguments, the numerator and denominator of the ratio, and returns the result as a decimal value.
• For example, the formula “=RATIO(A1,B1)” will calculate the ratio of A1 to B1.

And my question is, if we did have that Excel function, wouldn’t it just be named DIVIDE?

Video: Two Formulas for Excel Ratio

Way back in 2014, I added a page on my Contextures site, with two different formulas for calculating ratios in Excel, and showing the results in x:y format.

My original video show both methods – using the GCD function, and using TEXT with SUBSTITUTE. It’s a thorough explanation, and almost 9 minutes long!

That’s why I made the new video – our attention spans are shorter now!

Video Timeline

• 0:16 Ratio introduction
• 1:20 GCD formula
• 4:03 TEXT and SUBSTITUTE formula

Get Ratio Formulas and Sample File

Anyway, it was fun to try the helper tools, and I’m sure they’ll get better over the coming months.

For now, and as always, you can find Excel help on my Contextures site, which is packed with tutorials, videos, and sample files.

To see my Excel ratio formulas, and to get the sample files, go to the Calculate Ratio in Excel page. In the download section, you’ll find the two Excel files that I used in the Ratio formula videos shown above.

________________

Calculate Ratio with Excel Formula – 3 Steps

________________

Every morning, I use Excel to keep track of the projects I’m working on, and organize my tasks for the day. Of course, things don’t always go as planned, but it helps to know where I’m headed!

Project Work List

In my project workbook, I record the time spent on yesterday’s tasks, and then plan today’s tasks.

One column has dates, so:

• I move some tasks a day forward, if they weren’t finished.
• I move weekly tasks 7 days ahead, so they’re in the plan for next week.

To save time, instead of typing the revised dates, I use those little macro buttons at the top of column E:

• Select a date cell
• Click the +7 macro button to add 7 days
  • or click +1 macro button to add 1 day

Then, after everything is moved forward, I click the Sort button, to put things in the updated order.

Choose Number of Days to Add

If you do anything similar, you can get my macros to add numbers to worksheet cell values.

There’s a macro that adds a specific number, like my +1 and +7 macro buttons do

Or, use my macro that prompts you to enter a number, and the value in each selected cell is increased by that amount.

This screen shot shows the sample file’s macro button, and the input box.

The four selected cells will increase by 100, and all the other cells will stay the same.

Video: Add Numbers to Multiple Cells in Excel

To see how my macros work, and how you can modify the code slightly, watch this 5-minute video.

• The video starts by showing how to change multiple cells manually, with the Paste Special command.
• After that, you’ll see how the macros work, and how to change a couple of lines in the macro code.

Video Timeline

• 00:00 Introduction
• 00:33 Manually Add Number
• 01:23 Macro 1: Add Specific Number
• 02:07 Macro 2: Prompt for Number
• 02:44 See the Macro 2 Code
• 03:22 Modify the Macro Code
• 04:14 See the Macro 1 Code
• 04:34 Macro Buttons
• 04:52 Get the Sample File

Download the Sample File

To see how the macros work, and get the code to use in your own Microsoft Excel worksheet, you can visit my Contextures website.

You’ll find the instructions and sample file on the Add Number to Multiple Cells page.

The zipped file is in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the code.

______________________________

Excel Macro to Increase Amount in Multiple Cells

______________________________

Keep track of Excel worksheet discussions, with a detailed list of threaded comments and replies. Just click a button, to run a macro, that makes the list for you. And I’ve fixed the macro!

Threaded Comments in Excel 365

If you use Excel 365, there’s a fancy version of comments – threaded comments. This is the default comment style now, instead of those little yellow pop-up comments from older versions of Excel.

When you add a new comment now, in Excel 365, people can reply to your comment, and a “thread” is created.

In the screen shot below, you can see a threaded comment, with my original comment, and a reply below it.

And yes, I talk to myself – frequently!

Threaded Comments and Notes

You can still create the old-style comments in Excel 365, but they’re called “Notes” now.

In the screen shot below, you can see that:

• old-style comments (Note) have a red triangle at the top right of the cell
• threaded comment cells have larger purple shapes at the top right of the cell

What is that purple shape? I think it’s a rectangle, with a triangle below it, at the bottom right.

Does that shape represent a speech bubble?

Old-Style Comment Details Macro

On my Contextures site, long ago, I created macros that list the old-style comments on a worksheet.

You can even put tiny numbers over the red triangles, then print a list of the numbered comments!

Threaded Comment Details Macro

Threaded comments were introduced in 2018, and the old macros didn’t work with these new comments.

So, I eventually created a new set of macros, to list all the threaded comments on a worksheet.

• Note: There’s built-in command to print the threaded notes, but I prefer a different format for my comment lists.

Macro Buttons in Sample File

If you download the threaded comment macro sample file, the main worksheet has macro buttons.

These are rounded rectangles, in random colours, with a macro assigned to each button.

These buttons make it easy to test all the different macros, and see which ones work best for you.

Macros to List Comments

If you click the purple button, a macro runs, and lists each main comment, with its details,  as shown below.

There’s also a macro that lists all comments and their replies, as shown in the section below.

Fixed the Excel Macro

The List Comments & Replies macro has been on my site for about three years, and someone finally let me know that it wasn’t working correctly.

It had the following problem:

• If a main comment had more than one reply, all of its replies were listed
• If a main comment had only one reply, that reply was not listed.

Can you guess the error that I had in my code?

Here’s the list, after I fixed the code, and you can see how I fixed the macro, in the next section.

How I Fixed the Macro

After getting an email from John Stolz (Thanks!), about the missing replies, I checked the code.

Fortunately, the problem was easy to spot!

At the start of one section, I had this line:

    If myCmt.Replies.Count > 1 Then

Oops! No wonder the one-reply comments had a problem.

So, I changed the 1 to a zero, so the threads with 1 reply would have replies printed too:

    If myCmt.Replies.Count > 0 Then

Then I tested the code, and it works correctly now.

Get the Sample File

Excel 365: If you’re using Excel 365, and you’d like test the macros, go to my Threaded Comments page, and head to the download section.

Other Excel:  If you’re using a version of Excel that has old-style comments, use the macros from my Excel Comments VBA page.

_____________________

Excel Threaded Comments and Replies List Details-Macro

_____________________

Happy Groundhog Day! Have you made plans that you’ll be happy to repeat, day after day? Maybe these Excel REPT function examples will give you some ideas! There are written steps, screen shots, and 3 videos below.

REPT Function Minimum Row Height

First, if you’ve set up a nicely formatted named Excel Table, you can use the REPT function in one column, to set a minimum row height.

Here’s the formula in the Picture column, in the screen shot below, to make sure those cute pictures don’t get squished.

• =REPT(CHAR(10),5)

In the formula, REPT repeats a line break character – CHAR(10) – 5 times, and that keeps all the rows the same minimum height.

Thanks to AlexJ, who suggested this technique for working with tables.

You can see more of AlexJ’s work on my Contextures site.

Make In-Cell Charts

Next, here’s another fun thing to do with the Excel REPT function – make little in-cell charts.

For example, here are the steps to make a set of simple dot plot charts, based on the numbers in column B.

1. In cells B3:B5, type 100, 55 and 80
2. In cell C3, type this formula:
   • =REPT(” “,B3/5-1) & “o”
3. Copy the formula down to row 5
4. Adjust the width of column C, so the dot (“o”) is at the right edge in cell C3
5. After that, you can change any of the numbers in column B, and the related chart will also change

In this formula:

• The REPT function repeats a space character (” “).
• The number of repetitions is calculated from the number in column B, divided by 5, with 1 subtracted.
• I divide by 5 to keep the bar charts shorter

At the end of the formula, a lower-case letter “o” is added to the REPT result.

Find Last Text Entry in Column

And here’s one more example – it combines REPT with VLOOKUP, to find the last text entry in a column.

For example, with text items and blank cells in column D, use this formula to find the last text item:

• =VLOOKUP(REPT(“z”,255),D:D,1)

In this formula:

• The REPT function repeats the letter “z” 255 times
• VLOOKUP searches for that text string, in column D.

Since it’s unlikely to find that text string anywhere in column D, VLOOKUP returns the last text entry that it found – July in this example.

Get Sample Files and More REPT Examples

See more REPT function examples on my Contextures site, and download the sample files there too.

Video: In-Cell Charts

This short video show other examples of in-cell charts with the REPT function.

The chart shows student grades, out of 100, in either a bar chart or dot plot.

Video: In-Cell Dot Plot for Stock Prices

Watch this short video to see how to set up an in-cell dot plot stock chart with the REPT function.

Video: Set Minimum Row Height

Watch this video to see the steps for setting minimum row height with the REPT function.

You can see the full written steps in this blog post.

_________________________

Excel REPT Function Examples Groundhog Day

_________________________

Have you played with the ChatGPT app yet, to get AI help with your writing, or your Excel formulas? Yesterday, I asked it to write Excel TEXTJOIN formula examples, and things didn’t go too well!

Video: Excel TEXTJOIN Examples

One of the newer Excel functions is TEXTJOIN, and I’ve just uploaded a new TEXTJOIN page on my Contextures site, with a several example formulas.

For example, here’s a short video from that page, with my daughter, Sarah, showing how to use TEXTJOIN, with conditions.

Simple TEXTJOIN Formula

And here’s another example from my TEXTJOIN page. It combines all the weekday names from a range of cells, and skips any empty cells.

The weekday names are in a range of cells – A2:A8.

The following formula is in cell D3, and it returns all the days in the list, separated by a comma and space character.

• =TEXTJOIN(“, “,TRUE,A2:A8)

TEXTJOIN Formula Arguments

In the screen shot above, you can see the colour-coded arguments in the TEXTJOIN formula:

• Red: delimiter (separator) is a comma and space — the first argument is “, “
• Green: blank cells will be ignored — the second argument is TRUE
• Purple: items in cells A2:A8 will be combined — third argument is A2:A8

TEXTJOIN Example from ChatGPT

After I finished the new page, I asked ChatGPT to write a few TEXTJOIN examples, and encourage business people to use TEXTJOIN.

Here’s the introduction that is wrote, and I’m sure it makes you want to jump right in, if you haven’t already!

• Hey there, business spreadsheet users! Are you tired of using CONCATENATE or “&” to join multiple cell values into one?
• Look no further than the TEXTJOIN function!
• This function allows you to easily join multiple cell values with a specified delimiter, making your data look neat and organized.

Maybe it’s overly enthusiastic, but we were off to a good start, I thought.

ChatGPT TEXTJOIN Example Formula

After that, ChatGPT gave the 3 example formulas that I asked for. Oddly, all 3 had the same formula, except for a different delimiter in each formula:

• =TEXTJOIN(“, “, TRUE, A1:A5, B1:B5)

However, the three data examples were different, and were business related, as I had requested..

Data Example 1 – Products and Prices

Here’s the setup that ChatGPT wrote for the first example:

• Imagine you have a list of product names in one column and their corresponding prices in another.
• You can use the TEXTJOIN function to combine these two columns into one, separated by a comma and a space.

I put that formula in cell A7, and wrapped the text, so it’s easier to read.

Would you ever want this type of result from a TEXTJOIN formula?

Better TEXTJOIN Formulas

Instead of a  string of product names, followed by a string of prices, it might be better to have a formula in each row.

For example, in cell C1, use this formula to combine the product name and price. Then, copy that formula down to cell C5

• =TEXTJOIN(“, “,TRUE,A1:B1)

Or, use the following formula, in cell A7, to list each product, immediately followed by its price.

I put that formula in cell A7, and wrapped the text, so it’s easier to read.

• =TEXTJOIN(“, “, TRUE, A1:B5)

More TEXTJOIN Examples

The other two formula examples from ChatGPT had the same data setup, and formula. Only the data details were different.

Example 2 – Mailing List

• Let’s say you have a list of employee names in one column and their email addresses in another. You can use the TEXTJOIN function to create a professional-looking email list by joining the two columns with the “; ” delimiter.
• The formula would be =TEXTJOIN(“; “, TRUE, A1:A5, B1:B5)

Example 3 – Event Dates

• In another scenario, you have a list of dates in one column and corresponding events in another column. You can use the TEXTJOIN function to create a calendar view by joining the two columns with the ” – ” delimiter.
• The formula would be =TEXTJOIN(” – “, TRUE, A1:A5, B1:B5)

Final Word from ChatGPT

Just in case those ChatGPT formulas haven’t convinced you to give TEXTJOIN a try, here’s the final paragraph from its “sales pitch”.

• As you can see, the TEXTJOIN function is a versatile tool that can help you create clean and organized data.
• Give it a try and let me know how it works for you!

Did that sales pitch work? Are you ready to get started with TEXTJOIN?

Anyway, rumours say that ChatGPT could eventually be built into the Microsoft Office programs, to help us with our daily work. Are you looking forward to that?

Based on my few weeks of testing, it’s off to a good start, but will need improvements, and lots of careful checking, before we can hand over our important spreadsheet tasks!

Get More TEXTJOIN Examples

If you’d like to see more TEXTJOIN examples, written by me, and not by ChatGPT, go to the TEXTJOIN Function examples page on my Contextures site.

There are example formulas, videos, and Excel files to download.

_________________

Excel TEXTJOIN Function Bad Examples from ChatGPT

_________________

If you had to split a text string into separate columns in Excel, how would you do that? There are built-in tools that could help you, and Excel functions and formulas will do the job too. If your version of Excel has the latest functions and features, there are fancy new text functions for you to try.

Comma-Separated Text

In the screen shot below, there’s an Excel worksheet, with a list of items that were sold, in column A.

Each cell has a comma-separated item description, with 3 pieces of information:

• Product Code
• Product Name
• Product Size

Split Text with Excel Tools

You could quickly split that product description into multiple columns, using Flash Fill, or Text to Columns, if it’s just a one-time thing.

This animated gif shows the steps for splitting a different data set with Flash Fill.

• Tip: To save time, use the Flash Fill shortcut – Ctrl + E

Excel Formulas – All Versions

In some workbooks, you might prefer to split the data by using formula, so the separated data updates, if the original text string changes.

No matter what version of Excel you’re using, you can create formulas that split the text string into separate columns, based on those comma separators.

Most formula solutions use a combination of Excel text functions, like FIND, LEFT, MID, and RIGHT.

There are formula examples on the Split Address Formulas page on my Contextures site.

• One example has comma-separated text strings
• Another example has a variety of separators, and that makes the task more challenging!

Excel 365 Formulas

If you’re lucky enough to have an Excel version that includes the latest features and functions, you can use simpler formulas to separate the parts in a text string.

The 3 latest text functions are TEXTBEFORE, TEXTAFTER, and TEXTSPLIT.

To separate the item descriptions in the example below, I used the TEXTSPLIT function, with this formula in cell B4:

• =TEXTSPLIT(A4,  “, “)

Then, copy that formula down, to the last row of data.

Because there are 3 parts in the text string, Excel shows the formula results in 3 separate columns.

When cell B4 is selected, the thin blue border shows that the formula results have spilled into columns C (Item) and D (Size)

Problems in Excel Table

Usually, I keep lists in a named Excel table, because that makes it easier to work with the data. There are built-in filters, and other handy features.

However, Excel tables don’t play nicely with formulas that spill into adjacent cells.

As you can see in the screen shot below, instead of showing the results, the formula cells show a #SPILL! error.

Avoid Excel Table Problem

If you need to keep your data in a named Excel table, but you also want to use the new TEXTSPLIT function, here’s one way to avoid the #SPILL! error problem.

Instead of using TEXTSPLIT on its own, combine it with another new Excel function – CHOOSECOLS.

• Tip: To see which Excel versions include this function, go to the CHOOSECOLS page on the Microsoft site, and click the drop-down list for Availability.

With the CHOOSECOLS function, you can tell Excel to return one or more specific columns from an array.

• =CHOOSECOLS(array, col_num1, [col_num2],…)

The TEXTSPLIT function will create the array, in this example, and we’ll put the column number in a worksheet cell

Which Columns to Return

In the next example, shown in the screen shot below, there are comma-separated addresses in column A. Each address has four parts:

• Street, City, State, Zip Code

The list is in a named Excel table, so I can’t use a TEXTSPLIT formula on its own – that would result in a #SPILL! error.

Instead, I’ll use CHOOSECOLS to get a single column from the full address.

First, I typed column numbers in cells B1 and C1:

• Cell B1: Number 3 – State is the 3rd part of the full address
• Cell C1: Number 2 – City is the 2nd part of the full address

Enter the Formulas

Next, I entered this formula in cell B4, to get the State code:

• =CHOOSECOLS(TEXTSPLIT([@FullAddress],”, “),B$1)

The formula fills down automatically, because the list is in a named Excel table

After that, I entered this formula in cell C4, to get the City Name:

• =CHOOSECOLS(TEXTSPLIT([@FullAddress],”, “),C$1)

Again, the formula fills down automatically, because the list is in a named Excel table

Choose Different Columns

Because the column numbers are entered on the worksheet, its easy to change the formula results, to show a different part of the full address.

Just type any number between 1 and 4 in cells B1 and C1, to see the results for that column number.

But, if you type an invalid number, the formula will return a #VALUE! error.

Tip: To avoid that problem, you could create data validation drop down lists in cells B1 and C1, instead of allowing invalid entries.

_________________________

Split Text Into Columns in Excel-Get Specific Column

_________________________

It’s almost the holidays, so relax and enjoy some time away from work. Instead of struggling with budgets and forecasts, you can play games in Excel! And do you remember the games that were hidden in old versions of Excel?

Making Games in Excel

Have you ever built your own game in Excel?

I’ve just finished a new Excel Games page on my Contextures site, with a few games to download, including a Concentration card game that Doug Glancy made, long ago.

It’s like those memory games that kids play, and the goal is to turn over matching pairs of cards.

In this screen shot, two of the cards are ready for you to check, and the Birds option is set for the pattern on the back of the cards.

Adult Excel Games

Excel games aren’t just for kids though. For example, Ken Puls created a Blackjack game in Excel, that you can download from his site.

It even adds up your card values, so you don’t have to do arithmetic in your head!

Arcade Style Games

Next, see the games that Andy Pope has on his website. There’s a Suduko game, Mastermind, and several others.

For example, here’s a screen shot of Andy’s Excel-based Jawbreak game.

The game is built on a UserForm, with a button on the worksheet to start the game.

Excel Easter Egg Games

While working on my Excel Games page, I had a fun trip down memory lane, looking back at the old hidden games, in early versions of Excel.

The one that most people remember was the Flight Simulator in Excel 97. Did you ever play that, while you were supposed to be working on other things?

Here’s a screen shot from the game, with scenery from another planet, I think, and a scrolling list of developer names on a giant rock. Do you recognize any of those Charting Testers’ names?

Excel 2000 Dev Hunter

The last Easter Egg was Dev Hunter,  in Excel 2000. In that game, the developer names were printed on a roadway, and look – one of the pivot table testers was named Debra!

Here’s a short video that shows the action-packed Excel Dev Hunter game, with the multiple challenges of shooting, steering, oil spills, and turning headlights on and off.

More Excel Games and Details

Anyway, go check out the new Excel Games page on my Contextures site, and let me know if there are any games that I should add to that page.

You’ll also see a couple more Easter Egg videos for other versions of Excel, and a few fun facts that I discovered.

And for more holiday fun, here’s a shameless plug for my comedian son’s collection of bad jokes for Christmas – suitable for all ages.

If you share these jokes at your holiday parties, you’ll hear the little kids laugh, and you’ll have the satisfaction of making the teens roll their eyes, and adults shake their heads. You’re welcome!

_______________________

Relax and Enjoy Games in Excel

______________

Happy December! We’ve almost made it to the end of this year, and you’re probably getting reading for next year’s tasks and projects. If you need to set up monthly workbooks for next year, or automatically add sheets to existing workbooks, these Excel macros can help you!

Create Monthly Workbooks

At one company I’ve worked with, some of their data was organized in a separate folder for each year, with an Excel workbook for each month’s data.

Was that an ideal setup? Probably not. But that’s how things had been done for years, and it wasn’t worth changing!

Anyway, there’s good news, if you’re working with a system like that. There’s an Excel macro that can make your job easier, when it’s time to create a year’s workbooks.

And there’s another macro that will automatically add a monthly sheet to an Excel workbook.

Macro Creates Workbooks

Instead of creating all the monthly Excel files, you can click a button, and a macro quickly creates all 12 workbooks.

Roger Govier used this macro to help his consulting clients at the end of the year, and it’s a real time saver! So, he generously shared it with us, in case you need to do this too.

• Download Roger’s sample file, then unzip it, and enable the macros.
• Read the macro notes, then click the button to create the workbooks

Macro Messages

When the macro starts, a message appears, asking you to enter a 4-digit year number.

Another message asks if you want the month’s day to be in ordinal format, like 1st, 2nd, 3rd.

Click Yes or No.

After the workbooks have been created, a final message appears, telling you where the monthly workbooks are located.

If you open one of the monthly files, you’ll see that it has a sheet for each day of that month, with the numbers in the format that you selected.

Macro Adds Month Sheet

There’s another macro on my Contextures site, and it automatically inserts a new sheet, the first time you open the workbook each month.

Here’s the November sheet tab, with the 2-digit month number and short month name.

Video: Automatically Add Monthly Sheets

In this video, see how to automatically add a new worksheet with the month name, when an Excel file opens at the start of the month.

Get the Macro Workbooks

For the Excel files, and to see the written steps, you can go to the Create Workbooks and Worksheets page on my Contextures site.

All three workbooks are listed there, with more details on the macros. The zipped files contain macros, so be sure to unblock them in Windows Explorer, before you open them.

After you open the files, if you want to test the macros, enable macros, when the security message appears.

__________________

Excel Macros Create Monthly Workbooks Entire Year

_______________

It’s only a week until Thanksgiving, if you live in the USA, and maybe you’ve got everything organized, and ready to go. If not, I’ve got a couple of Excel holiday planner tools that can help you. There’s short video below, that shows a quick demo of the Excel holiday tools.

Holiday Meal Planner

The first tool is my Excel Holiday Dinner Planner. At the top of the sheet, select the time that you want to eat dinner.

I used this to organize our Canadian Thanksgiving dinner in October, and I’ll use it again at Christmas, even though it will be just the two of us.

• Pro Tip: For Thanksgiving this year, we cooked and carved the turkey the day before. That made Thanksgiving day much more relaxing, and we’ll definitely do that again at Christmas.

What’s For Dinner?

After you select your dinner time, the next step is to list:

• all the dishes you’ll be serving for your holiday dinner
• preparation steps for every item on the menu
• time estimate for each step

Sort the completed list, and Excel calculates when each preparation step should start.

Finally, go to the Print Plan Timetable sheet, and print out a copy of the schedule, to take to the kitchen.

Here’s our schedule from last Christmas, and we had to get the turkey into the oven at 1:05 PM.

Excel Holiday Planner

The next tool is my Excel Holiday Planner, which helps you organize all the events and shopping over the holiday season.

Thanksgiving is just the beginning! After that, there’s only a month until Christmas, and you might have lots of shopping and planning to prepare for that.

And it’s not just for Christmas planning. You can enter the date for any upcoming celebration, to make sure you’re staying organized.

Here’s a screen shot of the Calendar sheet, where you can enter up to 5 events per day. If you have more than that, you’re too busy!

Stay on Budget with Excel

Of course, Excel is great with numbers and calculations. You can keep track of your holiday budget in the Excel holiday planner too, and make sure that you don’t overspend.

Here’s a screen shot of the Summary page, where you can see how things are going.

• How much of the budget is left?
• How many presents do you still need to buy?
• How many days are left until the holiday arrives?

Excel Gift Idea

And if you’re looking for the perfect gift for someone who loves Excel, how about a highly-rated online Excel course?

You can get 20% off Mynda Treacy’s Excel courses, from today (November 17th) until Thursday, December 2nd.

Choose from her extensive list of courses (affiliate link), and don’t forget to find something that you’d like too! There’s always something new to learn in Excel, no matter how long you’ve been using it.

These two courses are popular, and would be a great investment in upgrading your Excel skills.

— 1) Excel Dashboards

— 2) Power BI

Free Excel Dashboard Tutorial

Mynda is also posting a new (free) Dashboard tutorial today, and the recording will be available after 7 AM (Toronto time).

Click this link to go to the free tutorial, and the screen shot below shows the finished dashboard that you’ll see Mynda build in the tutorial.

More Excel Holiday Fun

For more Excel files that have holiday planning help, and a bit of fun, go to the Excel for the Holidays page on my Contextures site.

This short video shows a quick demo of the holiday planner, Advent calendars, Christmas trees, and more.

______________________

Excel for the Holidays 2022

_______________

Round 2 of the Excel World Championship 2022 is happening today, so don’t miss it! There are details below, and the Final round is on November 12th. And for those of us who have personal competitions with Excel every day, at the office/school/home, Excel expert David Ringstrom has some great tips for you.

Hidden Treasures in Excel

How often do you run into strange things when you’re working in Excel? Are they quirks, or bugs, or features? Sometimes it’s hard to decide!

David Ringstrom has been a spreadsheet consultant since 1991, helping clients and teaching courses. Tapping into his decades of experience, David has just released a new book – Exploring Microsoft Excel’s Hidden Treasures.

The subtitle gives us more details on what’s in his book:

• Turbocharge your Excel proficiency with expert
  tips, automation techniques, and overlooked features

Useful Tips and Interesting Quirks

David’s publisher sent me a digital copy of the book to review, and I was impressed by its contents, and enjoyed diving into several of the chapters. I learned a few quirks about my favourite Excel topics, and discovered things I didn’t know about Excel’s newest features.

And be sure to follow David’s advice in Chapter 1, and use the Feedback command on the Excel Ribbon’s Help tab. You can let Microsoft know what you like, or don’t like, and make suggestions for new features (or vote on other people’s suggestions).

I fully support the suggestion to add  the “Center Across Selection” command to the Excel Ribbon!

Who Is the Book For?

David’s book is for intermediate and advanced Excel users who want to save time, build better workbooks, and avoid Excel’s pitfalls and problems. You might think, “I don’t need an Excel book — the internet answers all my questions.”

But this book can help you find useful Excel features that are so well-hidden you didn’t even have questions about them! For example, thanks to a tip in the book, I remembered Full Screen View, that was removed from the Ribbon in Excel 2013, and put it back on the Ribbon.

And unlike the internet, Excel’s Hidden Treasures (Amazon link) is well organized, with a detailed table of contents, and a comprehensive index.

Also, every chapter has a companion Excel file to download, so you can easily try each technique. The files are well constructed, and you’ll get ideas from them too!

Video: Excel’s Hidden Treasures

If you’d like to see one of David’s presentations, before you decide to buy his book, here you go! This video shows his “Excel Quirks and Nuances” presentation to the London Excel Meetup, on October 26, 2022.

Video Timeline

• 00:00:00 Welcome
• 00:07:42 David Ringstrom – Self Introduction
• 00:10:00 Main Presentation
• 01:12:00 Questions

Excel World Championship on ESPN

The Excel World Championship 2022 started last Saturday (October 29th), and you can see Round 1 on YouTube, if you missed it.

TODAY, you can watch Round 2, live on ESPN3 (and live stream on the ESPN app):

• Date: Thursday, November 3, 2022
• Time: 1 AM Pacific Time 
• Event: Round 2
• Hosts: Excel MVPs Danielle Stein Fairhurst and Tim Heng
• Watch it live, or catch it later at these links:
• Watch on ESPN
•  Watch on YouTube

Round 2 Matches to Watch

In today’s show, you can watch the following Round 2 matches :

• Michael Clarke vs. Steyn Wolmarans Alfons
• Oliver Altes vs. Willem Gerritsen
• Anup Agarwal vs. Matthew Fried
• Liam Bastick vs. Ryan Bains

Final Round

The Excel World Championship finals take place on Saturday, November 12, 2022, starting at 9 AM Pacific Time

The finals are hosted by Excel MVPs Jon Acampora and Oz du Soleil, featuring special guest host, Microsoft’s Excel PM, Johnnie Thomas.

• NOTE: No links are available yet, but you can check the Financial Modeling World Cup YouTube channel. They’ll post the Final Round links there soon.

__________________________

Microsoft Excel Quirks and Competitions

__________________________

It’s finally here – the best day of the fall holiday season – Spreadsheet Day! You don’t need to buy any gifts, or cook a big meal, or find a trendy costume. Just open up your favourite spreadsheet, sit back, and enjoy the day!

Spreadsheet History

We celebrate Spreadsheet Day on October 17th, as a tribute to the original personal computer spreadsheet – VisiCalc. You’ll find the details on how this celebration got started, on my Spreadsheet Day blog’s About page.

And if you love spreadsheet history, you can read more about VisiCalc on Dan Bricklin’s site, where he and co-creator Bob Frankston share their memories, materials, and photos.

There’s even a VisiCalc executable that you can download, with the IBM PC version, from 1981. That would be a great activity for Spreadsheet Day!

User: Gortu, Public domain, via Wikimedia Commons

Early Excel for Windows

And speaking of spreadsheet history, last week I saw this tweet, with Excel 2.0 for Windows, featured on the cover of the December 22, 1987 PC Magazine.

Was the Data Form really bright yellow back then?

#Retrocomputing #Spreadsheets
The famous “PC Magazine” cover (1987) about Microsoft Excel (2.0 for Windows)
[From https://t.co/mI3OM6Xws6] pic.twitter.com/VB9y05hiar

— Marty Mallavibarrena (@Mallavibarrena) October 8, 2022

Early Excel for Macintosh

I first used Excel on a Mac, and still have a copy of Excel 3.0, that runs on our old Mac PowerBook. In the photo below, you can see the old PowerBook, with its black and white display showing some of the files on the machine.

The oldest Excel file that’s on there is from April 20, 1987, so I must have been using Excel 1.0 when working on those.

Because everything was in black and white, I couldn’t make rainbow-coloured charts, like the one on the old PC magazine cover.

We could still make ugly charts though, even without garish colours.

For example, here’s a 3-D line chart that I make in Excel 3.0. Beautiful!

How Will You Celebrate Spreadsheet Day

What will you do to celebrate this year’s Spreadsheet Day?

• Build more spreadsheets?
• Dig out an old computer with ancient spreadsheet software on it?
• Wear clothes that have a grid pattern?
• Drink from a spreadsheet-themed mug, like the ones we saw recently?
• Something else?

Inspiration From Last Year

If you’re looking for more Spreadsheet Day celebration ideas, here are some contributions from last year.

First, here’s something you don’t see at the office every day. Last year, in the Microsoft Tech Community Excel Blog, Robert Gascon shared his Excel Chess Games Viewer.

Meanwhile, at Tiller Money (Twitter), they had a Spreadsheet Day cake!

It looks delicious, and that sugar/chocolate combination might help you tackle a few finicky formulas.

And later,  Brian Bilston (Twitter) shared his poem, Love Excels,  in a spreadsheet!

Also, his latest book was just released – Days Like These: 366 Poems (Amazon link). Do you think his October 17th entry is about spreadsheets?

Tag Your Tweets

Have fun with spreadsheets today, and if you post something to celebrate Spreadsheet Day on Twitter today, you can use this hashtag:

• #spreadsheetday

That way, I can find your contribution, when I’m doing the Spreadsheet Day roundup later.

Thanks! And Happy Spreadsheet Day 2022!

____________________________

Happy Spreadsheet Day 2022

_____________

There’s an Excel UserForm that you can download from my Contextures site, and use it to search, view, and edit records in an Excel table. The two short videos below show how the UserForm works, and how you can put flexible criteria in the search boxes.

And remember – it’s almost Spreadsheet Day – we’ll celebrate on Monday, October 17th!

UserForm Search Add Edit

The UserForm is in an Excel  workbook that has a named Excel table, with sample income and expense Transaction records, for a fictional small business.

After you open the workbook:

• Click a worksheet button to open the UserForm.
• Next, put criteria in one or more of the Search boxes – Transaction ID, Account, or Company
• When you’re ready, click the Search button, to see the matching records.

In the screen shot below, a name was selected in the Company search box, and three transactions from the selected company are shown in the Search Results list.

Work with Transaction Data

After you click Search, and  the results list appears, you can work with any of the listed records.

• First, click on any record in the results list
• That record’s data appears in the data entry cells of the UserForm, below the results list
• You can view the data, OR take one of the following actions:
  • update one or more of the data entry fields, then click the Update button.
  • delete the selected record, if it’s not needed any longer
  • change one or more fields, assign a new ID, and add to the existing data as a new record

Completed UserForm Excel File

The UserForm workbook is a completed Excel example, so you can use it as is, or make changes it.

In the workbook, the VBA project is unlocked, so you can:

• make changes to the UserForm design
• edit the VBA code
• or, just take a look at the code and design, to see how it works.

The page does NOT have instructions for building the UserForm, or changing the code.

• NOTE: If you’d like instructions for building a simple Excel UserForm from scratch, go to this page — Create an Excel UserForm with Combo Boxes. That page has written steps, and videos, to help you get started.

Video: Transaction Search UserForm Demo

To see how the Transaction Search Form works, watch this short video.

Note: There’s more detail on using the Search Boxes, in the next video.

UserForm Search Boxes

To see how the search boxes work, and the different ways to enter criteria, watch this short video.

Get the UserForm Workbook

To get the Excel sample file, with the completed Transaction Search UserForm, go to the UserForm Search Add Edit page on my Contextures site.

The Excel file is in xlsm format, and contains macros and an Excel UserForm. Be sure to enable macros when you open the workbook, if you want to test the UserForm.

___________________

Search Transaction Records with Excel UserForm

___________________

In the past, I’ve written about a duplicate grand total problem in Excel’s automatic Subtotals feature. Yesterday I noticed another problem, and it was nasty surprise!

Excel Subtotal Screen Shots

This week, I was updating the Excel Subtotals Feature page on my Contextures site, and getting a few new screen shots.

First, I added a couple of Subtotals to a worksheet list, then I made a couple of small changes to the worksheet.

Here’s the Undo list, showing those changes.

Next, I took a screen shot of the Subtotal dialog box, showing the Remove All button.

Remove All Button Impact

To get ready for the next screen shot, I clicked the Remove All button.

Sometimes, Excel asks you to confirm an action, like the message you get when you try to delete a worksheet.

However, for the Remove All button, there wasn’t a confirmation message.

• The Subtotal dialog box closed automatically
• All the subtotals were removed from the worksheet list

Try to Undo the Remove All

Next, I decided to put the subtotals back, so I could get a different screen shot.

And that’s when I got the nasty surprise – the entire Undo stack had been wiped out!

The Undo command on my Quick Access Toolbar was dimmed out – there was nothing left in the list.

So, my only choice was to rebuild the Subtotals from scratch.

Be Prepared Before Remove All

So, here’s my advice to you, if you’re using the Subtotal feature.

Before you click the Remove All button, to remove all the subtotals, do this:

• Save your workbook
• Make a backup copy of your workbook

After that, you can remove the subtotals.

Just remember that there’s no way to undo that action, and get your Subtotals back.

Video: Excel Subtotals Feature

In this short video, you’ll see how to create automatic subtotals with the Microsoft Excel Subtotal command.

Also, there’s a demo of duplicate grand totals problem, and the steps to prevent that.

The written instructions and sample file are on the Excel Subtotals Feature page on my Contextures site.

______________

Excel Subtotal Feature Problems

______________

Tuesday morning, I opened an Excel file that I’d been working on the day before. Immediately, an error message appeared, “Reference isn’t valid”. I’ve seen that error with Excel charts before, but this turned out to be a pivot table problem.

Fooled by a Chart

When the message appeared, there was a greyed-out chart behind it.

That chart must be the problem! So, I clicked OK, then deleted the chart, and figured I’d rebuild it later.

Unfortunately, a few seconds later, the error message appeared again.

That chart was not the problem!

Check the Pivot Tables

What else could have a reference problem? Well, there were two pivot tables in the workbook, so I checked them next.

• If I tried to refresh either pivot table, the error message appeared.
• Both pivot tables were based on the same data source
• I checked the data source for one of the pivot tables
• As expected, it showed a named Excel table, Table1, as the source.

It was strange though – the pivot table was still showing in the background.

Usually, Excel finds the source data range, and highlights it with “marching ants”.

Check Source Data Table

Next, I checked the data sheet, and there was no nicely formatted Excel table. Mysteriously, the table had converted back to a simple list, with all of the table structure removed.

How did that happen?

• I work alone, so there were no co-workers to blame.
• Then I remembered – Excel had crashed the day before, while I worked on that file
• Excel repaired the file, and re-opened it, with a “Repaired” message that I didn’t read (oops!)

Apparently the Excel table had some corruption, so Excel removed it, and I didn’t notice.

• Don’t be like me – Read those messages!

Reformat the Excel Table

Fortunately, the broken reference problem was easy to fix.

• First, I formatted the list as an Excel table again.
  • Excel used the same name – Table1 – but you can rename it, if needed
• After that, I checked the pivot table’s data source again, and everything was working correctly.

In the screen shot below, you can see the dashed line (marching ants), around the table’s border

Happy Ending

Good news! I’m happy to report that the error message, “Reference isn’t valid”, hasn’t appeared since I rebuilt that table.

Maybe I should check the rest of the workbook though, to see if anything else was affected by that “repair”.

And remember – read those “Repaired” messages!

Find Problems with Pivot Tables

All kinds of things can go wrong, if you’re working with Excel pivot tables.

Luckily, my pivot table problem was easy to find and fix, but in a big workbook, with lots of pivot tables, and different data sources, it can be tricky to pinpoint the problem.

• For details on all pivot tables in a workbook, you can use my “List All Pivot Table – Details” macro
• That macro lists each pivot table in the file, with details on its location, size, and source data.

• If source data is a table in the same Excel workbook, macro shows source data details

Video: Fix Pivot Table Refresh Errors

This video shows a couple of other pivot table problems that you might run into, and ways to fix them.

You can get the macro and workbook from the Pivot Table List Macros page, to follow along, and there are written steps on that page too.

_________________________

Excel Pivot Table Error Message Reference Not Valid

___________________________

After you filter a list in Excel, it’s easy to select an copy a few of the visible cells only. After doing that, you can go to another worksheet, and paste just those cells, without any problems. Unfortunately, Excel is not so helpful if you want to copy cells from that other sheet, and paste them into just the visible rows in the filtered list. This is a decades old problem, and there’s no magic solution.

Paste Problems

If you’re pasting into a filtered list, Excel doesn’t care about your visible rows, or your dreams of a happy ending.

Excel doesn’t tiptoe through your data, carefully placing the copied data into the visible cells only. Instead, Excel crashes through the filtered rows like a steamroller, crushing everything in its path.

Values are pasted into:

• a solid block of cells
• every row in that block – visible rows, and hidden rows.

So, instead of a “dream” solution, you can end up with a data nightmare!

Copy Across Same Row

There’s good news though, if you want to copy data from one column to another, within a filtered list. For example, you might need to copy some of the order dates, into the shipping date column.

For that situation, you can use a keyboard shortcut, and “fill” the data across the row, instead of copying and pasting.

Watch the short video below, to see the steps, and thanks to Khushnood Viccaji (LinkedIn profile), who shared this tip.

Note: There are detailed steps on my Contextures site, and a special step if you’re copying data from column A.

Video: Copy Across with Shortcut

This video shows the steps to fill right or fill left, to copy across the same row, in a filtered list.

Filtered Paste Workarounds

If you’re NOT copying across in the same row, and need to paste multiple values into a filtered list, this video shows a couple of workarounds you can try. Neither workaround is a quick fix!

Get the Sample Workbook

To get the Excel workbook that was used in the videos, go to the Filtered List Copy Paste page on my Contextures site.

The zipped Excel file is in xlsx format, and does not contain macros.

______________________

Excel Filtered List Paste Same Row Shortcut

______________________

The NFL preseason games for 2022 started last week. That reminded me of  football-related questions that I had, way back in January 2010. Do the biggest players have the highest jersey numbers? Does the heaviest team win? As usual, I turned to Excel for the answers!

Get the Answers

In my January 2010 blog post, you’ll find my answers to those “heavy” questions, and see the fancy pivot tables I built from the data.

Or, if you’d prefer traditional (boring) game statistics and analysis, you can get those on the 2010 playoff game summary on the ESPN website.

More Player Data for 2022

That player data is 12 years old, and I wondered if today’s players have similar statistics. To find out,  I added 2022 player data, for the same two teams – the Saints and the Cardinals.

The rosters will change, as the season progresses, so you can get the latest info from the team sites:

—  New Orleans team roster 

—  Arizona team roster

P.S. I checked, using a COUNTIF formula, and none of the 2010 players are still in the 2022 roster.

Football Player Data

If you’re interested, I’ve put the updated football player file on my Excel Sample Data page, so you can download it, and come up with answers to  your own burning questions!

In my sample file, the data is stored in a named Excel table, which has:

• 16 columns of data, including 6 columns with a calculation.
• 306 rows of player data, for two NFL teams, in two different years – 2010 and 2022

Player Data from Rosters

Each row shows these 10 fields, taken from the player rosters:

• Year: Roster Year
• Team: Team city name
• Name: Player’s full name
• No.: Player number, worn on jersey
• Pos: Player’s position code , e.g. QB (Quarterback)
• Ht: Player’s height in text, feet-inches, e.g. 5-10
• Wt: Player’s weight in pounds
• Age: Player’s age in years
• Exp: Player’s years of experience (or R for rookie)
• College: Name of college that player attended

Player Data Formulas

In the Excel table, I’ve added 6 more columns, with formulas to calculate the following:

• FirstName: Player’s first (given) name
• LastName: Player’s last (family) name
• Ft: Number of feet in player’s text height
• In: Number of inches in player’s text height
• Inches: Player’s total height in inches
• NumGrp: Number group for player’s jersey number (groups of 10)

Training Tip

• For an Excel training class, you could delete the formulas in those calculated columns.
• Then, help your students figure out their own formulas.

Get the Football Player Sample Data

You can get the file on my Excel Sample Data page, along with several other sample data files.

The sample file is in xlsx format, and there are no macros in the workbook.

_________________________

Excel Sample Data for Football Season

_________________________

The AVERAGEIF function in Excel is similar to SUMIF and COUNTIF – you can calculate a result based on criteria. There’s also an AVERAGEIFS function, if you need to use multiple criteria in your calculation.

Video: AVERAGEIF With Wildcards

This short video shows how to create a flexible AVERAGEIF formula, to calculate the average quantity sold, for the selected product name.

There are notes below the video, and the sample file is on the Excel Average Functions page, on my Contextures site.

Average Quantity – One Product

With the AVERAGEIF function, if you need to get an exact match for the criteria, just put the word in the formula, or refer to a cell that has the word in it.

For example, this formula calculates the average quantity per order, if the item is an exact match for the product name in cell E2 – pen.

(Note: upper and lower case are treated equally)

• =AVERAGEIF($B$2:$B$8,E2,C2:C8)

The result, in cell E5, is 8.33 items per order.

Add Wildcards to Criteria

To make the formula more flexible, add one or more wildcards.

For example, the asterisk (*) wildcard represents any number of characters (or no characters).

If you put it at the end of the criteria word, the AVERAGEIF function include all items that have begin with “pen”, followed by any other characters (or no more characters)

• Tip: You can use wildcards with the AVERAGEIFS function too.

After adding the wildcard, the formula result, in cell E5, is 8.80 items per order.

Note: The Gel Pen and Gel Pens orders are not included, because they don’t start with “pen”

Get the Sample File

There are more Average function examples on my Contextures site, and you can download the Average Functions sample file there too.

__________________

Use Wildcards with AVERAGEIF Function in Excel

__________________