How to make visual comparisons of actual sales to target goals in an Excel chart

2 years ago 432

When the narration betwixt the charted entities is little important than the idiosyncratic entities, effort utilizing these 2 illustration techniques to item important details successful Microsoft Excel.

Image: iStock/muchomor

Charts are awesome tools erstwhile they archer a story, and they bash truthful by highlighting details that mightiness different get mislaid successful the chart's noise. The quieter a illustration is, successful my opinion, the much effectual it is. This is easy represented by clarifying details successful a illustration that compares existent income by unit to each person's people goal. A bully illustration volition archer the data's communicative with small to nary effort connected the viewer's portion to find the hidden clues—details that matter. In this article, I'll amusement you 2 charts that correspond income and people goals successful Excel. We'll commencement with the default charts and alteration formatting to execute a cleanable illustration that exposes details absorption volition surely privation to know.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions. Excel Online volition show the charts, but you can't make them online. For your convenience, you tin download the .xlsx and .xls objection files.

Prepare your data

To get the expected results, the mode you operation your information matters. Sometimes, you tin alteration axis and bid values erstwhile the illustration exists, but for the astir part, you should commencement with a unchangeable operation design, and that's not arsenic hard arsenic it sounds. In addition, retrieve that you don't person to illustration the full information set; you tin illustration the columns and rows you want, adjacent if they're not contiguous. We won't beryllium dealing with that benignant of illustration this clip around, but it's bully to know.

SEE: How to usage XLOOKUP() to find committee benchmarks successful Excel (TechRepublic)

For our purposes, we'll way existent income amounts and each person's quota oregon people goal. Many radical successful income enactment with circumstantial expectations. Formulas that find commissions and bonuses usually measure the differences betwixt the existent income and the targets; committee and bonus magnitude astir apt usage a higher percent erstwhile targets are routinely met oregon exceeded.

Figure A shows a elemental information acceptable with nary existent structural issues. The illustration to the close is simply a 2D Column illustration that groups each person's existent income and people amount. (I entered a caller illustration title, but you don't request to bash so.) Notice that the selected information includes the header substance and doesn't see the Commission column.

Figure A

As is, the illustration is OK, but it's a spot hard to prime retired who's gathering their people and who isn't. You person to look astatine each acceptable of bars to discern thing meaningful, and that's not however charts are expected to work!

If you don't cognize however to insert a chart, bash the following:

Select B2:D8—the information you privation successful the chart. Include the header substance truthful Excel tin make a fable automatically.
Click Insert and past click Insert Column Or Bar Chart successful the Charts group.
Choose the archetypal enactment successful the archetypal line. That's it!

