Excel is a spreadsheet program that we use to perform calculations, but not all cells and ranges in Excel store calculations.
In Excel, when we have a specific cell that performs a calculation, it can sometimes be difficult to locate that cell, especially when working with large amounts of data. This is where the Calculation Style in Excel becomes useful, as it helps you quickly identify the cells that contain calculations.
In Excel, there is a set of formatting options that you can apply to cells to mark them as special cells. Among these predefined formatting options, one specific formatting option is called the Calculation Style.
What is Calculation Style in Excel
Calculation style is the part of Excel’s predefined cell styles that are available to apply. The core purpose of using calculation style is to highlight those cells which have formulas. You can apply this formatting to all the cells where you have formulas to make them stand out.
Let me give you a real-life example: let’s say you manage a small business and track monthly sales in Excel. You have a column for sales figures and another for monthly expenses. You use a third column to calculate the profit by subtracting expenses from sales.
You apply the calculation style (light grey background #F2F2F2, bold font, and orange) to make the profit column stand out. You can quickly spot the calculated profit cells whenever you review the data.
The good news is that in Excel, there are pre-defined styles that you can use to apply to different cells.
Benefits of Applying Calculation Style
There are many benefits to using the Calculation Style, whether you apply it to a single cell or multiple cells in your workbook.
From my own experience of using, it for several years, I’ve found a few key advantages that make it especially valuable.
These benefits not only improve clarity for anyone working with the file but also help maintain the integrity of your calculations.
One of the major benefits that I have found of using Calculation Style cells is that they clearly indicate which cells in your workbook contain formulas that should not be modified.
By applying this style, you signal to anyone using the workbook that these are special cells meant for calculations, and no changes should be made to their formulas.
This provides clarity and helps maintain the integrity of your data, ensuring that important formulas remain intact while the workbook is being used.
Apart from this, we have more benefits:
- When you use a consistent Calculation Style across your workbooks, it becomes much easier to identify all the cells that contain formulas. For example, if you are working with a large dataset that includes only a few calculated cells, applying the Calculation Style makes those cells stand out instantly. This saves time and effort, as you don’t need to manually search through the entire sheet to find where formulas are applied.
- The third benefit I’ve noticed while using the Calculation Style is that once you apply it to all your formula cells, you can easily locate them using the Find and Replace option. This allows you to quickly identify and edit formula cells without having to navigate through the entire worksheet manually. I’ll go into more detail about how this works later in this tutorial.
Apply Calculation Style in Excel
If you work with a lot of calculations in Excel, which is the core use of Excel in the real world, there’s a specific cell style that you can apply to the cell or a range of cells with a calculation.
In the following example, you have a sum formula in cell A15, and in this cell, we have applied the calculation style from the Excel cell styles.
As you can see, it has an orange font color, light grey as the cell color, and a light border around the cell.
If you try to apply these styles manually, you need to use different options, but with the calculation cell style, you can do it with a single click.
If I already have cells with Formula and I want to Apply Calculation Style all the cells One Go?
Let’s say you already have cells in your worksheet that contain formulas, and now you want to apply the Calculation Style to those cells.
Instead of going to each cell or range one by one, you can use the Go To Special option to select all formula cells at once and then apply the Calculation Style formatting in a single step.
To do this, you just need to follow these simple steps I’ve mentioned below.
First of all, press the keyboard shortcut Ctrl + G and after that press the keyboard shortcut, the second keyboard, shortcut Alt + S. This will open Go To Special dialog box.
Now, open the Go To Special dialog box and select the Formulas option, which is the third choice on the left. After that, click the OK button. Excel will instantly select all the formula cells in the current worksheet.
In the end, go to the Home tab, open the Cell Styles drop-down, and choose Calculation Style. This will apply the formatting to all the selected cells at once.
Create Your Own Calculation Cell Style
Instead of using only the predefined Calculation Style or other built-in Cell Styles, you can also create your own custom Calculation Style.
This allows you to define a style that represents calculated cells in a way that best suits your workbook. To do this, you have two methods you can follow.
- Go to the Home tab and click Cell Styles in the Styles group.
- Select “New Cell Style” at the bottom of the dropdown menu.
- Enter a name for your new style, such as “My Calculation Style.”
- Click Format to open the format dialog box.
- Choose your desired formatting options, such as font color, bold text, and background shading.
- Click OK to save the style and apply it to the cell.
Merge Calculation Style from a Workbook
The only downside of custom cell styles is that they are stored within a single workbook.
This means if you create a custom Calculation Style in one file, you won’t see it automatically in another file, which makes it a bit limiting if you want to use the same style across all your workbooks.
The good news is that Excel gives you a way to overcome this. You can use the Merge Styles option to copy cell styles from one workbook into another.
Simply open both workbooks, go to Home → Cell Styles → Merge Styles, and select the file that contains your custom style. With just a click, the style is imported and ready to use in your current workbook.
Here are detailed steps:
- First, open the workbook containing the calculation style you want to import and the workbook where you want to use the style.
- After that, go to the Home tab in the destination workbook and click Cell Styles in the Styles group. At the bottom of the Cell Styles menu, click Merge Styles.
- In the end, in the dialog box that appears, select the workbook that contains the styles you want to import and click OK.
The styles from the other workbook, including your calculation style, will now be available in the destination workbook.
Modify the Calculation Style in Excel (Any Other Cell Style)
When you right-click on the Calculation Cell Style and select the Modify option, the Style dialog box appears. In this dialog box, you can change all the formatting options that are applied to the style.
This dialog box is the same one that appears when you create a new Cell Style in Excel.
When you edit the Calculation Style, you will notice that three formatting options are already applied in the Style dialog box: Font Style, Cell Color, and Border. In the dialog box, these three formatting options are tick-marked, showing they are active.
In the top-right corner of the Style dialog box, you’ll find the Format button. Clicking this opens the Format Cells dialog box, where you can change any of the formatting options you want to include in the Calculation Style.
Now, if you want to apply other formatting options, such as Number Format, Alignment, or Protection, you first need to tick-mark them in the Style Includes section. Once they are selected, you can click the Format button and then define the specific formatting styles for each of them.
Note – If you want to remove a formatting option that is already used in the formatting, simply uncheck it in the Style dialog box and then click OK.
Keyboard Shortcut to Apply Calculated Style
There isn’t a direct keyboard shortcut to apply the Calculation Style to a cell.
The only option available by default is to use a shortcut to open the Cell Styles dropdown from the Home ribbon, then navigate to the Calculation Style, and press Enter to apply it.
However, there is a useful workaround. You can use VBA code that applies the Calculation Style to a single cell or a range of cells and then assign a keyboard shortcut to run that macro.
This way, you’ll have a direct shortcut that instantly applies the Calculation Style without going through the dropdown menu every time.
Sub ApplyCalculatedStyle()
Selection.Style = "Calculation"
End SubTo use this VBA code, first copy it from here. Then, open Excel and go to the Developer tab.
From there, click on Visual Basic to open the VBA editor. In the editor, insert a New Module, and paste the copied code into the code window. Once done, you can save and run the code whenever needed.
Now, once you’ve added the VBA code, the next step is to make it easier to use by adding the macro to the Quick Access Toolbar so you can run it with a keyboard shortcut.
To do this, go to the File tab, then click on Options.
In the Excel Options window, select Quick Access Toolbar from the left-hand menu. From there, you can add your macro to the toolbar and later assign a shortcut key for quick access.
In the Quick Access Toolbar section, the first step is to select Macros from the “Choose commands from” dropdown. After that, look for the macro we created earlier, in my example, the VBA code was saved with the name “ApplyCalculatedStyle”.
Select this name from the list and then click Add to move it into the Quick Access Toolbar list.
On the right side of the dialog box, you’ll see two arrow buttons that allow you to move your macro button forward or backward in the toolbar.
The position you choose here determines the keyboard shortcut. For instance, in my case, the macro is placed in the second position on the toolbar, so the shortcut to run it is ALT + 2.
Similarly, if you place it in the first position, the shortcut will be ALT + 1, and so on.
Other Useful Styles that You can Apply
And here we have some other useful cell styles that you can use:
Title Cell Style
If you want to change a cell value as a title, you can apply the title style from the cell styles.
With the title style, you get a thin font style and a font size of 18.
Headings Cell Style
And you also have headings (1-4) styles to use on cell data.
In heading styles, you get a bold font style and a border at the bottom of the cell.
Total Cell Style
You also have a total cell style as well that could be useful for you if you work with accounting data.
Frequently Asked Questions
The purpose of the Calculation Style in Excel is to highlight cells that contain calculations, making them stand out from other types of data.
Calculation Style in Excel is a manual cell formatting that you apply directly to a cell. Unlike Conditional Formatting, it does not require you to write any rules, and it cannot be applied automatically.
Yes, absolutely, you can modify the calculation style and change all the six parts that a calculation style is made up of.
The Calculation Style has been available in Excel since the 2007 version. This was the release where Excel introduced the Ribbon interface, and along with it, a Cell Styles drop-down was added on the Home tab.
Yes, you can use the Merge Styles option in Excel to copy the Calculation Style from one workbook to another.