In some workbooks, you might need to block duplicate entries in a column. For example, we don’t want 2 employees to have the same ID number. See how to set up a custom rule for that, with data validation. And keep reading, to see why COUNTIF can cause problems for you.

Block Duplicates in Column

When you’re working in Excel, you can use data validation to prevent duplicates from being entered in a column.

For example, in the video below, an Employee ID is entered in column A, and each ID must be unique.

If you try to enter a duplicate ID number, a data validation rule will block you, and a message appears.

• Tip: You could customize that message, to explain why the number is being blocked.

Video: Prevent Duplicate Entries in Column

Here’s a video that shows the steps for setting up the custom rule, so duplicate ID numbers can’t be entered in column A

There are written instructions below the video, and on the Data Validation Custom Rules page, on my Contextures site.

How to Block Duplicates

First, here’s how to block duplicate entries:

• Select the cells where you want the rule applied — A2:A3 in the screen shot below. (That range is named EmpIDs)
• On the Data tab, click Data Validation, and for Allow, select Custom
• In the Formula box, type this formula that checks for matching entries.:
  • =COUNTIF(EmpIDs,A2) <=

The COUNTIF function counts any matches in the EmpIDs range, for the number in cell A2.

Then, if there’s more than one ID with that number, the entry will be blocked by the custom data validation rule.

Important: Read the next section, to see the COUNTIF problem, and how unique IDs could be incorrectly blocked by this rule’s formula.

COUNTIF Problems

In most Excel tables, this custom rule works well, but in some situations, the COUNTIF function might not work correctly, and that can cause problems.

For example, if your numbers are text numbers, instead of real numbers, COUNTIF will cause problems.

In the table shown in the screen shot below, product codes in column B must be unique.

In column E, the COUNTIF function shows 2 in some rows, and those formula results are highlighted in yellow.

Text Numbers and Real Numbers

In those cells, the COUNTIF function is counting “0123” and “123” as (false) duplicates.

That happens because COUNTIF treats text numbers like real numbers, so:

• “0123” is equal to 123

and

• “123” is also equal to 123.

To avoid that problem, instead of using the COUNTIF function, use SUMPRODUCT, as shown below.

SUMPRODUCT Function Solution

The SUMPRODUCT function treats text numbers as text, so “0123” is different from “123”

In the screen shot below, some SUMPRODUCT formula results, in column F, are highlighted in green, and they don’t have false duplicates.

Here’s the data validation formula you can use, if you have any “text” numbers in column B, where you want to block duplicates:

• =SUMPRODUCT(–($B$4:$B$22=B4))

Get the Sample File

Get the Excel sample file, and see more examples of data validation custom rules on my Contextures site.

On that page, there’s also a section on how to prevent duplicates in multiple columns. such as product name, size and colour combinations.

For example, you could enter multiple products that are jackets, in slze large, but each of those large jackets must be in a different colour.

___________________

How to Prevent Duplicate Entries in Excel Column

___________________

Do you ever find yourself scrolling through an endless number of worksheet tabs, while you’re working in a large Excel file? It’s happened to me, more times than I can count, while working in my own files, or client workbooks. Here’s a quick tip that might help you save some time!

Video: Quickly Move Sheet

Scrolling through a large workbook can be painfully slow, and it seems to take twice as long if you’re dragging a sheet tab, to move it to a different spot in the workbook.

This short video shows a quick tip for moving sheets, so you don’t have to waste time dragging them a long distance!

• You could move a sheet temporarily, so it’s near a set of sheets that you’re updating.
• Or, move a sheet permanently, like I did with the Notes sheet in this example.

Thanks to UniMord, who told me about this time-saving tip!

Close Extra Windows

In the next video, you can see what happens if you close the original window first, after opening a new window for an Excel workbook.

Don’t let this happen to you – remember to close the new window first, and leave window number 1 open!

More Excel Worksheet Tips

You can find more Excel worksheet tips on my Contextures site.

And if you like time-saving macros, go to the Excel Worksheet Macros page. For example, there are macros to automatically add monthly sheets in a workbook, or create a set of monthly workbooks at the start of a year. Or, use a macro to change the worksheet settings on all sheets, like gridlines, headings, page break lines, and zeros.

Also, if you want more details and examples on the Excel Freeze Pane settings, go to the Freeze Excel Rows and Columns page on my Contextures site. For example, did you know that if you select the Freeze Top Row command, it doesn’t automatically freeze Row 1 on the worksheet?

_______________

Quickly Move Sheet in Large Excel File

_______________

In our everyday life, the words Merge and Combine have almost the same meaning. However, when you’re working in Microsoft Excel, there are important differences in their meanings!

