Choose From List to Change Excel Data

Do you need a quick way to show different data in a worksheet? For example, on an order form, you could let people select a region, and automatically include the shipping cost or tax rate for the selected region. I added an English/French selector to a workbook last week, so a formula would work correctly, in either language.

What Day Is It?

If you’re creating Excel reports, sometimes you need to summarize the data based on the weekday name. That way, you can see if there is a particular day when you get the highest number of calls to the customer service department, or make the most sales.

A pivot table is a great way to summarize data, but it can group dates by Years, Months, and other settings, but it won’t group things based on weekday names. Its “Days” grouping option shows the day of the month, not the weekday.

pivot table group by date

Use the TEXT Function

To find the weekday for each date, you can add a column in the source data, and use the TEXT function to calculate the weekday.

If the date is in cell B2, you can enter one of the following formulas in another cell in row 2:

  • To show the short name for the weekday: =TEXT(B2,”ddd“)
  • To show the full name for the weekday: =TEXT(B2,”dddd“)

Later, you can use that Weekday field in the pivot table, instead of the original Date field.

text function date format ddd

Quel Jour Est-On?

In Canada, we often have to switch between English and French, and the workbook with this TEXT function wasn’t working correctly when someone with French language settings opened the file. Excel didn’t understand the formatting code, so all the weekdays showed up as “ddd”, which isn’t too helpful in data analysis!

text function date format ddd french

When you open a workbook, Excel automatically adjusts the function names, based on your language settings, so TEXT became TEXTE. However, it doesn’t automatically adjust the formatting code (“ddd”) that is typed in the second argument.

For French users, “day” is “jour”, so the formatting code should be jjj orjjjj.

So, every time the file travelled back and forth between the Quebec and Ontario offices, someone had to manually change the formatting code in all the TEXT formulas.

Choose Your Language

To make it easier to change the language codes, I added a drop down list at the top of the worksheet, where you can choose English or French.

drop down list english french

On another sheet, I built a small lookup table that stores the codes for both languages. You could add other columns to the lookup table, for prices, shipping rates, descriptions, or any other data you need, based on the selection.

lookup table english french

A Flexible TEXT Formula

In a cell named TextDay, an INDEX/MATCH formula returns the code for the selected language.

index match formula returns TextDay code

On the data entry sheet, I changed all the TEXT formulas, to use TextDay as the second argument.

TEXT function with TextDay code

Now, the formula automatically adjusts when you select a language, so people can quickly see the results that they need, in either language. Here’s how it looks when French is selected, with French language settings.

TEXT function with TextDay code French

Just remember to refresh any pivot tables that are based on the source data.

Download the Sample File

You can download the sample file from my Excel Sample Files page, to see how it works. In the Functions section, look for FN0046 – Change Language for TEXT Function. The zipped Excel file is in xlsx format, and does not contain macros.

Also take a look at FN0029 – Show Text in Selected Language.

_____________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.