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

Benefits of Excel Training:

Is Excel Training Right for You?

In business, we have come to rely on Excel to manage data for our employees, clients and payroll. It is an incredibly useful tool that if used to its full extent, can save hours of work for anyone. In Excel, you can generate invoices, monitor cash flow, forecast financial scenarios, maintain balance sheets and income statements, the list goes on and on.
If you have finally decided it is time to start getting serious about improving your Microsoft Excel Skills, consider some formal Excel training.

Excel Training can be a great way to help boost your productivity at the office. There are many resources available for free and for sale online, including manuals, websites, online and cd based courses.

Most people don’t think they would benefit from Excel training. as such, they put on their resume bold statements like “Excel Professional” or “Proficient at Excel,” when in reality they know nothing about pivot tables, VBA, Print Views, or much else beyond basic functions and formats. If someone tells you that the Excel ruler is grayed out, are you going to be able to help them? What about if someone asks you how to fix their macro? These are things you could learn if you simply set aside the time.

Everyone can benefit from Excel Training

When you are looking for MS Excel training, you need to consider what is truly important for yours or your company’s needs. There are options available for individual study or for groups. Both on site and off site. Off-site classroom training would be the type of training scenario most businesses envision.
Some people find training online to be highly effective, while others feel that it is hard to make this a priority on their own time.
Excel training can increase confidence, allow employees to maximize use of its features, and teach employees how to create and use formulas.

The reality is that everyone could use a little bit of training. This goes with anything in life. Make time for it and you will see the results pay off in your office life as well as your personal finances.

Many things have changed over the years with Excel. Excel training will not only provide you with new ways to do new things, but also new ways to do old things. Don’t find yourself stagnant in old habits just because they are comfortable. Many people find that they grow to enjoy the new environments as much as the old, if not better.
Though Microsoft Excel is designed to be usable by both novices and advanced programmers, don’t let yourself become comfortable at a certain level. Training courses can be designed to fit the needs of each individual, and paced accordingly.

On the home page, I offer many tips and tricks for navigating through some of the less common areas of Microsoft Excel. This should give you the necessary edge over your coworkers to finally get that raise.

Formatting Excel Home

You probably already knew that you can align text vertically and horizontally, but did you know that you can also change the orientation of text within a cell? Keep a lookout for the -> button on the home tab, or look under “Alignment” in the “Format Cells” dialog box.

Notice how the border and fill area changes to match the alignment of the adjoining cell.

Text Wrapping causes text that would fall outside the left or right boundaries of the cell to be continued on subsequent lines within the cell.

VBA to Format Cell Alignment
Selection.Orientation = int
where int [integer] is the degrees of rotation [i.e. 45]

Selection.WrapText = bool
Where bool [boolean] is True or False

Basics

To change the column width, hover your mouse over the space between two column headings. The mouse should change shape into a line with an arrow on each side. Click and then drag to expand or contract the row or column size. If you accidentally shrink the column to 0 width and let go, don’t worry. Just select the columns to either side (by clicking and dragging from the center of one column heading to the other) and right click the selected columns. Then click “Unhide”. Another way to change the Column Width in Excel is to right click the column heading. In the right click menu, select the “Column Width” Option. Here you can enter a number for the exact column width.

VBA Formatting Examples for Column Width and Row Height

Columns(“B:B”).ColumnWidth = 14.57
(where 14.57) is the desired width
or
Columns(2).ColumnWidth = 14.57
or
Rows(“2:3″).RowHeight = 12.14
or
Rows(2).RowHeight = 12.14

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 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 for a Gradient

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

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…

Views

Now lets talk about views. Excel has some interesting features that can help you see your data more clearly, and become much more efficient. In Excel 2010, they can be conveniently accessed via the view tab.

Workbook Views

Page Layout

This view is not one of my favorite to work in, however it is great for making final adjustments to an Excel spreadsheet before you print it. Combine this with removing gridlines (assuming you are not telling Excel to specifically print the gridlines) and you have a good idea of exactly what is going to be printed. This is a good view to adjust the column and row sizings, and also makes it easy to create a header or footer. If you are wondering why the ruler check-box is grayed out, you can access the ruler in Excel by witching to this view.

Page Break Preview

