The PROs Guide to Mastering Microsoft Excel – Part II

In Part I of this guide, we walked you through the basics of using Microsoft Excel, gave you an introduction to some of its advanced functionality, and provided a number of free downloadable templates to help you along the way. If you’re still catching up, hop back to that post to get started. If you already have the basics down, Part II of our PROs Guide to Microsoft Excel is going to dive into how you can use the program to help your business. We’ll cover creating estimates, managing your job schedule, and even creating a floor plan.

Dive In

Click the buttons below to quickly scroll through this article, or keep reading for more information on how you can use a few advanced functions in Excel to improve your small business.

Advanced Functions for PROs

There are a lot of things Excel can do to streamline your workflow. Before diving into how Excel can help your business, however, let’s first define and review a few advanced functions you’ll want to know and use regularly.

Pivot Tables

Pivot tables take a ton of data and make it quick and easy to analyze and understand. Let’s say you use Excel for managing your customer information and job records. Start by clicking the Insert tab across the top of your workbook, then select the pivot table icon from the left-hand corner. From there, highlight the data you’d like to analyze. The pivot table should open in a new sheet for a clean set of data. Next, add fields for rows and columns for values. Referring back to our example, use your customer or job name for the rows, and drag and drop your costs (products, labor, etc…) in the values box. On each value, select the arrow dropdown to change what you’d like the table to show. Select from sum, average, and more. Quickly find what you’re looking for without manually searching through hundreds of cells. For more on using pivot tables, and a video walk through on how to set them up, click here.

If you’re not quite ready to tackle pivot tables, try using the sort function to help cut through some of the noise in your sheet. Select the column or row of data you’d like to filter. Then find the Sort&Filter (AZ) button within the ribbon. Depending on your version of Excel it may also be next to a funnel icon. Select how you’d like to sort the data or click the funnel icon to create filters within each column. To learn more about sorting, click here.



With VLOOKUPs, you’ve now officially entered the “Advanced User Zone.” They allow you to cross-reference two sets of data and combine the information from both sets into a third set. Here’s how it works. Let’s say you have one Excel table containing a list of all the suppliers your company works with. This table contains the supplier’s name and the types of products they offer. In a second table, you have a list of all the suppliers in your state, and the distance from their warehouse to your job site. By using a VLOOKUP, you can consolidate these two sets of information into one table that shows the suppliers you work with, the products they offer, and the distance from their warehouse to your job site.

To help illustrate, let’s take a look at the two tables below. Table 1 (on the left) contains a column for “Name” and another for  “Date of Birth.” Table 2 (on the right) contains “Name” and “Favorite Flower” columns. Since both tables contain “Name” as a column, this is known as the unique identifier or key. The VLOOKUP formula uses this key to match values together from both tables. In the example, you can see table 3 (at the bottom) is the result of matching in the “Favorite Flowers” column from table 2, into table 1 using “Name” as the unique identifier.

Now, here’s how you make it work. Using the formula bar, the basic formula for a VLOOKUP is “=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup]).” In plain English this can be written as: “=VLOOKUP(the value you want to look up, the range you want excel to look in, the column number in that range that contains the value you want to grab, whether you want an exact or approximate match)”. It is worth noting that the final option for this formula ([Range_lookup]) is optional. Putting in a zero will tell Excel to look for an exact match, while using a 1 will tell Excel to just look for something similar. Leaving that field blank will cause Excel to default to a value of 1 and look for an approximate match.

For our example, the formula would look something like “=VLOOKUP(Table1!A2,Table2$A$1:$B$9,2,0). By placing this formula in cell C2 and dragging it down the length of table 1, you will have a table that contains a name, date of birth, and favorite flower for each row.


Making Graphs and Charts

As a PRO, chances are you’re a visual learner. Staring at white boxes with a bunch of numbers probably isn’t the best way to display your data, especially if you’re sharing that data with your crew or with customers. In the grand, complicated scale of using Excel, creating graphs is super easy and also helpful when measuring things like sales numbers, profits, and overall business outputs.

To create a graph, simply click and drag over the data you’d like to display and click the Insert tab. Then in the charts section (found in the middle of the ribbon) click on the graph of your choice. The graph or chart will populate instantly. From there you can customize to your liking. Once you have a set number of metrics you’d like to visualize, you can use graphs and charts to create a dashboard of meaningful metrics to share with your team. We’ll revisit using charts later in this article to show you how to quickly create a detailed job schedule.

Understanding how to create and use pivot tables and VLOOKUPS to quickly analyze large sets of data is a great skill for any PRO. Once you add in the ability to turn that data into digestible and easy-to-follow graphs, you can make an immediate impact on your business. To further this exploration in advanced formulas and Excel functions, check out this Lifehack article we found that sheds some light on a few tips and tricks.