Merge vs Combine

The two screenshots below give you a quick summary of the differences between merge and combine. In both screenshots, there is a first name in one cell, and a last name in another cell.

If you merge those 2 cells, hoping to get the names into a single cell, you’ll be disappointed in the result!

After merging the cells, only the first name remains on the worksheet, and the last name was discarded. Oops!

Combine With Formula

Instead of merging, you can safely and successfully combine the text from the two original cells, if you use a simple formula in another cell. The next screenshot shows an example of that.

Here’s the formula that’s in cell C2, to combine the names into a full name, with a space between first and last name:

=A2 & " " & B2

More Ways to Combine Text

There are fancier formulas for combining text, like the examples in the next video, which combines text strings with formatted numbers.

This screenshot shows a few sample formula results, and the steps and formulas are in the video below.

NOTE: You can see the written steps, and get the sample file, on the How to Combine Cells page on my Contextures site.

Avoid Merged Cells

Merged cells can cause problems in an Excel worksheet, so I try to avoid them. There are merged cell tips and warnings on the How to Merge Cells page on my Contextures site.

This video shows one of my favourite ways to avoid merging cells – use the Center Across Selection setting instead!

NOTE: There are written steps on the Excel Worksheet Tips page.

_________________

Should You Merge Cells or Combine Text in Excel

_________________

When you’re printing financial reports in Excel, do you set them up to print one page wide? That feature saves paper, but it can make the header and footer text too small to read, especially if it’s in a small font size already. Here’s how you can avoid that problem. And there’s a macro to create a custom worksheet footer too!

Change Page Setup Options

On an Excel worksheet, the header and footer text automatically scales to the same percentage as the rest of the page, if you scale it for printing one-sheet wide.

To keep the header and footer text at its original size, you can change a setting for the selected sheet(s):

• Go to the Page Layout tab on the Excel Ribbon.
• In the Page Setup group of commands, click the little arrow at the bottom right of that group, to open the Page Setup dialog box.
• Click the Header/Footer tab, and at the bottom, remove the check mark from “Scale With Document”.
• Then, click OK to exit. (Note: this setting will affect both the header and footer.)

Excel Macro for Custom Footer

Next, if you want to get fancier with your Excel footer (or header), you can use my macro. It picks up values from the worksheet, and puts them in the footer, like the order information in the screen shot below.

Video: Custom Footer Macro

This video shows how the worksheet is set up, with order information at the top, in named cells. When the macro runs, it grabs that information, and puts it in the footer – some on the left side, and the rest on the right side.

Video Timeline

• 0:00 Introduction
• 0:22 Name 3 Cells
• 1:10 Copy Macro Code into Workbook
• 2:05 What the Code Does
• 3:15 Test the Macro

Get the Excel File

To get the Excel file from the video, so you can test the custom footer macro, go to the Excel Worksheet Macros page on my Contextures site. The code is also shown on the website page, so you can copy it from there, into your own Excel file.

The file is in Excel xlsm format, and zipped. In Windows Explorer, unblock the file, before you unzip it. After you open the file,  enable macros, if you want to test the Custom Footer macro.

You can also find more tips for Excel worksheets on my website.

_________________________

Excel Footer Font Fix and Custom Footer Macro

_________________________

It’s almost the end of June, and there’s a long weekend coming up! To celebrate, let’s take a look at some of the spreadsheet news from the past month. Here are 3 stories that caught my eye, and let me know if there was other noteworthy news that I missed!

EVE Online

Do you play EVE Online? It’s a role-playing game, where things can get so complicated that people use Excel to keep track of their activities.

Now that will be much easier to do, because Microsoft and CCP Games just released a free add-in to integrate Excel with EVE Online.

It’s the first and only game to have Excel data integration!

Read the details on the game’s official website, and get the free Excel add-in.

Create Spreadsheets with Google Bard

Google announced improvements to its AI chatbot, Bard, and now you can export tables to Google Sheets.

For example, I could enter this prompt:

• Please make a table to compare the populations of all Canadian provinces and territories

When Bard finishes the table:

• Check the table, to see if it looks okay – if not, try rewording your prompt
• Next, click the Export to Sheets button.
• Then, at the bottom left of the Bard window, click the Open Sheets link, to see your new spreadsheet.

Unfortunately, Bard isn’t available in Canada yet, so I’ll have to make my spreadsheets the old-fashioned way!

Life in a Spreadsheet

Some days, it can feel like you’re living in a spreadsheet! But have you ever considered graphing your life in a spreadsheet?