This is a very useful view, as it allows you to manually adjust the print page boundaries to include or exclude columns and rows on a specific page, or even from being printed altogether. To do this, you can click and drag either the hyphenated blue lines within the print area, or the solid blue lines on the outside borders. Don’t be shy; you don’t have to include the entire document in printing. And now you know that hiding rows and columns is not the only way to do this. You can have your cake and eat it too. Although I cannot think of a specific need for this, I should go ahead and remind you that you can change the print area to include a blank page, if you wanted one.

You have access to these three views at the lower right hand corner of the Excel Workbook near the zoom slider.

Custom Views

Find a view and zoom settings that you really like? or need someone to be able to go to a specific view with specific print settings? Add custom views to your spreadsheet to give your spreadsheet users the ability to see your data in a specific view.

Show/Hide

Removing gridlines in Excel can be a great way to view your formatted data more clearly, as well as see how your spreadsheet would look if printed out. Simply click the gridlines checkbox on the view tab to toggle on and off this feature.

Here you also have the ability to hide the formula bar or cell headings from view.

Zooming in Excel

There are many ways to zoom in Excel. First and most readily accessible is the zoom slider in the lower right corner of the screen. I am going to look into whether or not this can be hidden, as off the top of my head it seems like I have done that accidentally at one point or another. If that is the case, I will be sure to update you on how to show and hide the zoom slider. If you click the percent zoom right here, it brings up the zoom menu (the same one that you can access via the view tab). That being said, you can also zoom to selection or zoom to 100% either on the zoom menu, or on the Zoom tab. Did you know that you can scroll in excel using your mouse? Most mouses with scrollbars allow you to zoom by holding down the control key while sliding the mouse wheel up or down. You can also Zoom with VBA code.

 

What is Excel VBA?

VBA stands for Visual Basic for Applications. Basically (pun intended) it is a programming language adapted specifically for Microsoft Applications. This includes Outlook, Word, Access, Excel etc. In my opinion, it is easier to learn than some of the more traditional programming languages like Java, or C++ not only because of the structure of the syntax (programming grammar), but also the fact that Excel allows you to record Macros (programming code) and view/edit the code that you produce. This creates a great learning opportunity for those who like to learn by doing, rather than from a book or classroom setting.

This portion of the site is designed to provide examples of VBA code, specifically for Excel. As you read through some of the examples, you should begin to get an idea of how interact with Excel Spreadsheets via VBA code.

You might be asking yourself, “What good is a Excel VBA? I have gotten around Microsoft Excel just fine without it for the past x years.”

Imagine that you grew up in a remote town where you had never seen a car. Everyone walks to the local store, the local bar, or to the local restaurants. There is absolutely nothing wrong with this, everyone is getting along ‘just fine’.

An outsider strolls into the local pub and begins talking to the residents of your town about cars. He preaches how you could get to the store in 5 minutes, rather than 30. Some would shun the idea, convinced that walking is better, or that they would never understand this complicated machine. Those set in their ways begin to leave the pub, but you listen, at least partially intrigued with what he has to say. As you listen, you start to realize that not only can you get places that you typically go faster, you can begin to imagine going places that walking would never allow!

The metaphor at this point should be obvious. Excel VBA is a tool that allows you to go beyond your physical limitations of speed, and manipulate spreadsheets in ways that time would not typically permit.

Excel’s VBA is great for repetitive actions. Just because an action is repetitive, does not mean that it has to be simple, or even quick. Tasks that are done every day once a day in Microsoft Excel, but take hours, could potentially be reduced to seconds! Since I regularly work in Excel, I often find myself spending 5 minutes writing a macro for a task that would take me 30 minutes, even if I never expect to do that task again. At the worst, I have saved nearly half an hour; at the best, you can imagine my surprise when I have to do the task again, but already have a tool to accomplish it in seconds.

For this reason, I recommend you keep either one spreadsheet, or multiple spreadsheets with your Excel VBA code neatly organized. You will be able to grab code from old macros, and adapt it to new situations. As you use a particular piece of code more often, your brain will start to remember what you are looking for, and eventually you will be able to recall and write the code faster than you can find it!

Excel VBA Basics

The first thing you should know about Excel VBA, is how to make sure Macros are enabled. To do this, go to the Excel File Menu and click on Excel Options. Then go to the Trust Center, followed by Trust Center Settings, and finally Macro Settings.

Here you have several choices.

Allowing all macros is fine, so long as you are not downloading spreadsheets others have created (that includes E-mail, online tutorials, even friends [malicious hidden spam]) If you are downloading others’ spreadsheets, you open yourself to the possibility of malicious code being run on your computer.

