With the Excel sort feature, it’s a matter of few clicks to sort the entire name dataset with full names. As Excel uses the first character of each name, you can sort the names in ascending or descending order.

However, it is anything but simple if we want to sort the data by the last name in Excel.

Although it looks complex and not entirely simple as the case of full names, it’s doable quite easily if one knows the right steps. And there are several methods to choose from.

But whatever method we choose, we will first have to extract the last name into a separate column. Once the last name is in a separate column, we can sort the names alphabetically in the order we desire.

In this tutorial guide, we will discuss several methods to sort a column with names by the last name.

Starting with the easiest and lastly discussing the advanced tool available to dynamically sort the names data based on the last name in Excel.

Extract and Sort by Last Name using Flash Fill

The easiest method to extract first and last names from the list of the full names is to use Excel flash fill tool.

For Flash Fill to work, you have to repeat the desired results a couple of times. Once Excel recognizes the pattern you are following, it will suggest the rest of the range automatically. And if you agree to the suggestion, you can choose to fill the range.

Following are the steps to extract and sort by the last name using Excel Flash Fill:

1: Name two columns as “First name” and “Last name” each.

2: For the first name, manually type the first and the last name in the respective column.

3: Including the name you just type, select the cells down to the entire range, and hit CTRL+E on the keyboard.

4: Repeat step 3 for the last name column as well.

Following animation shows you how to do it quickly!

Another way to deploy Flash Fill is to simply drag the fill handle down and then select Flash Fill from the option.

Now that we have extracted the last name, we can sort the data using the method discussed here.

Extract and Sort by Last Name using Text to Columns

Excel Text to Columns, a short for convert text to columns, is another tool that can help us easily separate first and last names in a cell.

Text to columns uses identifiers called delimiters to split the content of the cells. In Excel user can specify the delimiter e.g. space, comma, full stop, dash, or any other character.

Once a delimiter that best represents the data is selected, In the preview pane, an Excel user can choose to either import all the columns or skip certain columns to import only selected columns.

Using the same example as above, the following steps will show how to use Text to Column to sort by the last name:

1. Select the names with the mouse or by using directional keys while pressing down SHIFT key on the keyboard. Make sure to EXCLUDE headers while selecting the names.
2. Go to Data tab > click Text to Columns in the “Data Tools” group. A pop-up with three steps wizard will appear.
3. Make sure “Delimited” button is selected and click “Next” button.
4. For step 2 of the wizard, check the “space” option and uncheck any other option under “Delimiters” list. At the bottom of the pop-up, Excel will show a preview of how data will be split. Click “Next”.
5. In Step 3, change the cell address to the cell where you want the split data. In my case, it is cell B2. Click Finish.

The following animation shows you all the steps carried out to get the First and Last name in two separate columns using “Text to Columns” tool:

Now the only thing that remains is sorting by the last name. Head over here to learn the sorting by the last name technique.

Find and Replace [CTRL+H]

By now it is clear that to sort the list of names by the last name, we first need to extract the last name into a separate column.

As we know, full name consists of three parts; first name, space, last name.

Excel’s Find and Replace tool can help us extract the Last Name from the Full Name by simply replacing everything before the last name within the full name.

Here are the steps to extract and sort by the last name using Find and Replace in Excel:

1. Select the names > hit CTRL+C on the keyboard to copy.
2. Move to the cell where you want the list of Last Names to appear and hit CTRL+V.
3. With pasted names still selected hit CTRL+H. A pop-up will appear with replace tab active.
4. In the “Find what” input field put an asterisk followed by the space.
5. Leave the “Replace with” input field blank.
6. Click “Replace All”

This will instantly remove the first two parts of the full name i.e. first name and space. And you are left with only the last name for each name.

Here is the animation that shows all the steps to use the Find and Replace tool to sort names data by the last name:

Now all that remains is sort using the last name which you can do by following these steps.

Extract the Last Name using Excel formula

Although the above methods are simple and effective, they have two significant limitations:

• Results are static. If we add new names to the list, results won’t update dynamically for us. And we will have to perform the whole process again for the new entries.
• If we have a middle name, the above methods can render unreliable results.