Emmett Shear, a co-founder of Twitch, is turning 40 soon, and tweeted the spreadsheet version of his life so far. He also posted a link where you can download your own copy of the spreadsheet, so that’s what I did!

Here’s a small section of Emmett’s life graph.

I was going to deduct points for the merged cells in the Month heading row, but then I noticed the merged cells in column A. It has two features that aren’t available in Excel:

• When selected, the merged cell range appears in the name box
• The merged cell gets vertical and horizontal scroll bars if the entire cell isn’t visible

I try to avoid merged cells, but if people insist on using them, these features seem helpful!

_____________________

Spreadsheets in the News 2023-06-29

____________________

Do you ever need to compare two lists in Excel, and see if there are some items in one list, that aren’t in the other list? The video below shows how to check for new items, and only add those to your existing list. You don’t want to have a list that’s full of duplicates entries!

Video: Compare Two Excel Lists

In this video’s example, we’ve got a conference attendance list in Excel, with people’s names and email addresses. There’s also a column where Y or N is entered, to show if they’ll attend the event.

Another Excel list has arrived, with most of the same names, but there are a few new people too. We want to keep the main list, and get the new names added.

In this video, you’ll see how to compare two Excel lists, to check if they have any of the same entries. After you find all the new items from List 2, you can quickly add them to the main list.

Video Timeline

• 00:00 Introduction
• 00:15 Two Lists
• 00:45 Excel Table
• 01:30 Find New Records
• 02:42 Add New Records
• 03:30 Get the Sample File

Video: Compare Two Cells in Excel

In the video above, we were only comparing names — it didn’t matter if those names had the same upper and lower case text.

But, if you were checking a list of passwords, you would need check the case, as well as the characters.  https://www.contextures.com/excelexactfunctionexamples.html

The video below shows how to use the Excel EXACT function to compare text strings for an exact match, including upper and lower case.

• Note: Examples 4, 5 and 6 show how to find exact matches in a list:

Video Timeline

• 0:00 Introduction
• 1:32 Ex 1: Test Password
• 2:52 Ex 2: Allow Changes to Cell
• 3:37 Ex 3: Force Upper Case Entries
• 4:16 Ex 4: Find Exact Match in List
• 5:19 Ex 5: Pull Name Exact Match in List
• 5:55 Ex 6: Count Exact Matches in List
• 6:13 Ex 7: Check Each Character in Cell

Get the Sample Files

To see the written steps for these video examples, and to get the sample files, you can go to the following pages on my Contextures site.

Compare Two Lists: To get the compare lists sample file, and to see the written steps, go to the Compare Two Excel Lists page. The zipped file is in xlsx format, and does not contain any macros.

Compare Cells: To get the EXACT function sample file, and to see the written steps, go to the Compare Cells with EXACT function page. The zipped file is in xlsx format, and does not contain any macros.

__________________

How to Compare Two Lists in Excel-Find New Items

___________________

______________

What’s your favourite way to troubleshoot Excel formulas? Here are a few tips for current versions of Excel, and a preview of new features that are coming soon, for Excel 365.

Use the Formula Bar

If there’s a problem with a simple Excel formula, I usually click in the formula bar, so Excel colour codes the precedent cells on the worksheet.

Sometimes the wrong cell is in the formula, so I can drag the coloured borders, and choose the correct cell instead.

Video: Audit Excel Formulas

This short video has a few more formula auditing tips, from my Audit Excel Formulas page. There’s another video further down, with new features that are coming soon, for Excel 365.

Go to Precedent Cells

For a formula that’s more complicated, you can go to the precedent cells, with this keyboard shortcut:

•  Ctrl + [

That shortcut will even take you to a formula cell on a different worksheet, but only if it’s the first reference in the formula.

Formula Troubleshooting Tool

For bigger formula auditing projects, you can use an Excel macro, to see where formulas have been copied down, across, or both.

In the screen shot below, I used a macro, written by Dermot Balson, which applies fill patterns to the formula cells.

• Fill lines go down, across, or crosshatch, to show the direction in which the formulas were copied on the worksheet.
• Solid coloured cells contain the original formulas

This colour coding makes it easy to spot cells where a formula is missing or different. That can give you a quick start in your troubleshooting!

And don’t worry – the colour coding is done on a new sheet, so it doesn’t mess up your worksheet!

New Excel Formula Tools

If you’re using Excel 365, there are new features coming soon, including improvements to the formula bar.

• In current versions of Excel, you can select part of a formula in the formula bar, then press the F9 key, to evaluate that part of the formula.
• In the upcoming updates, you’ll be able to click on an argument name in the formula tooltip, and see the values from that range of cells.

In this video, Mike Tholfsen, from Microsoft, shows 8 new Excel features, with the formula bar feature shown at the 6:28 mark.

Get the Excel Files

–1) Audit Tips: To get the formula auditing tips workbook, go to the How to Audit Excel Formulas page on my Contextures site.The zipped file is in xlsx format, and does not contain any macros.

