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.