To solve these issues, we can use Excel formulas to extract and sort the data by the last names.

Again we are using the same example as above but this time we have few names with a middle name as well.

Following is the formula to extract the last names from names:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Paste the above formula in the cell where you want the list to start (B2 in my case) and drag the fill handle down till the last name. Or simply double-click the fill handle.

Now that we have the last name for each name, we can sort the data using the sort tool in Excel as shown here.

As you can see that formula easily handled the names even with middle names. An additional benefit is that if I add another name to the last, I simply have to drag the fill handle further down to get the results instantly.

Sort the data by the last name in Excel

In all of the above methods i.e. Flash Fill, Text to Columns, Find and Replace and Formula method, we have only extracted the last name in a separate column. To sort the data by the last name we need to take few additional steps as following.

Extracted both the First and the Last Name

And now we can easily sort both the columns by the last name with the following steps:

1. Simply select both First Name and Last Name columns > go to data tab > click sort button in sort and filter group > a popup will appear.

2. From “Sort by” drop-down > select “Last Name” and if you want ascending order leave the order drop down as it is or if you like to sort names in descending order then select “Z to A”.

Extracted only the Last Name

And if you have extracted only the last name in a separate column, the steps are absolutely the same. The only difference is that now we will select Full Names alongside Last Names to sort as shown below:

Power Query – Dynamically Extract & Sort by the last name

Saving the best for the last, Excel Power Query is built for such kinds of tasks.

With Power Query, we can automate the process of extracting and sorting the data by the last name in a few simple steps.

And the best thing is that if our data changes, we simply need to refresh the query to get the latest results.

Below are the steps to extract and sort the data by the last name using Excel Power Query:

1. Select the range containing the full names and hit CTRL+T on the keyboard to convert it to Excel table range. Make sure “My table has headers” is checked. Press OK.
   
2. Go to data tab > Under “Get and Transform” group click From Table button. This will invoke the Power Query module.
3. With mouse, right-click on the header > click Duplicate column.
   
4. With duplicate column selected > in “Transform” group under home tab click Split Columns drop-down button > click By Delimiter.
5. Select Space as the delimiter. Under “Split at” options, click Right-most delimiter. This will give two additional columns. The last column contains the last names.
   
6. Remove the middle column by simply right-clicking the header and pressing Remove.
7. Click the downward-facing arrow at the right corner of the second column and select the order in which you want to sort the data. For example; ascending.
   Remember, we are applying the sort on the last name column which will automatically sort the full name column by the last name. I selected ascending.
8. Right-click on the header of the second column > click Remove to delete the column.
   Don’t worry. Your data will remain sorted. We are only getting rid of the redundant column meant for sorting purposes only.
   
9. Under Home tab > click Close and Load drop-down button > click Close and Load To…
10. A pop-up box will appear with options letting you choose if you want to show the sorted list of full names on a new worksheet or at a certain cell address on the same worksheet. I went ahead with Existing worksheet and C1 as the cell address to show the sorted list.

And here you have the dynamic list of Full names that is sorted by the last name. And if we add new names to the table, we simply need to refresh the query to fetch the latest results. As shown below:

This concludes our guide with five methods on how to sort the data by the last name.

All the methods discussed have their uses. If data is simple and you need a quick turnaround, Find and Replace is the one to go with. But if you have complex data then I prefer formula and power query methods.

Hope this tutorial has added to your Excel knowledge.

You may also find the following tutorials helpful:

• Create Data Validation lists from Unsorted data with duplicates in Excel [How To]
• Multi level or Multi column sort in Excel – How To
• Custom Autofill series and Custom sorting with Custom lists in Excel
• How to sort along Rows horizontally left to right

The post How to Sort by the Last Name in Excel – 5 Methods appeared first on PakAccountants.com.

Excel has many customization options for Excel charts to help us make better charts. And adding a secondary axis in Excel charts is one such customization option.

A secondary axis allows you to plot two variables along two distinct vertical axes i.e. y-axis. Each one on its own vertical axis. This helps us in making better charts as we can plot the data within one chart instead of two separate charts. Have a look at the following illustration:

Adding a Secondary Axis in Excel chart – Why?

Let’s understand why we need to add a secondary axis for better Excel charts in more detail.

Have a look at the following data containing sales figures and quantity in tonnage for two quarters.

And following is the chart using the data in the above table.

Noticed anything? We cannot see quantity figures in the chart!

Although both sales and quantity figures are plotted in the above chart, we can ONLY see the sales in blue.

As sales figures are in millions and quantity in mere hundreds, the difference is significant. Plotting both on a single vertical axis will cause a scaling issue. Should we scale the chart as per sales numbers or should we opt for quantity figures?

If we go with sales figures, it has pushed the scale of the chart to 2 million to accommodate the sales bar (blue). And in the process, the quantity bar (orange) is now so so short, as it is in mere hundreds, that it is now invisible.

And even if we try switching the chart type from a bar chart to a line chart, it doesn’t help either. Rather adds more confusion.

As you can see, the chart is giving a wrong impression that we made millions in sales by selling 0 units.

In the above example, we can clearly understand we cannot plot both sales and quantity on the same axis. And this is why we need a secondary axis.

And if we add a secondary axis to the above situation we instantly get a better Excel chart.

How did it happen? Let’s understand.

By adding a secondary axis to the chart, appearing on the right of the chart, we now have two vertical axes. And each axis has its own scale appropriate for the data plotted. The axis on the left is scaled for sales whereas, the axis on the right is scaled for quantity.

How to add a secondary axis to Excel Charts – Steps

Adding a secondary axis in Excel is very easy. Following are the steps you should follow to add a secondary axis to your Excel chart.

1: Select the entire data by having an active cell within data range and hitting CTRL+A shortcut.

2: Click Insert tab > under charts group click insert column or bar chart drop-down button > click clustered column chart.

3: Having the chart selected > click chart design tab > click change chart type button. A pop-up with chart-type options will appear.

4: From the list on the left click combo.

5. For sales select clustered column from the chart type dropdown. And for the quantity, it should be “Line” or “Line with markers”.

And you are done!

If you want an Excel chart to be aesthetically more pleasing, you can make more changes. Like for quantity figures instead of line chart type you can select “scatter with smooth lines and markers”.

And you can also change the colors of the bars, line, and background, etc.

How to remove a secondary axis in Excel Charts

If for some reason you want to remove the secondary axis from the Excel chart, it is only a keystroke away.

Simply select the secondary axis and hit the delete key on the keyboard. That’s it!

With the mouse, simply hover above the secondary axis > right click mouse button > click delete.

More free tutorials on Excel charts

You may also find the following Excel tutorials helpful:

• 10+ ways to make Excel Variance Reports and Charts – How To
• Create Waterfall Charts in Excel – Visualize Income Statements!
• Making BCG Matrix in Excel – How To
• Making a Slope Chart or Bump Chart in Excel – How To
• Dynamically Highlight data points in Excel charts using Form Controls
• Gantt Chart in Excel – How To
• Making Horizontal Dot Plot or Dumbbell Charts in Excel – How To

Liked this Excel tutorial? Help us spread the word! Pin it!

The post Adding a Secondary Axis in Excel Chart – Making Double or Dual Axis Charts – How To appeared first on PakAccountants.com.

Drop down lists serve multiple purposes that includes showing a list of items as a drop down menu within Excel cell thus making it easy for Excel user to make a selection, and secondly forcing user to only select from the list of items within the provided drop down.

Here is one example of Excel drop down list:

In the illustration above you can see that items listed in column A are used to create a drop down list in cell D2.

Such single drop down lists are really simple to make. But often times, situations require using two or more drop-down lists where the second drop down list is dependent on the result selected in the first drop down.

Such drop down lists in Excel are called dependent drop down lists or conditional drop down lists where every next drop down list shows the items depending upon the selection of the previous drop-down list.

Following is an example of dependent drop down lists or conditional drop down lists:

In the above example, under category if vegetables are selected then only the list of vegetables is fetched, whereas if fruits are selected in the first drop down list, second drop down list shows only fruits.

