Choose From a 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.
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.
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!
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“ or“jjjj“.
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.
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.
A Flexible TEXT Formula
In a cell named TextDay, an INDEX/MATCH formula returns the code for the selected language.
On the data entry sheet, I changed all the TEXT formulas, to use TextDay as the second argument.
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.
Just remember to refresh any pivot tables that are based on the source data.
Download the Sample File