Modify an Excel template for your own specific sales needs, and add your company information, logo, and custom charts.
- [Instructor] No template is going to be perfect for you right off the bat, but you can always make modifications once you've found a template that will work for you. For this video I'm using the 01_02 modified template workbook in the chapter one exercise files. I've already typed in our sample company name and added a logo, but I don't like this table color, so I'm gonna go to the table tools menu, click on design, and then in the table styles, I'm gonna browse around until I find what I like. You'll notice when I hover over each one of these, live preview gives you an example of what your data will look like, so you don't have to commit to anything one by one, you just hover over each one, find the one that you like.
I'm gonna take this one, and when you click on it, that format will be applied. The next thing you can do on a broad scale in terms of changing Excel's overall appearance is changing its theme so go to page layout themes. I generally stick to the office theme because I'm fairly conservative, but you can choose anyone you want, and again, as you hover over each one of these, live preview is gonna show you what you'll get when you commit to it. For instance, this one might be a little bit too bright for me. The next thing we'll look at is styles. Notice that there's a lot of steps that we do that's pre-built for you.
Styles are pre-built cell formats that you apply to one cell, a hundred cells, a thousand cells. If you need to change that style, you'll only change it in one place right here, and it will change everywhere within the workbook that that style's applied. For instance, I don't like this particular style. It doesn't match my table now, so I'm gonna choose this title, and then for row three, I think I'm gonna change that to the heading two style, and then row four, I'm gonna select the entire row.
I want an underline style, and again you can come in and change these anytime that you want. Just go to cell styles, and right-click and choose modify, and you have format options just like you do with format cells. For this particular style you probably wanna focus on font, like do you want it bold italic? Then once you commit to it, that style will be changed everywhere it exists in the entire workbook. Now I'm gonna control z to go back to where I was.
Now looking at the template itself, this is a great example of what I can do with this information, but it's not my information. It's somebody else's. Before I distribute this to my folks, I wanna get rid of that information, but before I do that, I'm gonna take advantage of the fact that there is information in here for me to play with, so I'm gonna add a couple of charts that might be helpful for my sales reps once they enter their information, something that gives them a visual clue as to how they're performing with their pipeline objectives. So I'm gonna select this first column for opportunity name. I'm gonna hold the control key and select the forecast amount column, then I'm gonna hit alt f one to create an instant chart.
Next thing I wanna do is go into the ribbon, click the change chart type button, and for this one I'm gonna choose a tree map chart. It's very useful. This one's not yet. First thing I need to do is give it some depth and some meaning, so I wanna add in some labels for this, and this is pretty crowded, so I'm gonna go to the quick layout tab, and I'm gonna turn off that legend. Let's increase this a little bit, give it a title. I'm gonna call this pipeline. So the tree map chart gives representations for all of your data points and grids, and the larger the grid that means the larger numerical value that opportunity represents.
So if we look over here, A Datum Corporation, that's probably our biggest opportunity in the pipeline. If I change that down to a smaller value, you'll see that the entire chart redraws itself, and it will do that, so this chart is entirely dynamic, and it also is dynamic based upon the slicers you have in your table. So for instance, I'm gonna choose contract and contact, and you'll see that my table has automatically redrawn itself based on the slicers that I've chosen, so I'm gonna turn that off.
Now this isn't gonna be very helpful for my sales reps when we first start entering information because there won't be anything, so the chart will be blank, so I'm gonna use control x to cut this chart. I'm gonna paste it onto a blank worksheet where I can come back and use it later once I do have information yet. Something else I'm gonna do is use control c to copy this chart, and paste it over here, and I'm gonna change this chart and make it a different sales chart. I'm gonna make this a sunburst chart. The only reason I'm doing this is some people like to see things differently, so now I have two different options to show people for when they're ready, so I'm gonna double-click on this sheet name and rename it charts, and then I don't want this to be distracting for my sales reps to see this and wonder what's going on.
I'm gonna right-click and choose hide. Now they don't know that that chart sheet was there, so there's no reason for them to be distracted. When they do enter their pipeline information in, and you wanna give them a graphical view of what's going on, right-click any worksheet tab, click unhide, and you'll see your sheet name listed here. Just select it, click okay, and there it is for ya, but I'm just gonna go ahead and hide it again. The next thing I'm gonna do is go ahead and delete this information. Notice that I only selected rows b through k.
The gray columns you see in l and m, those are formulas. I don't wanna delete those. Now I'm just gonna type in a couple of opportunites, just so people can see what's going on. Lead source was web, sales phase is closed. So this is great. Now I've got some data in here. One thing you might notice in here, I've got a typo, I'm missing a cap, lead source.
I was allowed to enter anything. That's all well and good. Being able to freeform type is great, but you lose the ability to ensure that your people, and put the right information the way that you want it. So next we're gonna go over a method called data validation, which allows you to ensure that your sales people only enter in the information that you want.
- 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