Or in other words, the ‘items’ drop down list is dependent upon the selection under the ‘category’ drop down list. That is why it is called a dependent drop down lists or conditional drop down lists.

Creating a Dependent Drop Down List in Excel

There are many ways to make one, but here are the steps to one of the easiest methods to create dependent drop down lists using Excel tables:

Step 1: Download the example workbook with sample data to work along.

Step 2: Select the data range > hit the shortcut combo CTRL+T, from the pop-up box make shore “my table has headers” is selected > press OK. Now your simple data range is converted to an Excel table.

Alternatively, having the data selected, you can go to Insert tab > in tables group > click table icon and the Excel table pop-up box will appear > make sure my table has headers is selected > press OK.

Step 3: Select the header of the table in cells A1:B1 and go to Formulas tab > in defined names group click define name button > a new pop-up will appear, for name enter category > press OK button.

Step 4: Select the entire table using the mouse or have an active cell inside the table and press CTRL+A combo twice > hit CTRL+SHIFT+F3 combo to invoke define name pop-up > in the pop-up make sure only top row option is ticked > press OK.

Step 5: On the worksheet go to the cell where you want your first drop down list of “category” to appear. For me, it is cell D2.

Go to data tab > in data tools group click data validation button > in the pop-up that appears, from the allow drop down select “list” > press F3 key on the keyboard > select the “category” from the pop-up > press OK button.

Step 6: Go the cell where you want the dependent drop down list to appear. For me, it is cell E2. Having the cell selected > click data validation button again > under allow select list > in the source input bar type following:

=INDIRECT(D2)

Press OK button.

Remember D2 is the cell that has the first drop down list.

Don’t panic if you get the following error as you might have nothing selected in the first drop down list. Just press yes to proceed.

And now you have the fully functioning dependent drop down lists that show the appropriate results in the second list based on the selection in the first list.

And the best thing about this approach is that if we add or subtract items from the Fruit or Vegetable lists, respective drop down lists will automatically update accordingly.

Have a look:

Also check out the following Excel tutorials:

1. Excel Drop Down List using Data Validation and Excel Tables that updates dynamically – How To
2. Multiple Column Data Validation Lists in Excel – How To
3. Create Data Validation lists from Unsorted data with duplicates in Excel [How To]

The post Making Dependent Drop-down Lists in Excel – How To appeared first on PakAccountants.com.

Here is an example comparing minimum and maximum sales of different products during the period:

I’m not really sure but so far every tutorial I have come across uses some kind of complex method or uses scatter chart to make dot plots in Excel. Well they have reasons but today I am attempting to make fully functional and working dot plot using bar charts! Yup dot plots are meant to replace them so why not! So lets start!

Dot Plot / Dumbbell / Lollipop / DNA Chart in Excel – Step by step

Step 1: Download the Excel workbook that contains dummy data so that you can follow along and learn the techniques applied in this Excel tutorial.

Step 2: Have an active cell inside the range and hit CTRL+A to select the whole data and go to > insert tab > under charts group click insert column and bar chart button > click clustered bar chart. This will insert the chart as following:

Step 3: Having the chart still selected go to design tab > click change chart type under type group > from the pop up box select combo from the list on the left and make the following changes:

• new series change chart type to clustered bar and select secondary axis
• old and delta series change chart type to stacked bar

You might have to juggle the chart type few times as you make the changes so have the correct chart type before you press OK. Once done your chart will look like this:

Step 4: Having the chart still active go to design tab > click add chart elements > axes > click enable secondary vertical axis.

Step 5: Right click on the left vertical axis > click format axis > from the bar on the right click on axis options icon > under axis position tick select categories in reverse order. Do the same for the vertical axis on the right.

Step 6: Delete the bottom horizontal and right vertical axis by simply left-clicking once and hitting delete key on the keyboard. Now we have the series plotted in the correct order.

Step 7: Click on the only visible series which “New” > go to design tab > click add chart elements > error bars > click percentage.

Step 8: Having the series still selected > go to format tab > click shape fill > select no fill.

Step 9: Right click on error bars > click format error bars. From the bar on the left Making the following changes:

• Direction: Minus
• End style: No cap
• Error amount > Fixed value: 0.1

