Excel for Office 365 Word for Office 365 Outlook for Office 365 PowerPoint for Office 365 Excel for Office 365 for Mac Word for Office 365 for Mac PowerPoint for Office 365 for Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Excel 2019 for Mac PowerPoint 2019 for Mac Word 2019 for Mac Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2016 for Mac PowerPoint 2016 for Mac Word 2016 for Mac Excel Online Data labels make a chart easier to understand because they show details about a data series or its individual data points. For example, in the pie chart below, without the data labels it would be difficult to tell that coffee was 38% of total sales. You can format the labels to show specific labels elements like, the percentages, series name, or category name. There are a lot of formatting options for data labels. You can use leader lines to connect the labels, change the shape of the label, and resize a data label. And they’re all done in the Format Data Labels task pane.
To get there, after, select the data label to format, and then click Chart Elements Data Labels More Options. To go to the appropriate area, click one of the four icons ( Fill & Line, Effects, Size & Properties ( Layout & Properties in Outlook or Word), or Label Options) shown here.
I’m starting a series on dashboards because I think creating sexy dashboards is a critical skill every marketer needs to know. It’s going to be a long series — but by the time we’re finished, you’ll be able to create dashboards that excel in both form and function.
(Editor’s note: This popular series continues on Marketing Land with!) Why Use Combination Charts? The first skill we’re going to focus on to that end is how to create a combination chart. When I took, in my dissertation, Google Analytics evangelist Avinash Kaushik asked me why I didn’t show visits and bounce rate together in the same chart. (Disclosure: I am not affiliated with MarketMotive in any way.) It’s embarrassing to admit this now, but I had no idea how to do that — or that you even could combine totally different metrics like that. But now, I use them all the time.
Formatting Charts in Excel Once you create a chart it's easy to format and enhance your chart using Excel's menus and commands. To change chart style in Excel, simply right click or double click on the chart item you want to format to view the formatting options for that item.
The reason is that they give you the ability to demonstrate data trends visually. (So thanks, Avinash!) Some classic metrics I use frequently in combination charts are:. Year-over-year data. Visits vs. Bounce rate.
Revenue vs. Conversion rate.
Campaign cost vs. Conversions Download The Excel Doc If you’d like to follow along, feel free to I pulled all my screenshots from. How To Create A Combination Chart Step 1: Have a dataset with at least the two values you want to chart.
(I also always.) Step 2: Click any cell inside your dataset and go to Insert Charts Insert Column Charts Clustered Column (in 2013 on the PC) or Charts Column Clustered Column (in 2011 on Mac). With my dataset, I’m just going to select the Visits and Revenue columns since I have an extra column for conversion rate. Click for larger image Step 3: Clean up your chart with the techniques I describe in this post on. I cleaned mine up using several of the techniques I described in that post:. Removed gridlines.
Thinned out the vertical axis. Added an intuitive title. Removed the tick marks in the horizontal axis. Changed the default column colors to match my branding by selecting one of the columns in the series and choosing my new color using the Fill Color icon (under Home Font for both versions) All of these options are accessible to you by simply pressing Ctrl-1/Command-1 (PC/Mac). Click for larger image Adding A Secondary Axis Let’s say we want to also add conversion rate to the chart. I changed the Revenue series back to a column chart to decrease chart junk.
One super cool trick to adding a new data series to a chart you’ve already created is to just select the column, copy it, select the entire chart (you’ll see the whole chart outlined), and then paste it in. It won’t look amazing because we’re pasting in a value that’s less than one. But then, all you have to do if you’re following along with the download is: with the chart selected, choose the new series by going to Chart Tools Format tab Current Selection Series “Conversion Rate” (2011: Charts tab Format tab Current Selection Series “Conversion Rate). To change the chart type to a line chart, I’ll break out the processes for 2013 and 2011 separately since the steps for the Mac are so different. 2013 (PC): With the Conversion Rate series selected, choose Design tab Type Change Chart Type. Then, in the Change Chart Type dialog, set Conversion Rate to Line from the drop-down. And put it in a secondary axis by selecting that option.
Then click OK. 2011 (Mac): With the Conversion Rate series selected, choose Charts tab Change Series Chart Type Line 2-D Line Line. Then select the series again like you did before (it doesn’t stay selected for maximum frustration value) and press Command-1 to pull up your formatting options. In the Format Data Series dialog, choose Axis Secondary axis. Then click OK.
I changed the series color to green, thinned out the secondary axis, and ditched the decimal places. If you don’t need them to interpret the data, kick them to the curb. (I almost always get rid of decimal places in my axes!). Click for larger image And, here’s one more I did for a client to be able to view their PPC campaign costs vis-a-vis their results. In this case, the first step in their conversion funnel is a call.
The final step is an actual job. This chart has more data than most I create, but I wanted to capture both phases of conversions in one chart. You can see that two of their campaigns in Bing drove eight times more jobs than their AdWords campaign they’re sinking most of their resources into, for less than 1/10 the cost. That’s actionable data right there!