This website is designed as a resource for people looking to get the most out of Formatting Microsoft Excel. For conciseness sake, I have to assume that anyone looking at this website has a basic foundation in creating formulas and numerically useful spreadsheets Microsoft Excel.

I want to give you the knowledge to work in Excel more efficiently, and to help you to take full advantage of some of the less common, yet infinitely useful tools in Excel. Over the next few months I will begin rolling out detailed tutorials on how to write code in Microsoft Excel VBA, how to understand conditional formatting, how to use Microsoft Excel formatting codes, how to use pivot tables, overviews on formatting data in Excel, managing views in Excel, Navigating Microsoft Excel with the Keyboard, and last but not least, managing data connections.

One of the main questions you should be asking yourself is: What can I format in Microsoft Excel? While the obvious answer might be “Everything,” many people simply don’t know all of the tools that are available.

Types of Excel Formatting

- Cells
-      Number Formats
-           Number Format Codes
-      Invisible Formatting
-      Format Column Width and Row Height
-      Cell Color
-           Gradients!
-           Patterns (i.e. crosshatching)
-      Fonts
-           Font Color
-           Font Styles
-           Size
-           Style (Italics, Bold, Underlined, Superscript, Subscript, Strikethrough)
-      Cell Borders
-           Hashed, Solid, Double, or single
-          Border Colors
-      Alignment
-          Vertical or Horizontal
-           Indentation within Cells
-           Wrapping Text
-           Merging Cells
-           Text Direction (Don’t worry, it’s Kosher)
-           Font Rotation
-      Hyperlinks
-      Hiding Rows and Columns
-      Charts
-      Pivot Tables
-      Images
-      Shapes
-      SmartArt
-      Text Boxes
-      Headers/Footers
-     WordArt

-      Tools for Formatting:
-           Format Painter
-           Clearing Formatting
-           Themes
-           Navigating Excel by Keyboard
-      Views
-      VBA

Invisible Formatting in Excel

Want to hide the contents of a cell without changing the font to white? You can set the format of a cell to blank. To do this, right click on a cell and select format cells. On the number tab, select the custom option. In the text box, enter 3 semicolons and hit “OK”. This will format the cells as blank. Now if someone changes the font color of the whole sheet, they won’t be bombarded with cluttered content that wasn’t visible before.

VBA Invisible Formatting

Selection.NumberFormat = “;;;”

Formatting Excel Cell Colors

Formatting Cell Color is easy. From the home tab, there is a shortcut with a picture of a paint bucket on it. You can select different colors by clicking the arrow nearby. The default color is yellow. This is the quickest way to format the cell color. You can also right click any cell, and select ‘Format Cells’. In this window, you can select the fill tab. From here you can select more colors, patterns, and even gradients!

Here is some vba code to control these settings:

VBA formatting excel Gradients

With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With

VBA Formatting for a Pattern

With Selection.Interior
.Pattern = xlGray75
.PatternThemeColor = xlThemeColorAccent1
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Formatting Excel Fonts

Formatting fonts is easy in excel. Right click on a cell and select “Format Cells”. From the format cells menu, click the “Font” tab. Here you have access to change all the font properties of a given cell. There are plenty of fonts to choose from, but if your particular font of choice is not there, it is possible to download new fonts online. Just be sure the website you are using is reputable and won’t give you a virus.

Font Size is measured in fractions of an inch. Divide any font size by 72, and that will tell you how large the font is. For example, a 36 point font would be 36/72 inches tall, which is equal to half an inch.

Beyond the font and size itself, you can choose from Regular, Italic, Bold, and Bold Italic. There are also multiple underline styles available from this menu, including single, double, single accounting, and double accounting. Here you can see the difference in the types.

Excel’s Accounting style underlining spans the entire width of the cell, while standard underlining only underlines the font within the cell.

You can also add superscript, subscript, and strike-through to a cell. Now, the moment you have all be waiting for…… Drumroll……

How do I add superscript to only a part of a cell?

You can change any part of a cell’s font by going into a cell’s edit mode. You can do this by double clicking a cell, selecting a cell then clicking in the cell’s formula bar, or by simply pressing “F2″. Once you are in the edit mode, select the characters you want to raise or lower, or change the font of. Go Crazy!!! But not too crazy…

You can return the font of any cell to the default settings by checking the box that says “Normal Font”

VBA Formatting for Fonts

With Selection.Font
.Name = “Algerian”
.FontStyle = “Regular”
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

Formatting Excel Borders

The quickest way to format borders is from the home tab. In the Font section, to the left of the paint bucket, you should see a drop-down menu with many borders options. Most of the options you will need are in this menu, but for those who need to get a little more creative formatting cell borders, there is a draw your own borders tool.