Step 10: Click Fill and Line icon > change the color to your liking, I chose blue. Increase the width to 2.5 > change begin arrow type to oval arrow.

Step 11: Click the downward arrow to the right of Plot Area Options > select “Delta” series > go to design tab > click add chart elements > error bars > click percentage.

With series still selected go to format tab > click shape fill > click no fill.

Step 12: Right click on the just inserted error bars > click format error bars and make the following changes:

• Direction: Minus
• End style: No cap
• Error amount > Percentage: 100%

Step 13: Select old series from the drop down arrow > go to design tab > click add chart elements > error bars > click percentage. With series still selected go to format tab > click shape fill > click no fill.

Step 14: Select old series error bars from the drop down arrow and make the following changes:

• Direction: Plus
• End style: No cap
• Fixed value: 0.1

Step 15: Click Fill and line icon and change color, I liked red. Change width to 2.5. Change begin arrow type to oval arrow.

Data labels for Excel dot plot / dumbbell / DNA / Lollipop chart

Now that we have a finalized chart, we can add more value if we have data labels with each dot. This was a challenge but the we have approached the chart, its super easy now.

Step 1: Select old series from the drop down arrow > go to design tab > click add chart elements > data labels > click inside end.

Step 2: Add the data labels for “New” series as well using same method but having them placed at outside end.

Final Touches – Excel dot plot / dumbbell / DNA / Lollipop chart

Get rid of legends at the bottom by left-clicking and hitting delete key on the keyboard. To bring the x-axis down right click on the axis > click format axis > click labels > from the drop down select high.

And finally with background of the chart colored and appropriate chart title we have the following result:

Loved it? Pin it!

The post Making Horizontal Dot Plot or Dumbbell Charts in Excel – How To appeared first on PakAccountants.com.

Some features of the Excel variance chart above we are learning today:

1. Each element in the chart has its own place and doesn’t obstruct others. Best choice to plot dynamic data that changes frequently.
2. Favorable variance is shown in green and unfavourable variance in red arrow with variances in number shown above.
3. One series, either actual or target, takes lollipop shape bar with the relevant number to its left.
4. Figures of the second series are plotted in yellow bar with the numbers at base. Again this series can either be actual or budgeted, planned or target series.
5. Just provide actual and budgeted figures and get the chart done. Simple!

Get fully worked Excel Variance Chart Template

What is a variance chart?

Most of the time variance charts compare two sets of series with each other to calculate the difference between each iteration. For example, we want to compare the sales of two companies for the last ten years. Comparing the sale of one company with the other for every year gives us variances or differences. Graphing these variances gives us variance charts.

There are a number of ways to make Excel variance charts from very simple ones like the one I discussed or a complex one with several Excel techniques applied for optimization like this one.

Important: Understanding Actual vs Target variances

As mentioned earlier, variance is simply a difference between actual and target or budgeted/planned numbers.

Understand one thing clearly, having actual greater than target isn’t always favourable. It depends on the nature of data and what the variance is about.

In case of cost, having actual lesser than the target is a good thing. But if data is about sales then having actual figures greater than the target is favourable.

For the purpose of this tutorial, I am making variance analysis for actual and budgeted costs. Therefore, if actual is less than target its favourable. And if a target is greater than actual then its unfavourable.

Advanced Excel Variance Charts – Step by step

Download this Excel workbook with sample data to follow along with the steps detailed in this tutorial.

There is a lot going on in this tutorial. So I have divided the whole in relevant bits for easy understanding.

Getting data ready for variance bars

As we are planning to plot favourable variance in green and unfavourable in red, we need to separate each from a single set of variances.

Step 1: In cell D1 give heading V. Green and in the cell, D2 put the following formula and double click the drag handle to populate down the cells:

=IF(B2>C2,B2-C2,””)

Step 2: In cell E1 give heading V. Red and in cell E2 put the following formula and double click or drag the fill handle to populate the cells:

=IF(B2<C2,C2-B2,””)

The above two steps split the variances in two so that each can be given a separate color. The formula basically checks if Actual is greater than target or not. If it is then the difference will show up under the green column. And if not then variance figure will appear under the red column.