–2) Formula Cell Colour: To get Dermot’s colour coding macro, and read more about it, go to the Formula Cell Colour Code Macro page on my Contextures site. The zipped file is in xlsm format, and contains the macros to colour coded the formulas. Be sure to enable macros when you open the workbook, if you want to test the macro.

_________________

Excel Formula Troubleshooting Tips and Tools

________________

With the Find and Replace feature in Excel, you can look for specific characters, and replace them with different characters, or replace them with nothing. You can also use wildcard characters in the Find What box, to make the find feature more flexible. The short video below shows you the steps. 

Video: Use Wildcard in Find and Replace

In this video, you’ll see how to use the * wildcard with the Find and Replace commands, to clean up a list of names.

• Warning: Remember to make a backup copy of your original data, before you try this technique.

For the written steps, and the Excel sample file, go to the Excel Find and Replace page, on my Contextures site.

Video Timeline

• 00:00 List of Names to Clean Up
• 01:02 Make a Backup of Data
• 01:30 Remove Colons and Text
• 02:30 Remove Hyphens and Text
• 03:00 Remove OF and Text

Note: There is a full transcript below the video.

Get the Sample File

For the written steps, and to download the Excel sample file, go to the Excel Find and Replace page, on my Contextures site.

The sample file is in xlsx format, and does not contain any macros.
______________________

Video Transcript: Use Wildcard in Find and Replace

I’ve been given a list of names to clean up in Excel, and this is just a small sample of a long list.

These are all fake names, but we’ll see how you can remove text, either before or after a specific character or string of characters.

1. In this column, we want to get rid of anything that’s after a colon. So here’s the name of a law firm, a colon, and then the name of an individual there. So we want to remove the colon and anything after that. And there’s another example here.
2. There are also a few that have a hyphen. Again, we want to remove the hyphen and anything after it. We also want to remove the space before the hyphen.
3. And the third thing we want to do is find any that have the word “of”, remove that word, the space after it, and anything before that. So we have law office of, law offices of, and the law office of. We also have someone with OF in their name, and we want to make sure that doesn’t get affected.

Make a Backup of Data

Before I make any changes, I’m going to make a copy of this data. I don’t want to work on the original. I want to keep that safe in case I have to go back to it. So either:

• make a copy of the whole workbook
• OR, at least make a copy of the names you’re going to work on.

So I’ll select these two columns and copy them. Then we’ll go to Sheet2, click here and paste.
So now I have a copy that I can work on, without any concerns about the original data.

Remove Colons and Text

The first thing I’m going to do is try and find the colon and delete anything after that.

• I’ll select the column where I want the changes, and I’m going to use the Find and Replace window
• the quickest way to get to that is Ctrl + H
• and here’s the Find and Replace window.
• I’m going to be looking for a colon. So I’ll type that, then I want anything after that.
• And the way we represent anything is with a wild card, which is Shift + 8.
• So the asterisk represents any characters or no characters.
• So if it ended with a colon, it would also find that. So here, we’re going to find colon John Wilson.
• It should also find colon Alice Smith.
• We want to replace that with nothing. So I’m going to leave this blank.
• I will click Replace All, and it should replace the two items that I can see here.

It’s telling me it made two replacements, so that looks correct.

Remove Hyphens and Text

The next thing we’re going to change is a hyphen and anything after that.

So in this case, we want to include the space before the hyphen, and the hyphen and anything that follows.

• So I’m going to delete what’s in the find box.
• I’ll type a space character, hyphen, and then the wildcard, again, Shift + 8.
• I want to replace that with nothing.

So I can see two records here that have hyphens. Click Replace All, and it made two replacements.

Remove OF and Text

The final thing I’m looking for is the word of, so I wanted to find the word of which is on its own, not as part of another word.

• So I’ll, again, clear out this Find What. This time I want it to replace whatever is before that.
• I’ll start with the wild card, Shift + 8 and space O F space.
• Replace that with nothing. So it should find one, two, three, and ignore this one.
• Click Replace All made three replacements and it left the name that has OF untouched.

So you can use, Find and Replace with wildcards to either remove text before or after a specific character or string of characters.

______________________

Excel Find and Replace with Wildcards

______________________

Since you’re reading this blog, you probably love spreadsheets. And do you love word games too? If you do, keep reading for a couple of ways to have fun with words in Excel!