To use this tool, you can first select a line weight and style from this menu. Then select the draw borders tool. You can do horizontal borders, vertical borders, and diagonal borders using this tool.

The other way to access border formatting is with the “Format Cells” option via the right click menu. After selecting this option, go to the “Border Tab”. Here you can choose line weights, color, and exactly which line you what to change.

VBA Formatting for Borders

With Selection.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With

With Selection.Borders(xlDiagonalUp)
End With

With Selection.Borders(xlEdgeLeft)
End With

With Selection.Borders(xlEdgeTop)
End With

With Selection.Borders(xlEdgeBottom)
End With

With Selection.Borders(xlEdgeRight)
End With

Formatting Excel Hyperlinks

Formatting Excel Hyperlinks is easy. Simply right click any cell. It does not matter if there is already text in it or not. From the right click menu select hyperlinks. This brings up a file browser. From here, you can either select a file to link to, or you can type in a web address i.e. “http://www.google.com”. If there is already text in the cell you had selected, the text will remain, and a link will go to the file or website desired. If there wasn’t already text in the cell, the web address or file address will be the text for the hyperlink. You can simply type over this text with your desired text, and the hyperlink will remain. You can link to other documents, other cells in a document (including the current document), or even to an e-mail with your default e-mail client. To remove a hyperlink in excel, you can right click and select “Remove Hyperlink”, or on the home tab in the ‘Editing’ section, you can click “Clear” and then “Clear All” or “Clear Formats”. formatting excel cell color, underline status, or any other font option will not affect the integrity of the hyperlink, though keep in mind, it may make it more difficult for a user to realize that the contents of the cell is a hyperlink.

Formatting Excel Hyperlinks in VBA

Use variations of the following code for formatting your hyperlinks.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
“C:\Program Files\MyFolder\MyFile.xlsx”, TextToDisplay:= _
“This is My Hyperlink to a file”

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
“http:\\www.google.com”, TextToDisplay:=”This is My Hyperlink to a Website”

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=”", SubAddress:= _
“Sheet2!A1″, TextToDisplay:=”This is a hyperlink to Sheet 2, Cell A1″

‘The following line of code changes the hyperlink address to an e-mail address. Two things are going on here, one is that there was already a hyperlink in the cell, and two, the cell already had text to display.
Selection.Hyperlinks(1).Address = “mailto:justinp.um@gmail.com?subject=Hello”

Selection.Hyperlinks.Delete

What are we trying to achieve when we format in Excel? Why take the time to make a spreadsheet look nice?

The ultimate goal of formatting is to assist the end-user in understanding data.

Don’t assume that just because you have put your data into a spreadsheet, that another person will be able to navigate it easily, or grasp the full depth of what you are trying to convey.

As someone with, quite frankly, terrible handwriting, I can testify to the importance of formatting. What if every textbook you read was handwritten? Or much more frighteningly, what if different parts of each textbook were written in the handwriting of the particular person who compiled it? You should certainly hope that those doing the writing have good handwriting; that way you can spend less time trying to navigate the words, and more time understanding the ideas behind them.

It is my opinion that my bad handwriting stems from my desire to get ideas down quickly, as my mind tends to run faster than my hands. Since working in Excel, this has not changed at all. More often than not, when I am exploring a new idea or analyzing data for the first time, my spreadsheets tend to become messy. I find myself squeezing everything as close together as I possibly can, or failing to label rows and columns.

Fortunately, computers give us the chance to review what we have created, without accumulating more eraser shavings than you can shake a preprocessed stick full of graphite at, you have the ability to reformat the data that you have presented.

If you know the proper tools that are available to you. Formatting becomes a much more bearable process. So lets take a look at those tools.

Excel Conditional Formatting

Basics

The first thing you should know is that conditional formatting in Excel takes priority over standard formatting of a cell when the two formats contradict. For instance:

changes to

without any adjustments aside from adding a conditional formatting rule.

You may be wondering, “When will I learn how to create conditional format rules in Excel?” If so, you are already one step ahead of the game. Read on to create your first basic rule.

Step 1. Select the range you would like to format.
Step 2. On the Home tab, click Conditional Formatting.
Step 3. Choose a Rule Type.

The preset rules have a lot to offer, but they don’t account for every need in Excel. Let’s look at them briefly.

As you can see, apart from offering range of value-conditions, Microsoft Excel also offers above and below the average, top 10%, and a few other useful criteria. Selecting any of these will bring up a menu for further options.

Fortunately, we have not been confined to preset conditional formatting rules; but before we address the custom conditional formatting rules, I want to mention data bars and color scales.

