One of the downsides to having all kinds of templates and style sheets in MS Excel is that you pretty much get locked in to creating sheets that look like everyone elses which doesn’t do much for creativity or inspirational ideas. This is why it’s a good idea to learn how to use Excel’s formatting tools.

To begin, start by creating a table that has rows and tables, like the one shown below.



Then, highlight the whole table …



Next, click on the table with your right mouse button, to get the following pop-up menu:



Choose the Format Cells option, to get the following window:



Using just these simple tools, you can create pretty much anything you’ve seen in Excel’s pre-formatted templates. To see how, we’ll move right across the top menu bar; first is the Number format option list where you can set the number in the table to represent a regular number, a dollar amount, a time or date, or whatever we like; for this example, we’ll chose currency and go with the default of two decimal places that makes out table look like the table below:



Next on the menu is Alignment.



All of these options are about making the contents of a cell or group of cells sit in their cell the way you want. Mostly they are about putting cell content at the cell, top, bottom, left right or centered; or whether text is wrapped on not; there is also an option to make text or numbers appear at an angle in the cell; but because we just want to show currency, we’ll choose to just center it.



Our table now looks as follows:



Next on the menu is Font.



Since it works exactly like the Font command in every other Windows application we’ll just stick with the default and move on to the Border option.



This is the section where a lot of the magic happens, it’s divided into three sections; Line, Presets, and Color. The line section allows you to choose what kind of border to draw around whatever cells you’ve highlighted - in this case we’ll choose a double bar.



You’ll notice that nothing happens when you choose it and that’s because you have to tell Excel where you want that border to exist, and that’s what the Presets section is about. Since we want a border that goes all the way around the outside of our highlighted area, we’ll select the following option:



But, we’d also like all the borders between each cell to have a border so we’ll choose the following option:



The other icons are for adding or removing certain walls of the border, for example, if you’d like to not have a border across the top, you’d click the following option:



Then, all that’s left to do is choose a color for the border.



Notice how once a color has been selected, the line color above changes to reflect your choice. Unfortunately, the border you added earlier does not change, so you must make your choices again to create the new color you’ve chosen.

Next up on the menu is the Fill feature.



Here you can add color to the background of the cells themselves. You have two options - to fill the cells you’ve highlighted with a background color or a pattern with a color. Let’s look at background first.

You simply click on a color to choose a background color, or click the More Colors button for a color wheel that offers more color options. Or you can click on the Fill Effects button which will display the following dialogue box:



You will be allowed to add colors or a shading style to create a gradient (a gradient is where color changes slowly across a cell or cells). The top part of the pane allows you to choose one or two colors of your choice, while the bottom selects the direction of the gradient. For our example, we’re going to choose light pink and a somewhat dark blue for the colors and from center as our shading style.



Then click the OK button and we’re almost done.

The last menu choice - Protection - isn’t really a formatting option; it allows you to lock the contents of cells so that you don’t accidently overwrite formulas. For now, let’s click the OK button and take a look at what we’ve done to our original table:



The effects shown here can be used in very nearly unlimited and creative ways to show off your sheets in ways that stand out far more than you’ll ever get using the cookie-cutter styles that come with Excel.

They can be used in titles, or in descriptions, or to create extra borders around the edges of your sheet, or to highlight certain areas, etc. They can also be used to alter your sheet after applying a style if you’d like to change things up a little bit.

This Tech Tip is brought to you by the Business and Technology Section ... IT solutions for today's CPAs. For more information and to view an archive of previous Tech Tips, please visit us here.

Do you have specific topics you would like to see covered in Tech Tips? Email any suggestions to communications@ficpa.org.