**Now would be a good time to inform you that you can set an Excel macro to run automatically: for instance upon the saving of a workbook, closing of a workbook, or even upon the opening of a workbook. We will address these later, but I wanted to point out that code can be run, even without your consent, if you do not know the source of the spreadsheet.

If you are concerned for the security of your computer, then go ahead and select Disable all macros with notification. This will allow you to see that there are macros in the sheet, and enable them if you deem them safe.

Upon opening a Macro Enabled Workbook, Microsoft Excel will prompt you:

If you want to enable Excel VBA for the session, select “Options,” and “Enable this content.”

You can leave the macro settings on “Disable with notification”, and switch to “Enable all macros” in the Trust Center Settings when you know you are opening a safe workbook. You may have to close Microsoft Excel for these settings to take effect.

Using a Macro

Now that you know how to record a macro, you need to learn how to access it. There are multiple ways to do this. The first is the developer tab. If you cannot view Excel’s Developer tab, go to the Excel File menu as seen above, and under “Popular”, make sure that the show developer tab check box is checked. This will make the Developer tab visible.

On the left, you will see the Macros button. Clicking this will bring up a list of the macros in all open workbooks (you might notice that the names of macros from other workbooks have do not look as clean as the macros within the active workbook.

This window allows you to run or edit a macro that you have created.

Accessing the Project Manager

Recording a macro, however, is more like riding a bike that driving a car; so if you really want to get some mileage out of Microsoft Excel’s VBA, you need to learn how to change or edit the code. To access the project manager (Excel’s VBA interface [aka IDE]), click “Visual Basic” on the developer tab, or hit Alt + F11.

First of all, you should see the project explorer shown in the left of the picture above. To show the project explorer, or bring it back if you accidentally close it out, it is as simple as clicking “view” then “project explorer,” or hitting Ctrl + R,

Macros are contained within modules. Each module can have more than one macro. To locate your macro, look for your project file in the project explorer. It will appear as “VBAProject(Filename)”

Expand this list. If there is not a subfolder named modules, then you do not have any macros in this project file. To create a new macro from scratch, right click your project file and select “insert module.”

This will bring up a blank space to create your code.
Every macro starts and ends the same way.

Sub Macro_Title()

End Sub

The actions your macro performs are located within these two key lines. The macro title cannot have spaces, cannot be a number, and cannot be a reserved keyword nor share a name with another macro in the same Excel project.

Comments

In the previous image, you may have notice the green text in my “Macro1″. This is a comment. Comments are extremely useful for programmers. They allow a programmer to make notes that make reading the code much easier or help remind what a particular section of code does. They also assist any third party who is reading your code to understand what function a particular piece of code serves.

To create a comment, begin any line with an apostrophe (‘), A.K.A a single quotation. If a particular piece of code is not working, you can comment it out to let the code run without executing the code. Later, when you have fixed or completed that section, you can remove the comments to allow the code to run. Two extremely useful tool are the comment block or uncomment block buttons. To add them to your toolbar, click any down arrow on the toolbar, go to add or remove buttons, then edit, and finally select the comment block and uncomment block tools. This allows you to change several lines of written text, or vice versa.

For now, start playing around with recording macros and editing the code that they create. I will continue to add content.

Alignment VBA

 

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 45

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Some Options

.HorizontalAlignment = (xlLeft, xlCenter, xlRight)

.VerticalAlignment = (xlTop, xlCenter, xlBottom)

.Orientation = (Any number of Degrees)

.IndentLevel = (Any Integer)

.ReadingOrder = (xlContext, xlLTR, xlRTL)

Font VBA

With Selection.Font

.Name = “Times New Roman”

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

Borders VBA

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

InteriorVBA

With Selection.Interior

.Pattern = xlPatternChecker

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.149998474074526

.PatternTintAndShade = 0

End With

Interior Gradient

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
(xlPatternAutomatic, xlPatternChecker, xlPatternCrissCross, xlPatternDown, xlPatternGray16, xlPatternGray25, xlPatternGray50, xlPatternGray75, xlPatternGray8, xlPatternGrid, xlPatternHorizontal, xlPatternLightDown, xlPatternLightHorizontal, xlPatternLightUp, xlPatternLightVertical, xlPatternNone, xlPatternSemiGray75, xlPatternSolid, xlPatternUp, xlPatternVertical)

Number Formats VBA

Selection.NumberFormat = “0.00%”

Selection.NumberFormat = “$#,##0_);[Red]($#,##0)”