Data bars (pink) are drawn within a cell, while color scales (white to black gradient) actually change the fill color of the cell. Both of them are very useful for showing the magnitude of the data in relationship to the rest of the data contained within the rule , especially if the data is not sorted already sorted. (Each cell is compared to the range you selected when you created the rule)

To achieve this particular effect, I used a white font, the default pink data bars and added a conditional formatting rule to format all cells based on their values as follows:

You can probably figure out how to work the other preset rules within the New Formatting Rule Menu. If not, feel free to leave a comment, and I will do my best to elaborate.

Also, don’t forget to play around with the icon sets. They are pretty neat, albeit a little over the top for my taste.

Advanced Techniques

In my opinion, the most useful thing about Conditional Formatting is the ability to apply conditional formatting based on a formula. This was difficult for me to grasp until I learned what is actually going on inside the formula, so I will attempt to take my time with this explanation.

The general idea:

Cells are formatted when the expression you write evaluates to TRUE.

First, lets look at what it means for an expression to be TRUE:

You may already know that computers associate 1 with TRUE and 0 with FALSE. But did you know that computers would evaluate any of the following as TRUE? 1, 3, -5, 76, 875354, .03

This means that when we are creating a formula to format the cells, the result of the formula can be anything except 0, an error, or text.

An exception to this is the Boolean word TRUE, as this word has a value of 1. The Boolean word FALSE has a value of 0. Try typing =(TRUE + 1)*12 in a cell. Now try =(TRUE + 1)*12*FALSE.

A typical conditional formatting equation for range A1:A5 (your selected range) will look as follows, note that the function evaluates to a number (A.K.A. TRUE), if the evaluated cell has the value 3 in it:

=Countif(A1, 3)

**Note that this is distinctly different than

=Countif($A$1, 3)

In the first example, the relative reference (A1) will allow the conditional formatting to evaluate each cell relative to itself. In the second example, since the cell reference is constrained by the dollar signs ($A$1), every cell checks if cell A1 is = 3.

The following image shows an example of the difference.

Microsoft Excel automatically puts dollar signs in the conditional formatting when you click a cell to get the reference, so make sure to delete them if need be. For more on Dollar signs in Excel, including keyboard shortcuts for them, check out the formula page (coming soon)

Now, if you understand the math behind TRUE and FALSE, you will have a better grasp on the next more advanced concept:

Conditional Formatting “AND” Function

Ever run into a situation where the AND function in Microsoft Excel’s Conditional Formatting doesn’t work? Don’t Stress!
You typically are allowed to use the “AND” function with conditional formatting, though sometimes it can be a little bit picky.

The other day, I was attempting to build a formula to answer a question regarding conditional formatting. Unfortunately, the formula I wrote, combined with the “AND” function it was contained in, was too complicated for the Excel conditional formatting engine.

Fortunately, now that we understand the math behind TRUE and FALSE, we can use a back door to achieve the same effect as the “AND” function.

