Use the Excel free, pre-built templates for inspiration.
- [Instructor] One of the most frustrating things in Excel is having an idea of what you need but no idea how to do it like what I'm looking at right here, it's a blank slate, I don't know where to start. Fortunately there's a great place to start. You can go to file and then click new and you'll see a list of templates that will show up. Right off the bat you just see some default templates that are kind of out there for everybody. You can also search by specific function area, so in this case as this is a sales course, I'm gonna search for sales. Give Excel some time to crunch through it and you'll see right now there are currently over 200 sales focused specific templates for you.
I'm just gonna run through a few of them to show you how you can preview them and create them if you want and then a little bit later on we'll walk through one in detail. Here's a monthly sales report that you could plug in some month end numbers to put into a PowerPoint presentation or share at month end with your team. You can plug in these numbers on your own or you could link to existing templates and reports that you have. Here's another one which is a campaign sales file. Again, you could plug in your own numbers, although I generally I encourage people to try to get numbers from other places if they exist so link to another report and just use this for a display at month end like in a PowerPoint presentation.
The report I'm looking at to be a little bit more specific on is the detailed sales pipeline management report, so I'm gonna go ahead and create that. You'll see a lot of information in here like opportunity name, customer contact, sales region, sales probability, probability forecast. Some of these fields may be relevant to you, others may not. So you can go ahead and add columns as you need them, you can delete columns, you can type over these and rename them whatever you want. All a template is, is a place to start from and you can go in and customize anything that you want from there.
First thing I'm gonna do is change my company name. If you have a company logo, you could click here and copy and paste your company logo in from a catalog of sorts if you have one or just leave it blank. Take a look over here on the right, there have been some slicers added that are attached to the table. Slicers are a quick and easy way to filter your data so you don't have to go and worry about trying to sort and mix and match and look for things. You might want to zoom out a little bit and so I'm gonna select on this particular customer contact and I can see just this information or you could click on region and any one of these slicer opportunities give you just a different way to look at your information.
On the next worksheet, we have what's called a pivot table. A pivot table is a summary of your existing data, so it rolls up all of that pipeline information into a detailed report and here you'll see some things called timelines. These are a way to, again, filter your data without you having to do any work. So let's say I want to see what my opportunities are in June. Click on June, I have two target opportunities coming up and then you just click off on the timeline. And then the last one is pipeline totals. This is just a forecast report of what's going on.
So again, not a whole lot of tweaking to be able to make this work the way that you want, in this case. In some cases you may have to do a lot of tweaking but it's a lot better off to start with a nice template that gives you an idea of what you need to do as oppose to you trying to recreate the wheel. I encourage you to spend some time looking through those templates because they can be, especially for me, a great sense of inspiration and given me a great start to building my own reports with these as a basis for those, so have fun.
- Using, modifying, and sharing templates
- Importing data from a database
- Cleaning up data
- Refreshing queries
- Pasting data
- Sorting and filtering tables
- Using slicers
- Adding a dynamic commission calculation
- Using conditional formatting
- Working with PivotTables and PivotCharts
- Creating a leaderboard
- Create period, monthly, and yearly summaries
- Creating dashboards