Excel Tips

In case you need an intro course before getting into the tricks, here are two videos that can get you started: Excel for Beginners and Basics for Beginners. The second video goes a bit faster and covers a lot more in a much shorter period of time.

Scroll through all six tricks below or click the links to zoom straight to any one of them.

If you just can't get enough, we've included a 7th bonus tip showing how to use Pivot Tables.

Sorting

Sorting allows you to order your details so it is easier to analyze information. Below you will find both a video and written directions.

The example below uses the headers from our storm exports. To sort the worksheet so all work orders are grouped by service, in chronological order within each property, do the following.

Select the data you want to sort (if sorting entire spreadsheet, click the square to the left of the “A” column).
Go to Data - Sort
Check “My list has headers” (at top of box).
Choose the column to sort by first, such as Property.
Add a level (+ button) and choose the next column to sort, such as Service Type.
Add more levels, choose options like Clock-in Date and then Clock-in Time.
Click Ok.
If you see a pop-up question, choose “Sort anything that looks like a number, as a number” and click Ok.

Subtotals

After you sort your data, subtotaling allows you to quickly see the totals for those details.

Once again using the headers from our storm report, you can see the total revenue by property and by service type.

Select the data you want to sort (if sorting entire spreadsheet, click the square to the left of the “A” column).
Go to Data - Subtotals
Choose Property in “At each change in”
Pick Sum for the function.
Scroll to Billable Rate in “Add subtotal to”
Uncheck “Replace current subtotals”
Click Ok.
Repeat steps but choose Service Type for “At each change in”.
Once done, you can hide details to make it even easier to see. Go to our website for more

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 worksheet into separate pages for printing.

This next video is only five minutes, but the last two minutes are probably the most important.

The four minute video below covers some of the same information as the one above. However, it does cover 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 scale the sheet to fit to one page wide by however tall it needs to be, simply by deleting the number next to tall. See the screen cap below for reference.

Visible Cells

Learning how to select only the cells that are not hidden helps in a couple different ways. We use it most often if we want to only copy/paste the subtotals that we have created. However, if we want to keep the individual rows (i.e. the work orders in our systems), we also use it to change the cell color or font size of the subtotal lines quickly so it is easier to see.

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

This trick isn't used too much in our exports, but it is great to know. If you ever have a full name (first and last) in one cell that you want to separate into two cells, a first name and a last name cell, this is how to do it. It also helps if you have ever downloaded information from a website and had all the info show up only in the first column (A). This trick helps you separate all those details into appropriate columns quickly.

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.

As an example, we might use it to quickly see which subcontractors haven't been paid yet. If we enter the date the check was paid when it's paid, and the cells stay blank until they are paid, we can set up conditional formatting to highlight all empty cells. Then, as we enter the check date, the cell color automatically changes colors.
For quick reference, follow the screen caps and instructions below.

Highlight the cells you want to change
Find the Conditional Formatting on the Home Ribbon
- Click the Highlight Cells Rules
- Then the More Rules
In the dialogue box that pops up, choose Blanks and click ok

You will now be able to quickly see which cells are still blank.

Pivot Tables

This is a bonus trick that kept showing up in search results as I was trying to find the best videos for this page... so I thought I would share. Pivot Tables are ways to pull small details and/or totals out of a large data set. I learned a lot by watching the video below, and it made me want to start trying it out on our storm exports. That's the great and horrible thing about Excel.... It seems there are almost infinite ways to make life easier and info "prettier". You just need to find a good YouTube video to teach you!

Want More Tricks?

That's all for now! I hope you found this page helpful. If you did, please send us a quick email at [email protected] to let us know which trick you liked best. Or, let us know if you have any questions about them or have another trick to add to this page!