Creating a Sales Pipeline Spreadsheet

To create a spreadsheet for the sales pipeline

To create a better Excel sales pipeline or sales graph Find out how to create an exact sales pipeline in Excel. I have shown you in earlier articles how to create a sales graph using an Excel pie graph. Here you can view the former Excel Sales Pipeline blogs: But there are many issues with deforming the real image of the sales pipeline with this kind of graph.

Note, for example, that all stages of this Excel plot look as if the trial is smaller than the prospection, but all stages have the same value, so the plot should be a slit, not an upside-down Pyramid. See how you can see discreet changes from one sales step to the next in the new Better Sales funnel Excel graph.

They could not have seen the differences so clearly in the diagram above. Let me now show you how you can simply build this diagram using the step-by-step and footage below. Normally your sales information looks like this (with a category and a value): For our Excel Sales Judge Diagram to be displayed and to look like a Judge or Pipeline, we need to include another set of dates that puts the sales information in the right place on the diagram.

For this we have to insert a colum for the new datarow between our originals. Right-click with the mouse on colum B2 and click on Insert. It adds a gap between columns A and A in the spreadsheet. After we have set up our Excel Sales Funnel Chart files, let's make the chart.

Select the range A1:C6, then go to your Insert Ribbon and select the stacked bar graph from the graph group: Now your diagram should look like this: When you look at the right (red) sales streak in the above Sales Pipeline Charts, it looks like a reverse pipeline.

Now your diagram should look like this: As you may have noted, there is more blank on the right side of the diagram than we need. The Excel always add more room than is needed for the axes containing them. Click with the right mouse button on the horizontal alignment and select "Format alignment" from the shortcut menu:

Then your diagram will look like this: Click on the diagram, then click on the following and press the Delete key: e) Optional - Delete the caption or insert any text to the diagram caption. I' ve switched mine to sales pipeline. Now your diagram should look like this:

At this stage the diagram is brought into form. We' ll have to conceal the fountain pen set and slide the rods together. Click with the right mouse key on the fillers formula sequence (the illustration on the right ) and select "Format data series" from the shortcut menu: Then, modify the "Gap Width" in the Serial Options to 0%: Then select the Fill Options drop-down list, and then select No Fill:

Now your diagram should look like this: You can see in the above image that the pipeline is monochrome. Therefore I suggest to change the colours of the individual datapoints. You can do this by selecting any datapoint in the graph by twice (twice) pressing a separate button.

Then you will see that only one datapoint is chosen instead of the whole one. You can then click with the right mouse button on this individual datapoint and select the item "Format datapoint...": Then, go to the Fill Preferences and select Solid Fill and select a colour. Select a solid line border and a black one.

You should have your diagram look like this: For all sales levels in the diagram, use a different colour to fill and enclose all of these gaps. Now your diagram should look like this: As we have removed the levels from the perpendicular axes, we should insert them again as labelling and also take the retail value into account.

Do this by selecting the diagram in the Whites pane, then from the Design bar, selecting Data labels using the Add Diagram Element buttons, and then selecting the More Data Labeling Options... drop-down menu: Now your diagram will look like this. It is recommended that you highlight the filler series data labels on the far right and hit your cancel key:

Right-click the Sales volume file tags and right-click one of the tags and click Format volume file tags from the shortcut menu. In the dialog box, under the Labelling Options group, type the Labelling Contains as Category Name and Value with New Line as the delimiter and Center as the Labelling position to append to the Labelling on the graph.

Now your diagram should look like this: That' s why I like to reformat large numbers with a $#M (for millions) or $#K (for thousands). My last move is to create a user-defined style by choosing the captions, going to the number style option, and typing this user-defined style.

Select Custom as the number category and type this formula[>99999999]$#,,, "M";[>999999]$#, "K";$# (Note: If you are having trouble copy ing/pasting this calculation in Excel, you may need to clear and substitute the " " " quotes because the Web Browser may not actually copy a quote) in the Format Code field, and then click the Add Add button. 2.

Then your ultimate diagram will look like this: Although this graph does not look as beautiful as the Excel graph of the 3D polyramid, it is more precise for the naked human eyes and the information. Allow me to know what you think of the Better Excel Sales Funnel Charts in the commentaries below.

Auch interessant

Mehr zum Thema