This video asks the question: “Can a single cell have multiple custom formats in Excel?”
Answer: Yes! In fact, it can have two, three, or as many custom formats as you wish.
How to Change Number Format based on a Condition?
To demonstrate this ability, we will build a simple test report that allows the user to select between Total Sales and the Count of Apps in an App list.
Observe the difference in the formatting of the answers.
When the user select Total Sales, a Currency Style is applied to the answer.
When the user selects Count of Apps, the Number Style is applied to the answer.
The same cell is taking on different formatting instructions based on the displayed answer.
Take a look at the sample data set.
On the Data tab we have a list of Apps and their associated Sales. We will build our mini-report on the Report tab.
The first step is to create a Data Validation dropdown list for the user to select one of the two calculations (Sales vs. App count).
On the Report sheet, select a cell (ex: C4) and create a dropdown list by selecting Data (tab) – Data Tools (group) – Data Validation. Set the Data Validation to allow for a List. In the Source field, type “Total Sales,Count of Apps”.
Test the dropdown for proper functionality and give the cell a light green fill to help it stand out visually.
Now we will build the dynamic number format in the cell directly below the Data Validation dropdown (ex: C5).
We don’t want to set the number format directly on the cell because the formatting will be applied to the cell regardless of the answer being displayed. Under normal conditions, the formatting of cell C5 can’t be influenced by the choice made in cell C4.
Featured Course
Excel Essentials for the Real World
Conditional Formatting to the Rescue
Conditional Formatting will be the tool of choice to control the number style applied to cell C5 based on the option selected from cell C4.
Building the Logic
Before we can create the dynamic number format, we need to create the formula that will select a calculation based on the option selected from the dropdown list.
In cell C5, enter the following formula:
=IF(C4=”Total Sales”, SUM(Data!B3:B29),COUNTA(Data!A3:A29))The logic of the above IF function is to determine if cell C4 is displaying the text “Total Sales”. If it is, we will total all of the sales in Column B of the Data sheet. If cell C4 does NOT display the text “Total Sales”, we can infer that the cell is displaying the text “Count of Apps”. We could count all of the sales, but if an App has no sale it would be ommitted from the count operation. Instead, we will count the Apps in Column A of the Data sheet. We want to count all cells regardless of their contents (i.e. numbers or words); the COUNTA function is best used for this purpose.
Test the answers by selecting the different options in cell C4.
If you have more than two options in your dropdown, you will need to create a nested IF formula utilizing multiple IF functions.
If you require a refresher on creating nested IF functions, see the video entitled Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions).
Back to Conditional Formatting
At this point we will use Conditional Formatting to create and manage the various number styles for the differing answers.
Select cell C5 and then select Home (tab) – Styles (group) – Conditional Formatting – New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format. This will allow us to check the selected value in cell C4 to alter the formatting of the number in cell C5. The formula we will write is…
=$C$4=”Total Sales”
If this formula results in a True condition we will format cell C5 by selecting the Format… button and setting the following display option.
Select Format – Number – Currency – Zero decimal places (click OK when finished to exit the dialog box)
Test the feature by selecting the different options from the dropdown in cell C4. When we select Total Sales, the answer is in Currency style. When we select Count of Apps, the number is in the original Decimal style.
We would like the answer for Count of Apps to be bold.
Select cell B5 and set the cell to Bold.
The problem is that the cell is bold regardless of the number being displayed.
To keep the Bold from being applied when displaying Total Sales, select Home (tab) – Styles (group) – Conditional Formatting – Manage Rules.
In the Conditional Formatting Rules Manager, select Edit Rule.
Select Format in the Edit Formatting Rule dialog box.
In the Format Cells dialog box, select the Font tab and select Regular from the Font Style list. Click OK when finished.
Notice when cell C4 is Total Sales, the answer in cell C5 is flagged as Bold.
Conditional Formatting is overriding this setting and reverting the cell back to Regular.
Making the data source list dynamic
We would like to have the ability to add new Apps to the App list on the Data sheet and not have to constantly update the formulas on the Report sheet to match. We just need to “upgrade” the plain table on the Data sheet to an official Excel Table.
Select anywhere in the data on the Data sheet and press CTRL-T (or select Home (tab) – Styles (group) – Format as Table). In the Create Table dialog box, click OK.
If we add a new entry to the bottom of the App list, we immediately see the table’s range extend to absorb the new entry.
If we return to the Report sheet, we can see that the formula has updated to include the new row’s data.
=IF(C4=”Total Sales”, SUM(Data!B3:B30),COUNTA(Data!A3:A30))What used to end at row 29 is now ending on row 30.
If we switch to Count of Apps, we see the updated number (from 27 to 28).
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
