Excel Tips

New to Excel? Start here. These two videos cover the fundamentals before you dig in the the tricks below:

Excel for Beginners - a thorough walk through for beginners
Basics for Beginners - covers more ground in less time

Note: The steps below are demonstrated on a Mac. Your specific version and platform may vary slightly. Use Excel's Help feature to find the equivalent on your version.

Jump to any section on this page:

If you want to go deeper, there's a bonus section on Pivot Tables at the bottom.

Practice File

Download this practice file to follow along. It's a sample of some of the info available in an SGA Storm Export.

excel_training.xlsx
File Size:	26 kb
File Type:	xlsx

Download File

Sorting

Sorting puts your data in the right order so patterns and problems are easy to spot. Watch the video or follow the written steps below.

The example below uses column headers from our storm exports. To group work orders by property and sort them chronologically by service, do the following..

Select your data. To sort the entire sheet, click the small square to the left of column A.
Go to Data > Sort
Check “My list has headers” (at top of box).
Choose your first sort column (e.g., Property)
Click the + button to add a level, then choose a second column (e.g., Service Type).
Add more levels as needed, such as Clock-in Date, then Clock-in Time.
Click OK.
If prompted, choose "Sort anything that looks like a number, as a number" and click OK.

Subtotals

Once your data is sorted, subtotaling lets you see group totals at a glance. Run it after sorting so Excel knows where each group starts and ends.

Using those same storm report headers, here's how to see total revenue broken out by property and service type.

Select your data (or click the square to the left of column A to select the whole sheet).
Go to Data > Subtotals
Under "At each change in," choose Property.
Pick Sum for the function.
Under "Add subtotal to," select Billable Rate.
Uncheck “Replace current subtotals”
Click Ok.
Repeat the process, this time selecting Service Type under "At each change in."
Once subtotals are added, use the collapse controls on the left (the numbers that order left to right) to hide row detail and see just the summary numbers.

Page Setup

Inside the Print and Page Setup areas, you can make your Excel doc printable and easy to read. Important details like repeating rows, showing grids, and page numbers are just a few ways to make it look nice. There is also a Page Break Preview that lets you make changes as well. There are four videos below that outline these ideas.

This first four minute video shows how to break a large worksheet into separate pages for printing.

This next video is only five minutes, but the last two minutes are printing essentials.

The four minute video below covers some of the same information as the one above. However, it does cover other details such as printing grid lines and centering data on the page. He also shows a couple different ways to get to the same information that is shown in the video above.

Here's a quick video of how to add Page Numbers in the Footer.

Lastly, here's one bit of info not mentioned in the videos above.

Inside Page Setup, you can force the sheet to fit to one page wide by however tall it needs to be, simply by deleting the number next to tall and leaving it blank. See the screen cap below for reference.

Visible Cells

Learning how to select only the cells that are not hidden (whether from subtotals or just hidden columns or rows) saves some real time if you want to be able to sort only the subtotaled information or quickly change the formatting of the visible cells.

For quick reference, if you don't want to watch the whole video, follow the screen caps and instructions below.

Highlight the cells you want to change
Find the Editing button on the Home Ribbon
- Click the Editing button
- Then the magnifying glass
- Then the Go to Special Button
In the dialogue box that pops up, choose Visible cells only and click ok

You will now be able to click copy or make changes to the cells and words that are highlighted.

Text to Columns

Text to Columns doesn't come up often with SGA exports, but it's worth knowing. Use it anytime data is crammed into one column that should be split across several, like a first and last name combined in a single cell, or downloaded data where everything lands in column A. A few clicks and it distributes neatly into separate columns.

Conditional Formatting

This is another trick that we don't use too much in exports, but can be very helpful in other Excel files. With this trick, you can easily spot trends and patterns in your data using bars, colors, and icons to visually highlight important values.

This video does a great job of explaining all the types of conditional formatting, but if you don't have 20 minutes to watch the entire thing, after watching the initial overview, skip to 7:39 for the ones used most often.

A practical example: tracking subcontractor payments. Leave the cell blank until a check is issued, then set conditional formatting to highlight blank cells in red. Once you enter the payment date, the highlight disappears automatically. At a glance, you know exactly who hasn't been paid.

Highlight the cells you want to format.
On the Home ribbon, click Conditional Formatting > Highlight Cells Rules > More Rules
In the dialog box, select "Blanks" and click OK.

Blank cells will now be highlighted, and the formatting will clear automatically when a value is entered.

Pivot Tables

Pivot Tables are one of Excel's most powerful features for summarizing large data sets fast. Rather than sorting and subtotaling manually, a Pivot Table lets you drag and drop fields to instantly see totals, averages, and breakdowns any way you want. They're worth learning for storm export analysis in particular.

The video below is the best introduction we found. If you're short on time, skip ahead to around 7:39 for the most practical applications.

Want More Tricks?

Got a trick we missed? We'd love to add it. Send us a note [email protected] and let us know what's most useful, or what you'd like to see covered next.