How to Format Excel Cells Perfectly With One Click

Does this sound familiar? You work on an Excel sheet and are almost done with it. Now, you just want to “brush it up” a bit. But: It seems that the formatting takes even longer than working on the calculations. In this article we look at techniques to apply a format with as few clicks as possible. Let’s start saving time now!

Method 1: How to format cells manually

Potential time saving:

Well, probably you are not here for learning it the long, inconvenient way. But anyway, I quickly summarize how to apply a format the “traditional” way in Excel.

Actually, there are several way to change font, number formatting, colors, borders. The first one would be via the ribbon:

On the Home ribbon, you can find the quick settings of font style, colors, borders and so in the “Font” group. Next to it, you can see further settings in the “Alignment” group and the “Number” group. Everything, no need to elaborate that in detail.

One comment: the small arrows in the corners (also highlighted yellow), lead to the Format cells window. It contains more detailed formatting options. Alternatively, you can just press Ctrl + 1 on the keyboard to open that window.

Method 2: Shortcut formatting with the format painter

Potential time saving:

Once you have perfectly formatted a cell (for example with the method 1 above). With the Format Painter, you can easily transfer this formatting to a different cell.

1. Select the original cells.
2. Click on the Format Painter on the Home ribbon.
3. Click on the unformatted cell to apply the format.

You want to apply this cell layout to multiple cells? Double click on the format painter and the you can click on all desired cells until you press Esc or click on the Format Painter button again.

Also: It works for whole ranges of cells. You can just select multiple cells and press on the Format Painter button.

Recommendation: This could be a great button for the Quick Access Toolbar. I have added it to my Quick Access Toolbar – in all Office programs at the same position. Now, when I press the Alt button and afterwards 4 (because it is in the forth position in my Quick Access Toolbar), I can quickly use it without the mouse.

Method 3: Apply cell styles to save a lot of time

Potential time saving:

Now, we get increasingly faster with formatting. Our next advice is to use the built-in cell styles.

Cell styles are a great way to apply layout settings to single cells are cell ranges. And later, you can easily change the format within the cell style definition.

Here is, how it works:

1. Select one or multiple cells.
2. Click on your desired cell style. It’s located on the Home ribbon in the Styles section.

Changing a cell style is quite simple, but requires some clicks through the menues.

1. Right-click on the style you want to change.
2. Then, click on Modify.
3. Now, you can click on “Format” to change the settings in detail.

The changes will be applied to all cells that use this particular style.

Disadvantages:

• When you copy worksheets several times from different files, cell styles might accumulated. I have noticed that especially for some Excel exports, for example from SAP.
• If you change a cell style, it only applies to the current Excel workbook. When you create a new Excel file, you have to do the same changes again.

Method 4: Use Office themes

Potential time saving:

This method works best when you already have a “clean” formatting in your Excel file. For example, if you have consequently used the cell styles in method 3 above.

But nonetheless, it’s often a worth a try: Create your own Office theme. This can save a lot of time when it comes to colors and fonts. The advantage: You can save a theme and use it throughout the Office suite. That way, you can transfer your corporate colors to all your documents.

How it works? Quit simple:

1. Go to the Page Layout ribbon.
2. Click on the theme that is already close to your desired layout.
3. Modify colors and fonts with the buttons right next to the Themes button.
4. Click on “Save Current Theme”.

Next time you want to use this theme, just click on “Browse for theme” and select it on your hard drive.

Method 5: Use Professor Excel Tools to use your individual formatting – with just one click

Potential time saving:

This is probably the feature I use most often of the Excel add-in “Professor Excel Tools“. It’s fair to say that I have saved days, maybe even weeks of work just with these two buttons.

1. Just select the cells to format.
2. Click on either the headline or content / body button.

As you can see, it’s very fast, right?

The good thing about this method: You just define it once – and you can apply the formatting to any workbook.

Defining your favorite formats is also very easy:

1. Apply your favorite heading or content / body layout to a cell.
2. Go to the Tools Settings (on the right side of the Professor Excel ribbon).
3. On the “Quick cell formatting” tab, select your pre-formatted example cells for heading and body. Click on Save and Close to save the formatting.

Image by garageband from Pixabay