Keep reading. We’re about to get into the good stuff.


Excel for Your Small Business

Now that you’re all set up with the basic understanding of how Excel works and you have a good overview of some advanced features, let’s dive into how you can use Excel for your business. There are a lot of ways PROs can use Excel (more ways than we have time to cover), but in this section we’re going to focus on creating estimates, scheduling jobs, and creating floor plans. We’ll start with creating estimates.

Create Estimates

We previously posted an article titled 17 Essential Tools for Your Small Business that includes several services PROs can use to create estimates. If you’re on a budget or if you just like using Excel (now that you know how to), let’s go over how to create a simple estimate you can use to determine job costs and share with your customers.

Start with a new sheet, using your customer or job name as the sheet name. We’ll create two tables, one for materials and one for labor. In row 2, add Materials, Price, Quantity, Total, a blank column, Task, Number of Hours, Price and Total. Adjust column width accordingly, using more space for Materials and Task. If you have a list of items from a previous job already on hand, use those as an example to fill in the cells. Or if you’re a plumber and regularly use the same products (fittings, glue, etc…) add those in so you can quickly get through creating your first estimate. To add your totals, use the formula bar to add the Price and Quantity cells in the Total column.

A cool trick is to use the auto sum formula in the first Total column “=SUM(B3*C3).” Then hover over the corner of the cell until you see a “+” symbol. Click and drag down in the total column, and the formula will be applied so you don’t have to individually change each cell.

Watch the 15-minute video below for how to create an estimate in Excel and download our template that we created, following the steps in the video. It’s pretty basic, but you can customize and edit it to better fit your brand.

Keep an Organized Job Schedule

Keeping your jobs and your crew’s schedule in order is a key function for any PRO. Knowing how long a job will take and how to line the next one up can make or break a small business. With the ability to create a gantt chart (a bar chart using horizontal lines to display progress or production completed over a period of planned or estimated time) in Excel, taking data from your workbook and making a schedule can be easily set up with just a few steps.

Since you’ve already created an estimate, this process is even easier because you already have the “tasks” aka “work that needs to be done” created for job completion. To get started, create a new sheet in Excel or download this free template created by the folks at TeamGantt. Enter in your tasks, start date, end date, and duration of each task. Once your data has been entered, click the Insert tab and select the 2-D Stacked Bar Chart from the center of the ribbon area. If you’re unsure of what kind of bar chart you’re on, hover over the image and wait until the name is displayed.

From here there are a few more steps to follow. For a clean, fresh schedule, you can quickly edit the template provided or follow along with the 8-minute tutorial below. Try starting out by editing the template, since all the formulas have already been created. You’ll be able to add your upcoming job schedule easily without worrying about any kind of set-up hassles.

[sc name=”PROtip” text1=”” link=”http://learn.supply.com/wp-content/uploads/how-to-use-excel.pdf” linkphrase=”Download this free template” text2=” from Hubspot to schedule and organize all of your social media marketing efforts.”]

Make a Floor Plan

Now let’s get a little more creative with our cells and rows of data. With a little vision and some elbow grease, you can turn your spreadsheet into a professional looking, fully customizable floor plan. Once you create one, it’ll be easy to copy and paste for future jobs.

To get started, sketch out your floor plan on a piece of paper and get a feel for the overall layout of the room. Next, make sure you’ve got all of your measurements. Measure all your tables, chairs, and anything that will be in or is currently inside the room. Don’t worry about the heights, just the length and width of the furniture or things that’ll take up space.

Now it’s time to open Excel and get your spreadsheet set up. The folks over at renovatedlearning.com recommend creating a grid of squares to each represent one square foot, and to create starting and ending boundaries within the space. They have laid out a detailed floor plan of a library (download it here) and you can follow along with the video below on getting your floor plan set up and creating your first room objects and pieces of furniture.

There’s more!

If you’re having fun using Excel and would like to keep creating tools to help your business, check out these 20 templates for construction management (that can be edited and used by any PRO). You’ll find free, downloadable templates for budgeting, creating more job timelines and more estimate templates, inspection reports, creating invoices, wage and payroll forms, and several other handy sheets to streamline and organize your business.

To recap, in this article we’ve walked you through creating estimates and a job schedule, and provided a number of free downloadable templates to help you along the way. As we mentioned in Part I, continue your Excel mastery with examineexcel.com, your go-to source for learning Excel.

Let us know how you’re using Excel for your small business and any tips or tricks you can share with other PROs.