Preparing data for actual and target series

Here we have a choice to make. Should it be actual series or target series that appears in bold bar? I prefer the actual series to appear as a bold column and target series as a lollipop to the left of each bar. This will clearly show actual results against planned targets on the chart.

Step 1: Go to cell F1 and put a heading Actual > Target and in cell F2 put the following formula and double click the fill handle to fill down:

=B2<C2

This checks if actual figure is greater than target figure and returns TRUE if it is the case. False if not.

Step 2: Go to cell G1 and give a heading Holder. And put the following formula in cell G2 and down the cells:

=IF(F2,B2,C2)

This is an interesting series and I will explain once chart takes the shape for better understanding.

Making Excel variance chart

Step 1: Select all the columns except Actual > Target using CTRL key + mouse. Once selected hit ALT+F1 to insert a column chart.

Step 2: Having chart active > go to design tab under chart tools > click change chart type.

Step 3: Click Combo from the list on the left and make the following changes:

Target: Chart > Clustered column, Secondary axis > checked
Actual: Chart > Clustered column, Secondary axis > checked
Holder: Chart > Stacked column, Secondary axis > unchecked
V. Green:  Chart > Stacked column, Secondary axis > unchecked
V. Red:  Chart > Stacked column, Secondary axis > unchecked

Step 4: Click holder series element in the chart by left-clicking in the formula bar change the series position to 1. This will bring the holder series at the bottom in the chart.

Step 5: Click on Actual series in the chart > go to format tab under chart tools > change shape fill to no fill. This will hide the actual series.

Step 6: Click on Target series to select > go to design tab > click add chart elements > select standard error.

Step 7: Right-click on the error bars > click format error bars. From the bar on the right > under direction click minus. Under end style > select no cap. Under error alignment click percentage > enter 100 in the input box and press Enter key.

Step 8: Having error bars still selected > click fill and line icon in the right bar. Change width to 2.75 pt. Change begin arrow type to oval arrow.

Step 9: Select the target series and from format tab change shape fill to no fill.

Getting arrows in Excel variance chart

Step 1: Click V. Red series in chart to select it > go to design tab > click add chart elements > hover over error bars and select standard errors.

Step 2: Right click on error bars just inserted > click format error bars. From the bar on the right click error bar options icon at the top and make the following changes:

direction: click minus
end style: click no cap
error amount: click percentage and in input box type 100 then hit Enter key.

Step 3: Having the error bars still selected click fill and line options. Change color to red or whatever you deem fit for unfavourable variance. Increase the width to 3pt. Change begin arrow type to arrow.

Step 4: Click on V. Green series in the chart > go to design tab > click chart elements > hover over error bars > select standard error.

Step 5: Right-click on to select the error bars just inserted > select format error bars and make the following changes in the right bar:

direction: click minus
end style: click no cap
error amount click percentage and in input box type 100 and press Enter.

Step 6: Click fill and line in the right bar with error bars still select and make the following changes:

Color: green or whatever you deem fit for favourable variance arrow
Width: 3pt
End arrow type to arrow

Step 7: Click on V. Green series again to select it > go to format tab > click shape fill > click no fill.

Step 8: Right anywhere empty inside the chart > select format plot area. It will open up bar on the right. Click the drop-down arrow at the right of Plot area options and from the list select holder series. Go to format tab > click shape fill > select the same color as you selected for V. Red series to match it up completely.

Configuring chart elements

This step is optional and you can skip if chart is already the way you like.

Mine has few quirks like target bar misalignment, actual bar too thin for my liking etc.

To have target lollipop aligned right on the edge of the actual bar, right-click just a little to the left of target lollipop in the seemingly empty area to select target series > select format data series. Change the following:

Series overlap: -20%
Gap width: 0%

From the bar on the right-click the drop-down menu with series option heading and select holder series and make the following changes:

Series overlap: 100%
Gap width: 100%

Managing data labels for Excel variance chart

By now we have fully worked and flexing variance chart that is not just amazing to look but also fulfills the purpose so precisely that you can get the idea gazing at it just once.