I will show you what that looks like; keep in mind that you don’t have to understand the formula to grasp the concept I am trying to show you. (I will explain the formula lower down in the page [use your browser's find function to search for "Indirect" or "Address")

The following formula will not work in conditional formatting, even though both parts of the "AND" expression evaluate to TRUE.

=AND((INDIRECT(ADDRESS(ROW(),1))>3),(INDIRECT(ADDRESS(ROW(),1))

If you don't believe me, Enter a number between 4 and 9 into a cell in the first column, and paste this formula anywhere else in the same row. If you did this correctly, you will see that the formula evaluates to TRUE.

So, how do we tell if two expressions are true in Excel without an "AND" function? The answer is in the math. An expression that evaluates to FALSE is always equal to 0 and an expression that evaluates to 0 is always FALSE; we can use this to our formatting advantage.

TRUE * TRUE = TRUE (1 * 1 = 1)
TRUE * FALSE = FALSE (1 * 0 = 0)
FALSE * TRUE = FALSE (0 * 1 = 0)
FALSE * FALSE = FALSE (0 * 0 = 0)

Understanding this, we can create an adapted formula that now works for conditional formatting.

=(INDIRECT(ADDRESS(ROW(),1))>3) * (INDIRECT(ADDRESS(ROW(),1))

If the expressions on the both sides of the multiplication sign evaluate to TRUE, then the entire expression evaluates to TRUE. If either side evaluates to FALSE, then the entire expression is FALSE.

This version can be used in Conditional Formatting even though the version using the "AND" function wouldn't work.

Try pasting this one in the same row as your previous number in Column 1. If the number is within the correct range, the formula will evaluate to TRUE. (TRUE*TRUE = TRUE). Can you think of how to create the back-door to the "OR" function?

A back-door to the "OR" function can be created using a simple addition problem, since any number not equal to 0 evaluates to TRUE:

TRUE + TRUE = TRUE (1 + 1 = 2)
TRUE + FALSE = TRUE (1 + 0 = 1)
FALSE + TRUE = TRUE (0 + 1 = 1)
FALSE + FALSE = FALSE (0 + 0 = 0)

**Important note: -1 evaluates to TRUE, just like +1. The following situation can arise: -1 (TRUE) + 1 (TRUE) = 0 (FALSE). If your formula might encounter such an exception, use the absolute value function around each expression: ABS(#).

ABS(-1) + ABS(1) = 2 [TRUE]

Excel Functions Referenced

I will soon begin a formula page on this website but until then, I will give a brief overview on formulas as it relates to this discussion.

1. Every formula in Microsoft Excel starts with the equals(=) sign.
2. Formulas are evaluated with the typical order of operations in math.

Try a Google search for PEMDAS. PEMDAS stands for Parenthesis, Exponents, Multiplication, Division, Addition, Subtraction. Multiplication has the same Order Weight as Division, and Addition has the same Order Weight as Subtraction.

In other words, due to the associative property, it doesn’t matter if you multiply first or divide first; in the same way, it doesn’t matter if you add first or subtract first. For a deeper explanation, Google the Associative Property of Multiplication or the Associative Property of Addition.

3. When describing a formula, any parameters surrounded by “[]” are optional and have default values.

For those of you interested in the Indirect and Address function used above, I will explain it here, as well as how it applies to conditional formatting in Excel.

Since formulas are evaluated from the innermost parenthesis first, lets start with Microsoft Excel’s Address() function.

Syntax: Address(Row_Number, Column_Number, [abs_ref], [return_type], [sheet_reference])

The Address function outputs the Cell Address of the cell at a specific row and column intersection. Here are some examples to give you an idea of how you can use it.

Address(1,1) = $A$1
Address(1,2) = $B$1
Address(2,1) = $A$2

With Optional Arguments:
Address(1,1,2) = A$1
Address(1,1,4) = A1
Address(3,5,4,”R1C1″) = R3C5
Address(3,5,1,”A1″,”Sheet1″) = Sheet1!$A$1
Address(3,5,1,”A1″,”[Book1]Sheet1″) = [Book1]Sheet1!$A$1

Note that all the arguments after row and column are Completely Optional. See Microsoft Excel’s Help for more information on this function.

Indirect() Function

Syntax: Indirect(Cell_address, [a1])

The Indirect function gives the value of a cell located at a specific cell address. Here are some examples to show you.
Lets assume the value in Cell A1 is the text “B1″, the value of cell A2 is the text “R1C1″ and the value of Cell B2 is the text “Over Here”
Indirect(“A1″) = “B1″
Indirect(A1) = Indirect(“B1″) = “Over Here”

Advanced Technique
Indirect(Row(),1): returns the address of the cell in the current row in column 1.
Indirect(1,Column()): returns the address of the cell in row 1 in the current column.

With Optional Argument
Indirect(R2C1,”R1C1″) = Indirect(“R1C1″,”R1C1″) = “B1″

Recap: Address() returns a Cell address using a row and column number; Indirect() uses an address to get a cell value.
We can use these 2 in combination with the Row() or column() function to create useful formatting techniques in Excel.

Now lets look at the function referenced above in the context of Conditional formatting. Here is the setup for this example. (IMG)

=(INDIRECT(ADDRESS(ROW(),1))>3) * (INDIRECT(ADDRESS(ROW(),1))

When conditional formatting evaluates each cell, the formula will read from left to right, innermost parenthesis to outermost parenthesis.

Checking the formatting for cell A1 first:

1. Row() will be evaluated. This will grab the row of the current cell being checked for conditional formatting.
2. Address(Row(),1) will then be evaluated. For A1 and B1, this will give us “A1″.
3. Indirect(Address()) will be evaluated. This will give us the value of 1 for A1 and B1.
4. Result of Indirect() will be compared to 3. This will give us FALSE for A1 and B1.
5. Repeat steps 1-3 for the right side of the multiplication sign.
6. Result of Indirect() will be compared to 10. This will give us FALSE for A1 and B1.
7. FALSE*FALSE = FALSE (no formatting is applied to Cell A1). [read up on the "AND" function in conditional formatting above]
8. Steps 1-7 will be evaluated individually for all cells each row.

The overall result of this is that you can use Excel conditional formatting based on another cell value to format a cell. You could adapt this to organize by column, or much much more; use your imagination.

As conditional formatting is extremely in depth, I will periodically update this section.

More to Come Soon…