Formatting Excel Home

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.

In order to follow any updates to this page and help to grow our community, go to the Formatting Excel Google+ page here: Formatting Excel on Google+

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. Formatting Underline Styles

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!!!Formatting Within a Cell 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