However, something really important is missing. NUMBERS!

Though we can get the idea of what each series might amount to but our chart can be more efficient with numbers to each series.

This is where chart data labels are used. It looks like a simple thing as one can insert it via chart elements under chart tools tab but this isn’t working in this chart:

So we need to work around and this is where you are going to learn how to excel at Excel labels to make awesome charts

Customizing Excel chart labels – Label the way you wanted!

Labels for actual series bar

Our actual bar is actually a stacked column. Problem with the stacked column is that it only shows the labels inside the column which is not legible enough to my taste. Have a look:

Ideally, it should be above the column. For this, we will have to add dummy series plotted as line chart just for this purpose. But before we can do it we need data for it.

Step 1: Go to cell H1 and give heading as Dum. Leg. In cell H2 put the following formula and double click fill handle to populate the whole range:

=IF(B2>C2,B2,C2)

Step 2: Select the column just added including data and heading and press CTRL+C. Activate the chart by clicking once anywhere in the chart and right click on any corner of the chart > select paste icon.

Step 3: Click on the newly added series > go to design tab > click change chart type > scroll down to find Dum. Leg. series in the list and change the chart type to line > click OK.

Step 4: Click on the line in the chart > go to design tab > click add chart elements > hover over data labels > click above. This will add the labels fetched from the series.

Step 5: Click on the line again if its not already active > go to format tab > click shape outline > click no outline.

Step 6: Go to cell I1 and give heading Var. Leg. and for cell I2 and down put the following formula:

=IFERROR(IF(D2=””,-E2,D2),””)

Step 7: On the chart, right-click on the actual bar labels > click format data labels and check value from cells and select the data range I2:I10 that we just created. Click OK.

Step 8: Uncheck boxes other than value from cells.

Labels for target series lollipops

We can easily add labels to even error bars in Excel. And even for target series in our chart, it is possible like this:

However, I am not liking how close the numbers of target series and variance arrows get. If we can manage the labels for targe series to the top-left that will make each label discreet to each series and easy to relate. For this we need to take help of scatter with straight line chart.

Step 1: Go to cell J1 and give heading X-axis and fill the column with 1,2,3…, till 9.

Step 2: Select target series data range, heading included, hit CTRL+C to copy > right-click over chart > click paste icon to insert a new series in chart. You might not be able to see it but don’t worry.

Step 3: Having chart active go to design tab > click change chart type > scroll down to locate the target series at the end of list > change chart type to scatter with straight line and click OK.

Step 4: Right-click on the line that is now visible > click select data. This will open up a box > scroll down the list and click on target series to select it > click edit button.

Step 5: Edit series box will pop up > remove everything in the series X values input bar > select the data of x-axis column excluding heading. Click OK and OK.

Step 6: Now we want to align the start of line exactly at the center of round head of first lollipop. In the current scenario, 0.75 gives the appropriate alignment. Now just add 1 in every next cell i.e. 0.75, 1.75, 2.75, 3.75,…,8.75. This will align the line with the heads of target series.

Step 7: Click the line once to select it > go to design tab > click add chart elements > hover above data labels > click left. This will insert the labels for target series.

Step 8: Having the line still selected go to format tab > click shape outline > click no outline to hide the line.

Labels for actual series bar

Step 1: Right click on the chart > click format chart area. From the bar on the right > click drop-down arrow and select Holder series > go to design tab > click add chart elements > hover over data labels > click inside base.

Step 2: Right-click on the data label just inserted > click format data labels > check value from cells and select the range of Actual series from the Excel sheet excluding header > Uncheck other boxes.

Step 3: Left-click once on the actual series data label > go to format > click shape fill > click more fill colors. From the color palette select white shade and adjust transparency to 50%.

Getting rid of unwanted chart items

Simply left click once on the following items and press delete key on the keyboard:

1. Chart title
2. Right and left vertical axes
3. Major gridlines
4. Legends

And here it is in all its awesomeness flexing as the dynamic data updates:

Download fully worked Excel Variance Chart Template

The post Excel Variance Charts: Making Awesome Actual vs Target Or Budget Graphs – How To appeared first on PakAccountants.com.