View Basket
Recover BasketMicrosoft Excel tutorials
Using Cell Styles in Excel 2007
Using Cell Styles
Excel contains a very wide variety of formats that you can apply to your cells. Naturally, when you create your worksheets you want your formatting to be consistent. Cell Styles offer an excellent way of achieving this.
Cell Styles are located in the Style section of the Home Tab of the Excel Ribbon. When you click on the Cell Styles button the categorised Cell Styles gallery is revealed. The gallery offers a live preview; hovering over any of the styles reformats the current selection within your worksheets, giving you a preview of what your data will look like if you choose that particular style.
The first benefit of using styles is that you apply several formats in one operation. For example, say we choose the style called "Heading One"; we automatically change the size, alignment and colour of our text. We can speed up the formatting process even further by using the Format Painter to copy styles into the other cells or even other worksheets. Simply highlight an area that already has a cell style applied, double-click on the Format Painter to make sure it stays highlighted then drag across the appropriate range(s) of cells. When you have finished, click once on the Format Painter to deactivate it.
Having applied cell styles in various parts of a workbook, you can take advantage of the most important benefit offered by styles; if we modify the attributes associated with any of the styles used in a workbook, the formatting of all cells to which those styles have been applied will automatically be updated.
When modifying a style definition, since we're not directly applying formats to any of the cells, it doesn't matter which cells are highlighted. To modify the attributes of a style, click on the Cell Styles button, right click on the name of the style and then choose "Modify". You will then be presented with the six categories of formats which can be included in a style: Number, Alignment, Font, Border, Fill and Protection. The list is fairly comprehensive; it includes just about everything that Excel has to offer in the way of formatting.
You will notice that you have the facility of activating and deactivating categories as necessary. Any categories that do not apply to a particular style can simply remain deactivated (i.e., checkbox not ticked). Simply click on each relevant category and make your selections. When you click OK to confirm these changes, all cells to which the style has been applied will be automatically updated.
Creating your own styles
As well as using and modifying Excel's built-in cell styles, you also have the facility of creating your own. One simple way of doing this is to highlight a range of cells and apply all the formatting attributes that you want to add to your style and then turn those attributes into a cell style. When you create your cell style, Excel will pick up all the attributes from the highlighted cells and include them in the style. Let's take an example; let's say we want to create a cell style for headings with text angled at 45 degrees.
We set the text angle to 45 degrees by choosing "Angle Counter Clockwise" from the Alignment section of the Home Tab of the Excel Ribbon. Let's say that we also want to centre the text horizontally and vertically, change the text colour and make the text bold and slightly larger. Having chosen the relevant options, to create a style which has all these attributes, simply highlight a cell that contains these formats and choose "New Cell Style" from the Cell Styles drop-down menu in the Styles Tab of the Excel Ribbon.
The Style dialogue will then appear. Here, we enter a name for our new cell style: let's say we call this cell style "Heading". The Style dialogue window contains six categories of formatting information which can be activated or deactivated via a series of checkboxes. We can now switch off those categories of formatting that don't apply. Thus, in this example, we won't need the categories "Number", "Border", "Fill", or "Protection"; so we can uncheck all of them. Next to each of the aforementioned categories, you'll notice that Excel has picked up all the attributes of the highlighted cell; so it is not necessary to click on each category to set any options; all we need to do is click OK.
When creating a style in this way, it's important to realise that Excel does not apply the style to the cell or cells on which the style was based. If you want to apply the style to the cell, you need to do so explicitly by highlighting the cell and choosing the name of your new cell style from the Cell Styles drop-down menu in the Styles section of the Home Tab of the Excel Ribbon. Having created a style you'll notice that Excel has an extra category called custom in the Cell Styles drop-down menu and it is here that you can choose the style that you just created. Although the highlighted cell may look the same, it will now be associated with the new style.
We would then proceed to apply the style wherever it was needed throughout our workbook. This, in itself, is a big time-saver. In addition, however, if we decide to change the format of our heading, all we need to do is to modify the attributes associated with our cell style and all of our headings will be automatically updated.