Creating this illustration was easy, but I deliberation we tin each hold that it isn't arsenic effectual arsenic it could be. The values, some people and actual, are excessively adjacent to 1 different to truly item what's going connected (or isn't). For instance, you mightiness wholly miss that James didn't conscionable his people and that Luke, the caller feline is already surpassing his archetypal goal. James needs a pep talk; Luke has earned a pat connected the back.

How to overlap 2 bid successful Excel

Overlapping the 2 bid is 1 mode to wide things up a bit. In different words, let's determination the people bid truthful that it's connected apical of the existent worth series. The quality betwixt each person's people and existent income volition beryllium much apparent—or astatine least, we deliberation they should be. Sometimes it works; sometimes it doesn't.

To make the overlapping series, bash the following:

Click immoderate of the bars successful the existent bid (the bluish bars) to prime the full series.
Right-click the conception and take Format Data Series from the resulting submenu, which volition unfastened the Format Data Series pane (to the right).
Change the Plot Series On enactment to Secondary Axis, arsenic shown successful Figure B. Doing truthful adds a caller axis to the close side. It's casual to spot that James hasn't met his people but present June and Luke are a spot of a mystery. They've exceeded their targets, but by however much?
Return to the illustration and prime the people bid (the orangish columns).
Change the Gap Width to 100. As you tin spot successful Figure C, everything is much apparent, but we person that secondary (vertical) axis to woody with. The secondary axis allowed america to format the 2 bid differently, but we nary longer request it, and frankly, it adds confusion.
Select the secondary axis to the close and property Delete. Figure D shows the resulting overlapping series, and it makes sense!

Figure B

Figure C

Figure D

I don't cognize astir you, but I find the default colors a spot excessively garish. It isn't indispensable for the technique, but you mightiness privation to alteration the colour of 1 oregon some of the series. You tin take immoderate colors you like, but I'm going to alteration the people columns, the orangish colour to a lighter bluish by choosing a antithetic theme. To bash so, click the Format icon successful the top-right country of the chart—it's the paintbrush. In the resulting pane, click Color (at the top). Choose a much pleasing theme. I chose the next-to-the-last one, which is shades of blue. Feel escaped to take immoderate taxable you want. Alternatively, you could right-click the series, take Format Data Series and click the Fill enactment to alteration lone the 1 series.

SEE: Office 365: A usher for tech and concern leaders (free PDF) (TechRepublic)

The overlapping columns bash a amended occupation of highlighting details than the default chart, but it isn't the lone mode to correspond these values. In fact, due to the fact that the values are each truthful close, James' nonaccomplishment to conscionable the people is inactive casual to miss—not arsenic easy, but I deliberation we tin inactive bash better.

How to marque a combo illustration successful Excel

The occupation that we've dealt with from the opening is that each of the values are akin truthful there's not a batch of contrast—contrast that would gully our attraction and marque immoderate details much obvious. This clip we'll correspond the people values arsenic a enactment alternatively of a file by utilizing a combo chart.

To get started, prime the aforesaid data, B2:D8 and past take Insert Combo Chart successful the Charts radical connected the Insert tab. Choose a Clustered Column Line chart. Figure E shows the caller illustration (using the aforesaid default bluish and orange). Without doing a thing, it's wide wherever everybody stands. However, you mightiness alteration the colour taxable arsenic we did to the archetypal chart. As before, it isn't indispensable to conscionable the method requirements; it's conscionable easier connected the eyes.

Figure E

At this point, you could stop, but I'll amusement you a alteration you mightiness like. Specifically, we'll region the connecting lines and reformat the markers. The effect is simply a cleaner chart, but you bash suffer the travel of the connecting lines that tin stress the differences betwixt personnel. But successful this case, comparing the unit whitethorn not beryllium of interest.

Our enactment doesn't person markers, truthful that's the archetypal step. If you already person markers, you tin skip this adjacent section. To show markers, prime the enactment and past click the contextual Chart Design tab. In the Type radical click Change Chart Type. In the resulting dialog, alteration the Target mounting from Line to Stacked Line with Markers, arsenic shown successful Figure F, past click OK.

Figure F

To region the connecting lines, double-click it to unfastened the Format Data Series pane, click the Fill & Line option, and past click Line astatine the apical (if necessary). If you don't similar the circular markers, alteration them arsenic follows:

Click Marker (at the top).
In the Marker Options section, click Built-in.
Choose the horizontal enactment from the Type dropdown and summation its size; I changed it to 15.

As you tin spot successful Figure F, removing the connecting lines produces a cleaner chart, but the information is, this alteration is preferential. A speedy glimpse is each you request to spot that James is the lone idiosyncratic not gathering the target. A 2nd speedy reflection is that nary of the unit are producing good supra their target. In addition, it mightiness beryllium clip to summation Luke's target, which volition astir apt mean besides expanding his committee percentage. He'll beryllium blessed to perceive that.

Figure F

There are different ways to execute ocular results that item the data's details. You could usage sparklines oregon adjacent conditional formatting if you don't privation to make a accepted chart. You tin usage either of these techniques for immoderate benignant of idiosyncratic examination wherever the narration betwixt the charted entities isn't arsenic important arsenic the individuals.