Most folks in the cost-estimating field are familiar with Microsoft Excel, but even if you haven’t crossed paths with the spreadsheet tool professionally, you may have used it to graph science fair results in school, to keep track of your monthly budget, or to organize your contacts. This data-collecting, function-utilizing, visual-developing tool does it all!

You probably can recall some of the basic shortcuts that you have learned early on in your relationship with Excel (CRTL+C, anyone?), but we’re going to expand your knowledge bank to make this user-friendly tool even friendlier. Here are 5 Excel-lent tips to make your next spreadsheet endeavor faster & easier:

1. Too Many Sheets, Not Enough Space!

Do you ever feel like the sheets in your Excel file are out of control? It can easily become overwhelming. Thankfully, a simple solution can keep two sheets open at the same time -

1. Go to the View ribbon
2. Click “New Window”

This will open a duplicate of the current file and allow you work on two sheets at once!

2. Alt: The No-Mouse Shortcut

Have you ever wished you could keep your hands on the keyboard without having to jump back and forth to the mouse to work on your spreadsheet?

Odds are you have actually encountered this shortcut in the past, but quickly hit ”Esc” in fear that you’ve broken something.

Hit “Alt” when viewing your spreadsheet and grey letters will pop up at the top of your sheet. Yes, those grey letters may have led to concern in the past, but click the letters on your keyboard that correspond the ones hovering over a ribbon or action and you’ll be able to move about the spreadsheet mouse-free!

3. Trace Dependents / Trace Precedents

Getting a conceptual feel for how data and formulas flow through a file can take a lot of work. Luckily, Trace Precedents / Trace Dependents can help with that.

1. Click on any cell with a formula in it.
2. Go to the Formulas ribbon.
3. Click "Trace Precedents"

   1. Blue arrows will point from the cells that feed into that cell

      (Keep clicking to go back even further in the formulas path)

4. Click "Trace Dependents"

   1. Blue arrows will pop up and point to cells that the selected cell feeds into

Hint: Select “Remove Arrows” just under “Trace Dependents” to hide the arrows.

Trace Precedents	Trace Dependents

4. Dynamic Ranges

Tables allow you to reference a column by name and automatically expand/contract based on the data. But, what do you do if using a table isn’t an option?

The INDEX(array, row_num, [column_num]) function does far more than just return a value – it can actually return a range as well!

• INDEX(array, 1, ) returns the entire first row (notice the second comma)
• INDEX(array, , 1) returns the entire first column
• INDEX(array, 1, 2):INDEX(array, 3, 2) returns from row one to row three of the second column ({Cat; Dog; Fox})

Need more flexibility? Use COUNTA() or COUNTIF() to figure out how many rows you need or MATCH() to locate a column by name. Even create a named range with your new formula for updating data validation filters!

5. Number Formatting

Occasionally, you’ll find that a cell will display a value in the wrong format. As much as everyone loves $100, sometimes the value you’re looking for is 100%. You could go to “Format Cells”, but that can be tedious.

For a quick fix, use CTRL+Shift+[A Number Key]. While it may take a second to remember what each number key changes the cell format to, Excel helps you build your memory by assigning formats to similar keys. For instance, change your cell to the currency format with CTRL+Shift+4[$].

Here is the full list:

Note: As an added bonus, this shortcut allows you to create and remove borders around the cell!

Happy spreadsheet-ing and stay tuned for more tips!