Wordle Fun and Failure

Do you play the online Wordle game every day? I usually play that game first thing in the morning, to get my brain working.

I’ve only shared one of my Wordle scores online, and that was my embarrassing failure, on December 22, 2022.

What do you think the correct word was? The answer is below the screen shot.

That’s right – the correct answer was “Excel” and it took me 4 guesses to get it!

And earlier this week, the May 1st word was “Range”. It took me 4 guesses to get that one too. (Sigh)

I hope you have better luck with Excel-related words!

Guess the Word Game Offline

My granddaughter (Miss A) came to visit last weekend, and she loves Wordle too. After we finished the day’s game online, we decided to make our own “offline” version of a “Guess the Word” game.

To get started, Miss A drew a 5 x 6 grid on paper, and I secretly wrote a 5-letter word on another scrap of paper.

Miss A wrote her first guess in the grid, and asked me to check it, with these instructions:

• Circle any letters that are in the answer word, but in the wrong spot
• Draw a square around any letters that are in the answer word, and in the right position

It only took her 4 guesses, and then she challenged me to guess her secret word.

Printable Guess the Word Game Cards

We really enjoyed the game, so after Miss A headed home, I decided to make word game cards in Excel. That will make it easier to play “Guess the Word”, the next time she comes to visit!

While we played the game, Miss A wrote out the alphabet letters each time, to keep track of which ones she had used. That was a smart idea, so I added the 26 letters below the 5 x 6 grid.

At the top of the cards, I put the symbols that we used, using the WingDings font for the circle and square.

Play Guess the Word

When the game card was finished, I copied it, and pasted 3 more copies on the Excel sheet, so you can print them, 4 to a letter-sized page.

You can download the Excel Guess the Word Game Card template from my Contextures site, and have offline fun with your family and friends.

There are instructions on that page too, for how to play the game.

Or, you can make your own rules!

More Fun with Words in Excel

For another fun way to combine words and spreadsheets, you can create a word cloud in Excel.

In the screen shot below, you can see the word cloud that I created, based on the lyrics from a Bruce Springsteen song. The word size is based on the number of times that word is repeated in the song.

Can you guess which song I used? The answer is below the screen shot.

Excel Word Cloud Workbook

I made that word cloud last January, using a file that I downloaded from Robert Mundigl’s Clearly and Simply blog.

There are instructions in the workbook, on how to change the settings, including the font size and colour.

I wrote about Robert’s word cloud tool, and the settings that I used, to get a large black font.

The default settings, shown in the word cloud below, were too small for my tired eyes!

_____________________

Excel Template Guess the Word Game Cards

______________

Create a monthly Excel calendar, based on a few short formulas. Next, add formatting and a heading, and if you’re feeling fancy, show a list of the month’s holidays too! This example uses functions that are available in Excel 365, and in Excel for the Web.

Make an Excel Calendar

In the video below, you’ll see the steps to create a printable monthly Excel calendar, based on a few short formulas.

After the calendar is set up, you can change the year or month number, to update the calendar, with that month’s holidays highlighted.

• Note: The sample file uses the SEQUENCE, FILTER and TEXTJOIN functions that are available in Excel 365. If you don’t have that version, you can download the sample file, and then open the workbook in Excel for the Web.

Video: Make a Monthly Excel Calendar Step-by-Step

This video shows how to make the monthly calendar, starting from scratch.

It’s about 11 minutes long, and here’s the timeline of all the steps that are included. Most of the formulas are short – only the last formula is a bit complicated. I’ll walk you through that formula, so you can see how it works.

Video Timeline

• 00:00 Introduction
• 00:13 Start Calendar
• 00:58 Year and Month
• 01:25 Date Formulas
• 02:20 Month End
• 02:50 Sunday Formula
• 04:56 Calendar Formula
• 05:32 Calendar Format
• 06:15 Calendar Heading
• 07:13 Hide Dates
• 08:35 Highlight Holidays
• 09:48 List Holidays
• 10:22 Get the Excel File

Get the Excel Monthly Calendar

In this example, the Excel worksheet has 5 cells with formulas, and two data entry cells — for the year and the month. Change the year or month number, to see a different calendar. Holidays from the selected month are highlighted, and the holiday names are listed at the top of the calendar.

Visit my Contextures site to download the Excel sample file, and for the written steps.

Note: The sample file uses the SEQUENCE, FILTER and TEXTJOIN functions that are available in Excel 365. If you don’t have that version, you can download the sample file, and then open the workbook in Excel for the Web.

_____________________

Create Excel Monthly Calendar From 6 Formula Cells

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

______________