Dr. Peter J.

Scharpff RI

Excel 365 Charts

Graphical Representation of Figures
 

DR PETER J. SCHARPFF RI

EXCEL 365 CHARTS

GRAPHICAL REPRESENTATION
OF FIGURES

2
Excel 365 Charts: Graphical Representation of Figures
1st edition
© 2019 dr Peter J. Scharpff RI & bookboon.com
ISBN 978-87-403-3234-6

3
EXCEL 365 CHARTS Contents

CONTENTS
About the author 6

1 Introduction 7
1.1 Structure 7
1.2 Format 8
1.3 Software 8

2 Charts 9
2.1 Introduction 9
2.2 Creating a chart 9
2.3 Updating 13

3 Formatting charts 14
3.1 Introduction 14
3.2 Format 14
3.3 Customizing 18
3.4 Formatting risks 20
3.5 Axes 26
3.6 Tips 29

4
EXCEL 365 CHARTS Contents

4 Chart templates 31
4.1 Introduction 31
4.2 Creating a template 31
4.3 Applying a template 33
4.4 Exercise 34

5 Combined charts 35
5.1 Introduction 35
5.2 Series 35
5.3 Strange images 39
5.4 Formatting 41

6 Trendlines 46
6.1 Introduction 46
6.2 Discover trends 46
6.3 Predictions 48
6.4 Exercises 51

7 Sparklines 52
7.1 Introduction 52
7.2 Dependency 52
7.3 Data table 55
7.4 Special formatting 57
7.5 Other sparks 58
7.6 Exercise 59

Appendix 63

5
EXCEL 365 CHARTS About the author

ABOUT THE AUTHOR

Scharpff Consultancy is a consulting organization that has been active in the world of
automation for decades. Information, training and guidance on the use of personal automation
tools are key focuses. One of the specialties is facilitating (and performing) individual training
programs for PC-users. The model for this has been conceived in the early 90s from the
last century by Scharpff Consultancy in collaboration with various experts. Nowadays, it is
used by most training centers and even by mainstream educational institutions. Individual
training has proven to be a better method for learning computer skills than the classroom-
trainings that are still predominant in the training world.
Scharpff Consultancy has a lot of expertise in developing and publishing materials for
individual training purposes. This textbook is a product of that specific know-how. It
will allow you to obtain the necessary knowledge and practical skills on your computer
completely on your own.

Dr Peter J. Scharpff RI, who is both the founder of this consultancy agency and the author
of this workbook, originated from the field of computer-linguistics. His scientific focus was
on the interaction between man and machine, especially on the technical and linguistic
aspects of speech in that proces. Besides developing materials for training purposes for
office automation, he also produced many publications on a broad range of topics such as:
digital security, privacy, hardware, programming, web design, teleworking, social networks
and many more. Several of his ebooks discuss hobbyist topics such as drawing, audio, video
and photo editing.

6
EXCEL 365 CHARTS Introduction

1 INTRODUCTION
The purpose of this ebook is to familiarize you, as an end user of Excel 2019, with the
graphical capabilities and features in this popular spreadsheet program. Excel has a lot of
possibilities in that area: beautifully formatted charts, predictive trendlines, or insightful
sparklines. You will learn in this workbook how to present your data more clearly and
clarifying than with the ‘plain’ numbers.

1.1 STRUCTURE
This ebook is modular in design. Each module can be studied separately. However, some
exercises use files created in earlier chapters. A module is set up in the following way:

• Each chapter starts with a short explanation of the topics that will be addressed.
• If possible, each topic will be presented with a (short) exercise. These exercises
describe a possible situation or problem, the way to handle or tackle it, and the
final outcome.
• At the end of a chapter, there is often an extra exercise to put into practice what
you’ve learned.

Exercises are labelled with a mouse icon next to the text. Please, focus on these
instructions, you don’t need to perform all actions or examples mentioned in the
text. It can be beneficial, however, to experiment with other possibilities suggested
in the text.

Even though it is possible to go through each module independently from the others, in
exercises later in this book we will not mention all needed actions and keystrokes because
we assume that you, working through the material already obtain certain skills. In several
exercises you’ll find only general instructions and you’ll have to figure out yourself how to
accomplish the task. In this way you will learn the most.

7
EXCEL 365 CHARTS Introduction

1.2 FORMAT
Names of keys and buttons are given between square brackets: [ and ]. In some cases you
have to press two keys at the same time, we will denote this by a plus sign: [Alt]+[F]. When
you have to type two keys consecutively, there will be no plus sign: [F5] [2] [Enter].

Titles, texts and options that you see in your window or on screen are noted in italics, for
example: ‘the Properties window contains several sections and tabs such as View, Security
and others’. File names and folders are shown as bold text.

1.3 SOFTWARE
We assume that you have access to the software described here and that you can perform
standard operations in Windows, e.g. left and right click, double-click and drag. Furthermore
you must have a basic working knowledge of the actions in Excel. Only where actions differ
significantly from normal use, they will be mentioned in the exercises.

Office 365, and also Excel 365 as a part of it, is a subscription-based software product. In
addition to security updates, Microsoft is also providing new design and functionality during
the subscription period. As a result, the commands, (caption) names or illustrations in this
user guide may differ slightly from your situation. We regularly provide our ebooks with
updates, especially for ‘big’ changes. This is how we try to keep things as close as possible
to the present state of design.

This ebook does not, of course, include all the options and possibilities of the software
discussed here. It is part of a series of ebooks about Office 365. At the end of this book
you will find a brief description of our other Excel ebooks, to determine if they can help
you to deal with other aspects of the software.

8
EXCEL 365 CHARTS Charts

2 CHARTS

2.1 INTRODUCTION
‘A picture is worth a thousand words’, or in this case: ‘…a thousand numbers’. It is not quite
easy to draw conclusions from a large number of figures in a worksheet. Charts representing
the numbers give a lot more insight and overview. In this chapter, we’ll discuss how to
create a chart from your data, which types to choose from, how to add the necessary labels
and other elements. Using adequate descriptions in a chart will clarify the meaning of the
images you present.

2.2 CREATING A CHART

To create a chart from a model – with numbers presented in tabular form – you must first
select the cells in the worksheet on which the chart should be based. Then insert a new
object and choose a specific type of chart. The chart is immediately adjusted as data change
in the cells on which it is based. You may take this into consideration when picking a type of
chart for your purpose. First we’ll build a simple model, and then add a simple chart to it.

Start a new workbook.

Create the model as shown in figure 2.1 below – also set the desired number, text
and cell formatting – and save it as Sales Summary (for example, in the folder \
My Workbooks that we have used before in the Excel basics ebook).

Figure 2.1 Model of sales data

All numbers in the figure above are in pounds since this

user guide is written for the UK version of the program.
Of course, it is also possible to display the amounts in a
different currency, e.g. euros (€) or dollars ($). You already
know how to do this from the ebook on Excel basics.

9
EXCEL 365 CHARTS Charts

The model presents an overview of the turnover of a lunchroom and a pub over the quarters
in a certain financial year. In order to make the many ‘big numbers’ in the model more
understandable, so that it is easier to see which quarter has done well, we’ll add a chart to
the model.

Select cells A1:E4.

On the Insert tab, in group Charts, click the Recommended Charts button.

Figure 2.2 (Recommended) chart of the quarterly figures

Select (with [OK]) the recommended chart: a Clustered Column chart. You may
also choose this chart type manually via the Insert Column or Bar Chart button in
the Charts group.

By selecting the cells as above, Excel deducts how and where to add labels in the chart
to clarify the data. Unfortunately, including cell A1 in the selection does not immediately
lead to a ‘correct’ chart title in this case. You will have to arrange that yourself (see below).

10
EXCEL 365 CHARTS Charts

The chart appears as an object on the worksheet and, when selected, has a ‘thick’ border
with (round) handles to resize it. The objects border can also be dragged to move the chart
to another position.

Place the chart object under the calculation model. Notice in the model the colored
frames to indicate where Excel retrieves the numbers and labels from. The program
only shows these markings if the object is selected.
Increase and/or decrease the height of the object, to see how the columns and, for
example, the scaling on the Y-axis are being adjusted.
Undo the adjustments ([Ctrl]+[z]).
Now enlarge the chart: this way you can accurately see what happens when you
make adjustments in the exercises below (when finished, you can always resize the
chart again).

If the chart is selected on the worksheet, the ribbon will show some additional tabs,
containing various options to customize the chart. Next to the selected chart object, you
also find three buttons for the most common features: adding or removing chart elements,
assigning chart styles or colors, and filtering values or names in the chart.

Figure 2.3 Additional tabs and buttons for the chart object

11
EXCEL 365 CHARTS Charts

Select the chart object and activate the Chart Design tab.
In group Type, click the Change Chart Type button. In the dialog you’ll find
all available chart types on the All Charts tab. The chart type of your choice is
highlighted here with a grey border.

Figure 2.4 All types of charts

Choose some other chart types in the various categories in the list and, if present,
a subtype at the top of the right window pane. Excel gives you a preview of your
choices.

Not all chart types can be created based on the data you
have now selected for the column chart. Excel will indicate
this and if necessary provide you with clues as to how your
model should be designed.

12
EXCEL 365 CHARTS Charts

Finally, end the survey with a 3-D Clustered Column chart. If you are not sure
which one it is, hover over the thumbnails at the top of the right pane to choose
the right one. Assign this chart type to the object by double-clicking it, or by
selecting it followed by [OK].

It’s quite a skill – even when using the Recommended Charts button – to choose the ‘right’
type of chart to present your data. Or in other words: which chart shows best what your
figures mean. And even accounting for personal taste can play an important role in choosing,
designing and formatting an appropriate chart based on your data.

In almost every new release of Office and Excel, Microsoft

introduces new chart types. In this version 365, the types
Map and Funnel (see the left list of types in figure 2.4)
are new. You may want to examine them closer on how to
use them. Also look in Help or other information to find
out how your model should be designed for getting the
desired result.

2.3 UPDATING
If the data on which a chart is based change, the chart object is updated automatically. This
may also affect the layout or the presentation of the chart. Let’s see what happens with the
chart when changing the underlying data.

Click anywhere in the calculation model (so the chart is no longer selected).
Change the revenue of the pub in quarter 2 to: 18500.You see that the corresponding
column in the chart is adjusted accordingly.
Now enter the amount 100000 as the turnover for the lunchroom in one of the
quarters.

The consequence of this last action is, of course, that not only the column but also the
Y-axis must be adjusted. And that results in resizing the other columns as well. So changing
the data can affect the entire layout of the chart object. Keep that in mind, and also take in
consideration that figures coming from other (external) sources – using the right references
to other sheets or workbooks that you don’t control or modify yourself – may have similar
(undesired) consequences!

Undo the changes of the amounts. See that the chart is modified automatically.
Save the model (do not close it if you continue now with the next chapter).

13
EXCEL 365 CHARTS Formatting charts

3 FORMATTING CHARTS

3.1 INTRODUCTION
Many elements of the chart can be customized to suit your needs. But before we come to
doying it yourself, it might be a good idea to see what Excel can do for you in formatting
the charts using built-in layouts and styles.

3.2 FORMAT
You probably already noticed that, for example, the styles in the Chart Styles group of the
ribbon correspond to the chart type you have chosen. And also the layout of the chart has
a few ‘preset’ options.

If necessary, open the workbook Sales Summary and select the chart.
In the Chart Layouts group of the Chart Design tab, click on the Quick Layout
button. Hover over all options and find out which chart elements Excel can place
in the chart object: data labels, axes, titles, tables, gridlines, and legends.

14
EXCEL 365 CHARTS Formatting charts

Figure 3.1 Chart with axis titles

Finally, select Layout 9 that will add all titles to your original chart (see figure 3.1
above).
Click (once) on the chart title and then select the text. Beware: double-clicking
immediately on the title object brings you to another option. You can now change
the chart title: for example type ‘Revenue Lunchroom and Pub’.

But probably you’d prefer that the chart title would be taken from your calculation model,
in our example the label in cell A1. This can be arranged as follows:

• Select the chart title as an object (so click it once).

• In the formula bar, type a equal sign.
• Click cell A1 (where the title is).
• Enter this cell reference using the check button of the formula bar (you may use
[Enter] as well).

15
EXCEL 365 CHARTS Formatting charts

Link the contents of cell A1 to the chart title object as described above.

The contents of cell A1 now appears as the title above the column chart: ‘Sales summary’.
Note that the text formatting is not inherited from cell A1 (the characters there are much
larger). To change the font type or size above the chart, you need to select the title box in
the chart area and set the desired features on the Home tab. Once you have selected the
text in the chart element, you can also use the text formatting options in the mini toolbar.

Figure 3.2 Formatting text in the chart elements via the mini toolbar

The shortcut menu (right-click) also shows text options like Font, but the mini toolbar then
contains options that apply to the box itself, not to the text.

Some actions are done more conveniently via the ribbon,

others through the context menu. In this workbook we mainly
use the options of the ribbon, unless there is a (special)
reason for using the context menu. Check now and then,
if an option is also available in the shortcut menu.

Set the text format of the chart title in another (jolly or business style) font with
a 32 points size.
Change the text in cell A1 to: ‘Revenue Lunchroom and Pub’.

To set the format of the chart title, you probably selected the title element as an object,
and then made the changes. Basically, this way you can customize all the chart elements
you want. It is also often possible to double-click on an element, especially for formatting
issues. Let’s consider a few more examples.

Double-click on one of the red columns (all four of them will get handles in the
corners to show that they are selected). The Format Data Series panel appears on
the right side of your window.
Click on the paint bucket and in the Fill category, set a solid color yellow for the
columns (you may also choose a slightly less bright variation).
Make the other columns red (idem).

16
EXCEL 365 CHARTS Formatting charts

Figure 3.3 Move the legend

Select the legend (it gets handles) and move it to the grid of the chart area (see
figure 3.3 above). The window pane at the right changes its name: it is now called
Format legend (should you have closed the pane: double-click the legend to reopen it).
In this window pane set a solid border around the legend and make sure that the
grid is not visible in the background (for example, by setting white as the filling
color for the legend area). An (outer) shadow for the element is also compelling,
especially in a 3-D chart like this, just try it.

One of the formatting settings is about the legend overlapping

the chart. Since you don’t need the empty space right next
to the chart anymore to show the legend in, feel free to
uncheck that option.

In your chart you don’t need the axis title elements, because you can clearly see what has
been plotted on the axes: euros and quarters. We’ll discuss axes later, but for now we remove
the axis titles.

Select the axis title elements as object and press the [Delete] key to remove them.

Note that these actions cause the chart area to be adjusted

so that your chart is displayed optimally. The program draws
the chart in the plot area – which is also an element within
the chart area – that is automatically updated when things
change. You could also select that area yourself and adjust
the size manually.

Now close the model (save it, you’ll need it later on).

17
EXCEL 365 CHARTS Formatting charts

3.3 CUSTOMIZING
Now let’s design a new calculation model to demonstrate some other customizing issues,
such as the effects of your choices and adjustments and (more) formatting.

In a new workbook, create the model as shown in figure 3.4 below. Set the correct
formatting (currency symbols, decimals, borders, text formatting, et cetera), use
formulas where necessary, and copy them whenever possible.
Save the model with the workbook name Lunchroom January (do not close it).

Figure 3.4 Monthly sales for the lunchroom

Select the entire model (range A1:D9), and then create a 3-D Clustered Column
chart (not the ‘stacked’ variant).

What you see now in the chart is probably not what you want. What is the point of
plotting the unit prices, the quantities and the totals all in the same chart... Therefore, we
will change the source data of the chart.

Make sure the chart is selected.

On the Chart Design tab in the Data group, click the Select Data button.

18
EXCEL 365 CHARTS Formatting charts

Figure 3.5 Delete Data Series

In the Select Data Source dialog, Remove the series Quantity and Price/unit: only
one series remains. In the background – if necessary, move the data window – you
see that the series also disappear from the chart and from the legend.
Now swap the rows and columns with the [Switch Row/Column] button. This gives
all menu items (and the total) their own colour.

For switching, the Chart Design tab also has a button (look in the ribbon), but it is not
available now because the Select Data Source dialog box is open. Note that this action also
changed the contents of the legend and the X-axis of the chart.

We still have to delete the total from the chart, because you don’t want to see 200% revenue
in the chart, of course. You can accomplish this in various ways:

• select the Chart data range at the top of the Select Data Source window and
modify the reference (in two places because the data range has now become a
multi-selection); or
• From the dialog, reselect the desired range as a multi-selection; that is pretty
tricky, but you can temporarily minimise the window (with the button at the
right end of the box); or
• If the Select Data Source window is closed, you can resize one of the two
highlighted ranges with colored borders in the model (the other one changes
simultaneously).

19
EXCEL 365 CHARTS Formatting charts

Adjust the data range of the chart – in a way of your choice – to the desired multi-
selection (A1:A8,D1:D8).

The last mentioned method of modifying the range is by far the simplest, so probably you
will have chosen it. It is similar to adjusting ranges in formulas.

If the Select Data Source window is still open, close it.

On the Chart Design tab in the Chart Styles group, choose a different style. On the
right side of the group there are some buttons to scroll through the styles or to
show them all. In figure 3.6 below we chose Style 8 with the legend at the bottom.

Figure 3.6 Chart Style

Probably now you will understand why a picture is worth a thousand numbers: at once you
can see in the chart what menu item has produced most of the turnover in this month: the
salmon wraps. In the data model you’ll have to look closely to come to the same conclusion.
If you embed only the data table (and not the chart) in a presentation, your spectators will
soon be staring at the numbers.

3.4 FORMATTING RISKS

The many formatting options in Excel allow you to create the most beautiful charts... But
there’s also the risk that the chart type or view you have chosen, does not show adequately
or correctly what you mean. In figure 3.6 above for example, the choice for a 3-D chart
gives a somewhat distorted view: because of the grid lines in the background it seems as if
the revenue for the salmon wraps is about £ 4,000, but that is not the case at all. See the
actual figures in the model.

20
EXCEL 365 CHARTS Formatting charts

To avoid any misunderstanding, you could place the totals next to the the columns as data
labels. Because in the chart the rows and columns are switched, you have to do that for
each column. In the original chart this could have been done in one go.

Switch rows and columns again (all totals now get the same color, and the menu
items are plotted on the X-axis again).
On the Chart Design tab in the Chart Layouts group, click the Add Chart Element
button, and choose from the Data Labels option Data Callout. Selecting the data
(series) is not necessary here.

Figure 3.7 Callout labels

In figure 3.7 we resized the chart object so you can see all labels clearly. And you already
know how to give each column its own color, so we will not elaborate on that.

Choosing a particular chart type plays an important role when presenting your numbers in
a chart. For instance, you may not use a line chart to present this data.

Select your chart, and choose – via the Change Chart Type button on the Chart
Design tab - a line chart (if you like with data markers). What exactly is this line
telling you?

21
EXCEL 365 CHARTS Formatting charts

Figure 3.8 Meaningless line chart

Well actually... nothing. You cannot conclude on the basis of this line chart that there is a
rising turnover in the lunchroom. And to what exactly does an intermediate point on the
line corresponds on the X- or Y-axis? Impossible to say!

Also the 200% issue we mentioned (and corrected) before – the total plus the underlying
numbers in a single chart – involves a certain risk that your data will not be properly displayed
in a chart. Many complex chart types such as Radar or Bubble charts often require a certain
design of your model. For example, a Pie chart requires that the values together represent
100%. It means that there can only be one data series. Let’s take a closer look at that.

Now change the chart type to a 3-D Pie.

The pie is drawn on the basis of the same data and is labelled with the callouts. In this case,
it is correct because the source data already consists of one series: see the multi-selection
(two framed ranges) in your model. But if you plot the chart from the beginning, Excel
does not use the right set of data.

Select cells A1:D8 in your model.

On the Insert tab, create the same 3-D Pie based on this range.

22
EXCEL 365 CHARTS Formatting charts

Figure 3.9 New pie chart

The model consists of several series, but they cannot all be displayed in one pie chart.
Excel then decides to use the first series next to the category labels (menu items) as a basis
to draw the chart, in this case the prices per unit in column B. But that, of course, is no
useful information.

Place both charts side by side, in approximately the same size.

Adjust the value range (using the handles of the blue frame in the model) to only
column D with the totals.
Except for the data labels, the charts are now more or less the same.
Use the Charts Elements button next to the right chart (the one with the [+] on it,
see also figure 3.10) to turn off the legend.
On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element
button, and then hover over all Data Labels options, showing a preview in the chart.
Now click the More Data Label Options at the bottom of the list. A window pane
will appear on the right side: set the Label Options to show the Category Name and
Percentage in the Outside End position of the pie chart.

23
EXCEL 365 CHARTS Formatting charts

Figure 3.10 Labels and percentages at the outside end

Select the left chart. Change the data range for this chart: drag the blue frame in
the model from the Total Sales to the Quantity column.
Display the names and percentages here as shown in the other chart.
Study all options in the window pane on the right carefully, also in the various
categories at the top, where all formatting options for the chart, labels, texts, et
cetera can be found.
Use the 3-D Rotation option of the chart area (you’ll find this option in the context
menu too) to tilt both charts in the same way: set the rotation relative to the Y-axis
to 30°.

The percentages in the two charts are different for the same menu items. When it comes
to quantity, the Egg & Cheese Muffins appear to be the most popular product, but when
it comes to sales, the Salmon Wraps contribute more to the total revenue. Let’s drag the
points from the pie to emphasize this conclusion.

Figure 3.11 Highlighting a category

24
EXCEL 365 CHARTS Formatting charts

Click once in the chart, and then once more in the pie point (the handles of the
point mark that it is the only one selected), and drag it out of the pie. If necessary,
adjust both charts as shown in figure 3.11.

Beware: for dragging only one point form the pie, that
one must be selected. When all points are selected, they
all spread out! And that may look like an attractive cheese
platter, but it’s probably not what you want to show.

Figure 3.12 All points spread out

25
EXCEL 365 CHARTS Formatting charts

As an alternative to two pies for the two data series, you could also use a Doughnut Chart
to display both percentage distributions in a single chart. This type of chart allows you to
show several series of data summed up to 100%.
Whether this gives a good overview? Try to make the chart design as shown in figure 3.13
and judge for yourself.

Figure 3.13 Doughnut Chart

As you will understand, it is important to choose the right chart types, formatting, settings
and views to show what you mean. Your charts may look beautiful, but without meaning
they will be useless.

If you made a third chart object like the one in figure 3.13, delete it.
Save and close the workbook.

3.5 AXES
The layout of charts must have elements that indicate what your figures mean or represent.
Especially the axes labels are important chart elements to explain the data: what is plotted
where and against what?

26
EXCEL 365 CHARTS Formatting charts

Reopen the workbook Sales Summary.

You see clearly on the axes that this chart is about the sales in four quarters. The quarter
labels can be found below the X-axis, a scale in thousands of euros along the Y-axis. In
the 3-D design of the chart there are also gridlines in the background so you can roughly
estimate the amounts. To show the exact figures in the chart you may add the data labels
as we did before in the exercises above.

If the figures are high and pretty close to each other, this view is not always the most wanted
one, as you can barely see the differences. But there is a nice solution for that.

Save the model with the name Lunch Bar, but do not close it: we’ll use this
workbook for some experiments.
Now adjust the amounts in the model as shown in figure 3.14 below. While
editing the numbers you will see that Excel is already adjusting your chart to the
amounts: as soon the editing is completed the scale on the Y-axis – originally from
£0 - £50,000 – now shows a range of £37,200 - £39,200.

Figure 3.14 Scale adjusted

The program has provided the solution this time and automatically rescaled the Y-axis. You
can also adjust this yourself via the Axis Options in the right window pane.

27
EXCEL 365 CHARTS Formatting charts

Select the chart, and from the Chart Elements button next to it, activate the Format
Axis window pane (click the arrow button behind the Axes option, and choose
More Options).

Figure 3.15 More Axis Options

In the chart, select the vertical axis with the amounts.

You can also open or display the appropriate format window

pane on the right by right-clicking the element in the chart:
in this case for the Y-axis, but it applies to all chart elements.

Figure 3.16 Format Axis pane

28
EXCEL 365 CHARTS Formatting charts

Most options are set automatically, which is why Excel adjusted the value scale for you
in the previous exercise. From figure 3.16 above you’ll understand that you can shape the
axes as you like.

Set the minimum value (Bound) for the axis to begin at zero.
View the result.
Undo the setting, because that, of course, was exactly what we didn’t want to see.

Study the possibilities and options in the Format Axis window pane carefully, because they
can be very useful to customise your design to the right and clear charts. We’ll come back
to the axes later in this ebook.

3.6 TIPS
Finally in this chapter we have some tips for you for adequate designing and format beautiful
charts.

• You can resize the chart object on the worksheet by dragging the handles of the
selected chart, for instance, to enlarge it for more accurate editing. You may also
place your chart on a separate (new) worksheet in the workbook. This gives you
a lot of space for designing and formatting the chart.

Select the chart.

On the Chart Design tab, click the Move Chart button at the far right.
Now place the chart in its own (new) sheet. This is automatically called Chart1 by
Excel, but you can also think of a name yourself.

Figure 3.17 Chart as an object or on a separate sheet

29
EXCEL 365 CHARTS Formatting charts

Relocate the chart to an object on Sheet1. Enlarge the object – if necessary – to

clearly display all chart elements.

• Certain settings or adjustments can make the chart smaller than you’d like, and
as a result, for example, it does no longer fill the object frame completely. Or
in other cases you may need space to emphasize specific elements outside the
chart. For this you can manually resize a chart element using the handles (if the
element is selected) or move it within the chart area. You can also select the Plot
Area and use its handles to in- or decrease the space for the chart within the
object frame.

Figure 3.18 Resize the Plot Area

• Unless you already know exactly what the best view is for your chart, it’s a good
idea to view multiple chart types – also in the category Recommended Charts –
whether they show adequately what you mean with your calculation model (and
the chart). And not only the main types, also all kinds of subtypes could satisfy
your needs.

There are so many possibilities and options to create and format charts that we cannot
discuss them all here. But, of course, you can experiment as much as you like until you
have found the most beautiful or suitable design.

30
EXCEL 365 CHARTS Chart templates

4 CHART TEMPLATES

4.1 INTRODUCTION
Once you have created the most beautiful or suitable chart to present your figures, you can
save the design as a chart template. Then, the next time you want to create a similar chart,
you can use the template to apply all the settings at once. This time-saving possibility –
introduced with the Excel 2007 version – is quite simple to use.

4.2 CREATING A TEMPLATE

You probably already know what a template is: a kind of model as a base for other (new)
products. For example, in a document template you can include a lot of formatting, styles,
images, and/or text that will be available when you start a new document based on that
template. Also (new) calculation models can be based on a template which includes certain
objects, formatting or settings, so you don’t have to (re)set everything in each new workbook.
In most cases, you can apply a template to a new or existing document afterwards and
transfer the elements or settings. In this way it is more or less similar to applying a theme.

Similar to templates for documents, presentations or workbooks, templates can created for
the layout of charts as well. We’ll briefly demonstrate it below.

Open the workbook Lunchroom January.

Select one of the charts (which are roughly the same in formatting, aren’t they?).

Figure 4.1 Saving as template

31
EXCEL 365 CHARTS Chart templates

Save the pie chart format as a template from the context menu, with the file name
Pie chart.

Figure 4.2 Saving as a chart template

32
EXCEL 365 CHARTS Chart templates

Excel gives the file the extension .crtx and places it in a special reserved folder. See the
figure above: we made the extensions of files in Windows Explorer visible, and you can see
the folder name and location in the address bar of the window.

Close the workbook.

4.3 APPLYING A TEMPLATE

In the following example, we will once again use the Sales Summary model, now for
creating a new chart based on the newly saved chart template.

Open the Sales Summary workbook (that’s the original model with the ‘raw’ sales
figures, so not the file that you saved as Lunch Bar).
Select cell range A2:E3 (see figure 4.3 below) with the quarterly figures of the
lunchroom.

Figure 4.3 Apply your template

On the Insert tab, click the dialog launcher button in the lower-right corner of the
Charts group to open the Insert Chart window (you may also use the Recommended
Charts button).
Activate the All Charts page of the window.
Open the Templates folder: there is your saved template. When you point to it,
Excel shows a preview for the current situation.
Double-click the template (or click and close the window with [OK]) to apply it
to the selected range.

That’s all: as you can see your new chart inherits all the formatting and layout from the
template. That has saved you a lot of setting and layouting.

33
EXCEL 365 CHARTS Chart templates

Create another new chart based on the template, but now for the Pub. Beware: you
must multi-select the labels in row 2 (range A2:E2) including the empty cell A2,
and the data including the label Pub in row 4 (A4: E4) – see figure 4.4 below – to
display all labels correctly.

Figure 4.4 Multi-selection to get it right

4.4 EXERCISE
Delete the Column Chart.
Place the two pie charts next to each other under the data model. If you select
both charts (with the [Shift] key pressed), you can arrange them with the Align
button on the Shape Format tab.

Figure 4.5 Aligning two charts

Emphasize in both charts which quarter has the highest contribution to the revenue
by dragging the relevant pie points out as shown in the figure above.
Close the workbook (you may save it, but preferably under a different name).

So, now you know how to create charts, how to format them, and even how to save them
as a template to transfer all the attributes to other charts you want to create. But Excel has
much more for you in store as you will see in the following chapters.

34
EXCEL 365 CHARTS Combined charts

5 COMBINED CHARTS

5.1 INTRODUCTION
A special option when designing charts is combining data series of a different nature, sort
or quantity in one graphical presentation, using different chart types and elements. A well-
known example that we demonstrate in this module is a combined column and line chart.
But other combinations are also possible, although sometimes they lead to strange results.

5.2 SERIES
In the example in this chapter, the starting point is an overview of a car dealer who wants
to show the last years sales and the average selling price of the cars in one chart. With this
presentation he wants answers for strategical and/or operational questions such as:

• When is the best or worst sales period?

• Is there a trend in the average prices during the year?
• In what month are buyers willing to pay most on average for a car?

Create the data model as shown below in a new worksheet. To create the month
labels use the fill handle: type January in cell A2, and then drag the handle of that
cell down to fill the cells with the next months.

Figure 5.1 Car Sales model

35
EXCEL 365 CHARTS Combined charts

In this model the two data series are quite different from each other, not only in sort
(quantity and price), but also in terms of the size of the numbers. The number of cars sold
sum up to a few dozen, and the average price lies around £15,000. If you put both data
series together in one chart, it will result in a strange picture.

Select all cells of the model (A1:C13).

On the worksheet, insert a Clustered Column chart (2-D) .

Figure 5.2 Column chart with widely varying data series

If you stretch the chart object vertically (as we did in figure 5.2, you may try it as well), at
the bottom you will see a few dashes that in fact are the columns for the number of sold
cars. And whichever chart type you select, the two data sets are so different (in terms of
quantity) that the result will never be to your satisfaction. A nice solution could be if the
number of sold cars would be plotted in the chart with a different scale.

Undo (if necessary) the resizing of the chart object.

Now resize the chart to cover a considerable part of the worksheet, without
overlapping the data model.
On the Format tab, in the Current Selection group, select from the Chart Elements
drop-down list the Series “Sold Cars”.

Figure 5.3 Select Series as a chart element

36
EXCEL 365 CHARTS Combined charts

Besides via the Chart Element list box (or in the Format
Data Series pane), you can also easily select a series by
clicking one of the data elements in the chart itself. This
way all elements of the series are selected. But in this case
that is virtually impossible, since the data series is not (or
barely) visible in the chart area.

In the same group, click the Format Selection button.

In the Format Data Series pane, under Series Options, set the series to be plotted
on a secondary axis.

Figure 5.4 Add Secondary Axis

On the right side of the chart area, a second scale has appeared with different values: from
0 to 35. In the chart you now see larger columns for the sold cars numbers. They (partially)
overlap the columns of the average prices. That’s not very useful, especially not in those
cases where the overlap is complete (if you like, you can customise overlapping and spacing
in the window pane Format Data Series). But moreover, in view of the issue of a possible
trend, it’s preferable to present the average prices with a line rather than with columns. This
can be done by changing the chart type of the Average Price Series.

On the Chart Design tab, open the Change Chart Type window, and on the All
Charts page tab click (if necessary) the Combo category.

37
EXCEL 365 CHARTS Combined charts

Figure 5.5 Column - Line chart

Now you can create a (custom) combination of column and line chart by assigning
the different types independently to the series with the list boxes. Check or uncheck
the box to the right of the series to indicate whether the values should be plotted
on a Secondary Axis or not. If the preview is okay, close the window with [OK].

In the resulting chart, you can see how the average selling price developed in the whole
year, and at the same time in which months most cars were sold (April and October). But is
there a trend in the average prices? It’s not very clear to see whether it gets better or worse
in the course of the year. Furthermore, it seems that the summer period is the worst time
to sell cars, but the average price in some of the summer months was quite high. Maybe a
lot of convertibles were sold then?
How to interpret the presented data in the charts or what conclusions may be drawn from
them, is a different story. For example, one car dealer will conclude he has to advertise a
lot in the ‘bad’ selling period, while another would prefer to do so in the period when a
lot is sold: apparently then his visitors seem to feel like buying.

38
EXCEL 365 CHARTS Combined charts

5.3 STRANGE IMAGES

In Excel, you can create quite a few combinations of different chart types for different
series, but not everything is possible. Therefore, some chart types are not listed in the
boxes. Compare the list of all charts on the left with the types you may choose for you
combinations: you will not find types like Sunburst, Funnel or Waterfall.

Moreover, whether certain combinations actually say anything or result in useful overviews,
is sometimes a matter of taste, and also a question of ‘wrong’ type choices. We have already
discussed some of these risks in the previous chapter.

Study the charts below with all kinds of combinations. What do you think: could
they be of any use (in some cases)?

Figure 5.6 Combo Column and Bar

39
EXCEL 365 CHARTS Combined charts

Figure 5.7 Combo Pie and Scatter

Figure 5.8 Combo Area and Line (with markers)

First, save your workbook with the Column/Line chart as shown in figure 5.5, as
workbook Car Sales. Then make a copy of the workbook to exercise and experiment
with the combinations. Try to create the chart combinations above.

40
EXCEL 365 CHARTS Combined charts

5.4 FORMATTING
Especially in combinations of types the overall formatting of the charts is very important.
When using two Y-axes, for instance, to which plotted data series do the gridlines belong,
or can we use ticks on the axes to clarify the values?

The chart tools on the Chart Design tab include a Quick Layout button to choose a predefined
layout for your chart. It applies or places a legend, a data table, various titles, or certain
formatting elements in the chart. In the next exercises we will do some formatting manually,
because that way you’ll encounter more possibilities.

5.4.1 GRIDLINES

In figure 5.5 the amounts are plotted on the left and the quantity numbers on the axis on
the right. The gridlines in the background belong to the left scale: you can see this clearly
if you select the grid or display the tick marks for this axis.

Close your experiment file and reopen the workbook with the Column/Line chart
(Car Sales), or undo all experimenting in your current file until the chart object
looks like the Combo as shown in figure 5.9.
Click one of the (major) gridlines so that they are all selected. When necessary, open
the Format Major Gridlines pane (via right-clicking the gridlines: Format Gridlines).

Figure 5.9 Primary Gridlines

The selection handles on the left side of the chart are exactly placed next to the amounts,
but as you can see the numbers on the right do not correspond with the gridline handles.

41
EXCEL 365 CHARTS Combined charts

In the window pane on the right, choose the Secondary Vertical (Value) Axis (in
the chart object it will be selected) and add the major gridlines (via the Add Chart
Element button) for this extra Y-axis. Give both sets of gridlines (select in the chart)
their own color as shown in the figure below.

Figure 5.10 Double Gridlines

Despite of this action, reading the data has not become easier, maybe rather trickier. Let
us think of a different solution.

Remove the Major Gridlines for both axes (select them and press [Delete]). This
leaves the background empty.
In the window pane set major tick marks (pointing outside) along both axes, and
via the Add Chart Element button display– in a light color – the minor gridlines
only of one of the series.

Many users prefer this view because it’s easier to see which values correspond to the plotted
line (marks) or columns. And even more is possible: see in figure 5.11, for example, how
we have limited the bound of the right scale, and added a title and background image.

42
EXCEL 365 CHARTS Combined charts

Figure 5.11 Easier reading of values

Add an appropriate title for the chart, for example: Car Sales this year (you may
also insert the current year, of course), and figure out how to add a nice picture
in the background.
For now, remove the background image again, because we want to show you yet
another formatting aspect.

5.4.2 AXIS TITLES

Especially on the secondary Y-axis it is useful to know what the numbers stand for: we
therefore insert an axis title.

On the right side of the chart, add the axis title Number of Cars appear (using the
[+] is the handy here). Choose your own design (we increased the font size slightly
and gave the text a gray color, see figure 5.13 below).

At the bottom along the X-axis the months are plotted, but we don’t need an axis title
there. Also on the left side of the chart there is no axis title needed, because the notation
of the numbers tells us that we’re looking at amounts of money. But the title object may
come in handy when using a different notation for the axis.

43
EXCEL 365 CHARTS Combined charts

Figure 5.12 Thousands of views on the axis

On the primary vertical axis now display the amounts in thousands (via the Axis
Options of the Format Axis pane). The numbers on the left Y-axis then lose the last
three zeros and an axis label Thousands appears next to it.
Under Number, in the Currency Category, delete the currency symbol of the axis labels.
Change the text of the axis title box to Pounds x 1,000 and give that text box the
same formatting as the other axis title.
Relocate and/or remove the axis title objects to the situation as shown in figure 5.13.

44
EXCEL 365 CHARTS Combined charts

Figure 5.13 A suitable Column-Line chart

If all went well, your chart will look something like figure 5.13. And that’s a nice looking
and professional chart, isn’t it?

Save your model again (you do not need to close it if you’re going to proceed with
the next chapter now).

45
EXCEL 365 CHARTS Trendlines

6 TRENDLINES

6.1 INTRODUCTION
To discover or predict current and future developments is tricky, but your calculation models
can help a bit. It is quite difficult to see decreasing or rising trends in the bare figures,
and even more awkward when the number of figures involved is large. With a few simple
examples, we demonstrate in this chapter how your models and charts can help you when
thinking about the future.

6.2 DISCOVER TRENDS

In the previous module, we created a model in which the car sales in one year and the
average selling price were combined in one chart. We’ll use this model now to see if you
can discover trends in it.

Open the Car Sales workbook with the Column/Line Combo chart, if necessary.

Take a look at the red line in the chart of the average selling prices. Have these prices on
average risen in the course of the year or did they get lower, what do you think? Let’s see
what Excel can tell us.

In the chart, select the line of the Average Price.

Use the Chart Elements button to add a linear trendline .

Figure 6.1 Inserting a linear trendline

46
EXCEL 365 CHARTS Trendlines

A slightly declining dotted line appears. So in this case, you may conclude that the average
selling prices on average are going down. We can also display this downward trend more
clearly in the figure.

Figure 6.2 Formatting the trendline

Right-click the trendline and choose the Format Trendline option in the context menu.
In the right window pane, set a green color, a width of 2 points and a larger dash
type for the trendline (something like in figure 6.2)

And what do you think of the numbers of sold cars? Is there a rising or declining trend in
the whole year? This is of course quite difficult to identify with such big differences between
months, and with data plotted as columns. Try to make your own analysis first, before you
let Excel show the trend.

47
EXCEL 365 CHARTS Trendlines

Now, let’s project a trendline in the chart for the series of Sold Cars as well. Give
it a distinctive formatting, for instance, a thick dash line, this time in black.

And... did you see that coming in terms of ascending or descending? In the columns in
the chart (without the trendline plotted) you might have discovered it, but look again at
the amounts in the data table: it is hard to discover any trend in them, neither ascending
or descending, don’t you think?

Figure 6.3 Trendlines for both series

Close and save this workbook as Car Sales (trends).

6.3 PREDICTIONS
Discovering trends is one thing, but predicting is a whole different story. However, Excel
may also contribute to predictions using trendlines.

In a new (blank) worksheet, create the model as shown in figure 6.4 below. You
can think of the formulas needed for calculating profit or loss (in cells B4:E4)
and the annual totals (column F) yourself. And you also know to format things
by now, of course.

48
EXCEL 365 CHARTS Trendlines

Figure 6.4 A quarterly balance

Select cell range A1:E3. Using the Quick Analysis button in the lower right corner
of the selection, create a Clustered Column chart (2-D) of the Revenue and Cost.

First question: will there be overall profit in this year? Answer is of course: yes, because
you can easily see this in the Total data column. Next question: is there an ascending or
descending trend in Revenue? That is more difficult to answer, even after studying the chart.
And the same goes for the Cost.

Figure 6.5 Column chart for Revenue and Cost

But fortunately, Excel can come to our aid for answering these last questions by plotting
two trendlines in the chart.

49
EXCEL 365 CHARTS Trendlines

Right-click one of the Revenue column (so they’re all selected), and from the context
menu, choose Add Trendline.
In the Format Trendline window on the right, try out under Trendline Options what
different types of trendline could be plotted in the chart (Exponential, linear, et
cetera). Finish with a Linear trendline.
Next, insert a linear trendline for the Cost in the chart as well.

Figure 6.6 Two descending trendlines

The trendlines for both revenue and cost are descending, which of course for the cost is
very useful, but less pleasant for the revenue. And how will this trend develop in future?
To make a prediction for next year, you can stretch the trendline in the numbers to the
next periods as follows.

Select the trendline of the Revenue. If the window pane on the right does not
appear or isn’t there already, open it by right-clicking the trendline, and choosing
Format Trendline.
In the Trendline Options under Forecast, set 4 quarters (periods) forward to be
displayed.
Select the trendline for the Cost and set the Forecast accordingly.
Resize - if necessary, but probably Excel does it for you – the chart object so that
there is enough space for displaying the trendlines and all labels are visible.
Use the Chart Elements ([+]) button to display the Primary Major Vertical Gridlines
for better identification of the next (4) periods.

50
EXCEL 365 CHARTS Trendlines

Figure 6.7 Predictive trends

It is not difficult to see that the trend in both cases remains descending over the future
periods. But also is clear that the costs trendline will soon cross the revenue line. And this
crossing of the trendlines means that profit changes to loss. And considering the current
trend this will happen already in the first next quarter!

This ‘prediction’ may be just in time to act energetically: for example, deploying more
sales reps to generate more revenue, or make cuts in the cost. In both cases – or with a
combination of measures – you might prevent the eventual loss. Therefore, the trendlines
might be golden information for your business!

6.4 EXERCISES
In the model, change some values of the revenue and/or cost data so that the
trendlines no longer intersect. Make sure the lines continue to descend.
Now increase the number of time periods to forecast. Check if they are going to
cross somewhere in the future.
Make the number of periods in the Forecast so large that the trendlines will cross
the X-axis. Is this still a prediction?

Reopen your workbook with the Car Sales.

Show in the chart when the trendlines of the quantity of sold cars and of the average
selling price are going to intersect. What can you conclude from this?

No more and no less than that the sales will continue to rise at this trend, and that the
average selling price will go on declining. And, of course, that the title of the chart is no
longer appropriate.

51
EXCEL 365 CHARTS Sparklines

7 SPARKLINES

7.1 INTRODUCTION
A very special phenomenon in Excel are Sparklines, small charts that show trends or patterns
in your data. Or actually next to your data, since they do not appear in a chart object, but
in a cell, or in a range of cells close to the values of which you want to monitor patterns
or trends. In this context, we’ll discuss also some other special formatting.

7.2 DEPENDENCY
Using Sparklines as a visualization of your data is considered a form of conditional formatting,
because the graph in the cell depends on the values in the data model. But in this case,
those values are not present in the cell itself, but in a nearby range of cells. An example
may clarify this. Let’s create a simple model to register the number of miles that employees
drive per month.

In a new (blank) workbook create the model as shown in figure 7.1 below.

Figure 7.1 Mileage model

Activate cell F3.

On the Insert tab, in the Sparklines group, click the Line Sparkline button.
In the Create Sparklines dialog, select the Data Range for the desired sparkline, so
the range of monthly mileages of Willy in the cells B3:D3.
Set – if necessary (the box will already show your selected cell $F$3) – the Location
Range where the sparkline should appear.

52
EXCEL 365 CHARTS Sparklines

Figure 7.2 Insert Sparkline

Click [OK] and see the trendline appear in cell F3, and also the (extra) tab Sparkline
in the ribbon. Study all available options there.
Check in group Show the option to show Markers: they appear in the sparkline
for each of the three months.

Figure 7.3 Sparkline with markers

As the sparkline shows, the number of registered miles for Willy has an ascending trend
in the first three months of the year. We’ll be discussing more formatting of the sparklines
later, now we’re going to extend this model for the mileage registration of some colleagues.

In cell A2, type the label text Name and in F2 the labeled Trends.
Add some persons with their numbers of travelled miles, and at the bottom add
a total row as well. As an example you may use the data (and formatting) from
figure 7.4 below.

53
EXCEL 365 CHARTS Sparklines

Figure 7.4 Fill down all trendlines

In the cell range F3:F9, create the sparklines for the added persons and the total
row using... Fill Down (preferably with the fill handle).

7.2.1 CUSTOMIZE

As with regular charts, sparklines are adjusted immediately as the cell contents (values)
they’re based on change.

Change some of the mileages in the model and see the sparklines change accordingly.
Undo the changes (so you can follow all steps and actions in the exercises below).
Save the model (do not close) with the name Mileage Registration.

7.2.2 CLEAR

For some reason (that Microsoft thought of ), you can only clear sparklines (whether or not
in a group or range) by clicking the Clear button in the Group group of the Sparkline tab.
Strangely enough, erasing (a range of ) sparklines pressing the [Delete] key does not work.

Figure 7.5 Delete sparks

54
EXCEL 365 CHARTS Sparklines

But you are no longer completely inexperienced in working with spreadsheets, so you may
have already thought of some alternative methods yourself: like undoing the creation of the
sparks, or deleting the entire column where the sparks are plotted.

7.3 DATA TABLE

As you probably noticed, between the last month and the Trends we left a column unused.
In the next exercise we are going to create a data table of the model, which has certain
advantages when using sparklines.

Select the data in cell range A2:D9, including labels and totals.
On the Insert tab, in the Tables group, click the Table button.

Figure 7.6 Creating a data table

In the Create Table dialog box, check the settings to create the table, and confirm
by clicking [OK].

Working the other way around, you could have clicked the
Table button first, and then specify the location of the data
and check the presence of headers in the Create Table
dialog. But that’s more work, and the result is the same.

The table now gets a special layout (see figure 7.7 below). This is one of the included
formatting styles for data tables that you can also find – or change, if you like – in the
Table Design tab.

55
EXCEL 365 CHARTS Sparklines

In cell E2, type the label for the next month: April. Finishing the input with [Enter],
you see that this label and the cells below immediately get the correct formatting
in the data table.
Now enter the monthly mileage for Willy in April and see that the sparkline is
immediately expanded.

Figure 7.7 Adding data

Please complete the table with some similar random numbers. Don’t forget to add
the formula for the total at the bottom (copy it from the cell next to with the fill
handle, this includes the border formatting).
Also, insert a column to the right of April, to make space for the next month.

The sparklines give a nice and quick overview of the mileages from the employees and
whether they are de- or increasing over the months. And you can also see in the total
mileage spark if there is a specific trend for the whole organisation.

Not only is using a data table for the registration useful for the consistently formatting of
(new) columns or rows, there is also another important advantage: the option in data tables
to sort, select or filter the data. In each column of the table there is a button for this in
the right corner of the label cells.

Filter the data in the table using the Name selection button. Select two of the
employees, and also the Total.

56
EXCEL 365 CHARTS Sparklines

Figure 7.8 Filtering data

Now make all people reappear in the table (Select all).

We do not elaborate further on this here. Working with data tables is a special and very
useful functionality in Excel. In our ebook on advanced topics in Excel, data tables are
discussed in more detail.

7.4 SPECIAL FORMATTING

By emphasizing the highest or lowest values in the sparks, you can easily discover peaks
and troughs in the trendlines.

Select one of the sparklines (this also marks the entire range where the trendlines
are plotted with a frame).
On the Sparkline tab in the Show group, uncheck the Markers option, and then
check High Point.

57
EXCEL 365 CHARTS Sparklines

Figure 7.9 Highlighting the peaks

To change the color of the marking points (high, low,

negative, et cetera), just use the Marker Color option in
the Style group of the Sparkline tab.

In several ways you can format the sparklines with beautiful results, but keep in mind that
the main purpose of it must be, of course, to clarify what you mean.

7.5 OTHER SPARKS

There are also two other types of sparklines: Column Sparklines and Win/Loss Sparklines,
each with a variety of styles and options to set.

Select one of the sparklines again.

On the Sparkline tab in the Type group, convert the sparks to a Column Sparkline.
This sparkline type may even show more clearly who, when and where is the highest
user in the mileage table.

Figure 7.10 Column sparklines

58
EXCEL 365 CHARTS Sparklines

Now change the type to Win/Loss. That’s probably not a very desirable view in this
case, but you can choose it, of course, when useful.
Reset the sparks to Line-sparks with marking the high points.
Save the workbook.

Column sparkline?

The types of sparklines in Excel are called Line, Column,

and Win/Loss sparklines. But let’s be honest, only the Line
variant is really a line, a term like Column Sparkline therefore
is not very adequate, almost a ‘contradiction in termini’.
That’s why we prefer to call all these mini charts ‘sparks’
in stead of ‘sparklines’.
Originally, the term ‘sparkline’ was an alternative to ‘inline
chart’ used for line charts the size of the surrounding text.
You often see those in stock market reports or results.

7.6 EXERCISE
You’ll find some more special formatting features in the Sparkline tab, such as adding axes
for sparklines or grouping, and the possibility to edit the data. In the exercise below we
have a few more ‘tips and tricks’ for you.

Enter in cell H2 the label Total Trend, and make the column (auto)fit this text.
Merge the cells H3:H9 (with the Merge & Center button in the Alignment group
of the Home tab).
Select the cell range B9:F9 with the totals (be sure to include that last cell, even
though the month May is not added yet).
On the Insert tab in the Sparklines group, choose a Column Sparkline. In the Create
Sparklines dialog, set the Location Range to the merged cells (by clicking).

Figure 7.11 Total spark

59
EXCEL 365 CHARTS Sparklines

What you get to see now is, in fact, only the tops of the columns, because the smallest value
in the row is considered the minimum of the imaginary (vertical) axis on which the columns
for the totals are plotted. By setting the vertical axis differently you will get a ‘better’ chart.

Make sure the Total sparkline is selected.

In the Sparkline tab, in the Group group, click the Axis button.

Figure 7.12 Formatting the Axis of the spark

Under Vertical Axis Minimum Value Options, click Custom Value.

Set the minimum for the (imaginary) axis to 3500. It results in some easier to
compare columns.

Figure 7.13 Setting a minimum for the Column Sparkline

60
EXCEL 365 CHARTS Sparklines

Showing an axis in the sparkline chart does not always lead

to a usable or even visible result. Of course, you can always
try what the effect is in different situations.

You may also combine sparklines with (advanced) Conditional Formatting effects and, for
example, clarifying icon sets. On the internet – searching for instance with ‘Sparklines
Excel’ – you’ll encounter several inspiring examples. The two models shown below may
illustrate a few of the possibilities.

Figure 7.14 Business overview (Microsoft example)

61
EXCEL 365 CHARTS Sparklines

Figure 7.15 Sports results

62
EXCEL 365 CHARTS Appendix

APPENDIX
In the table below a brief description of our ebooks on Excel 365, published by
www.bookboon.com.

Our ebooks (user guides) on spreadsheets in Excel 365

With Excel you can create calculation models, for example,

for financial controlling and accounting, for calculating a tax
Basics -
declaration form, for compiling an annual report, and so on. In
Calculating on the computer
this user guide you’ll learn the basic skills, functions, features
and options of this popular spreadsheet program.

Make your figures more transparent and comprehensible by

presenting them in a chart. You learn how to design them in
Excel, which (combined) types you can choose from, and how
Charts to add the required captions. Further, this ebook discusses
how to discover trends in your figures by drawing trendlines
or sparklines. By using charts your models and figures can get
more predictive power.

Excel has numerous tools to help you designing your models,

checking your formulas, finding solutions or creating scenarios,
and the like. You’ll also learn to fill cells with (own) lists of
Advanced Options
labels or data, to add comments to your models, and to create
and Techniques
planning schedules. Furthermore, some advanced topics are
discussed such as (external) references, security, and the (saving
in a certain) format of your workbooks.

In a spreadsheet program it is all about designing and building

an appropriate model: what is the purpose of it, what should it
calculate, what do we want to find out, analyse of even predict,
and so on? In this ebook we discuss various (advanced) model
Model Design
designs in Excel such as outlines, consolidations, data tables,
pivot tables, and forecasts. Also, we cover some topics on
searching and filtering in models, and elaborate formatting and
printing in more detail.

With the Visual Basic for Applications programming language

on board in Excel, you can edit recorded macros as you desire,
but also program your own applications, or add additional
VBA Programming functionality. For example, in Excel you could create dialog
boxes to ask questions like ‘What is the starting value for the
calculation?’, ‘Where is the data?’ or ‘What report do you want
to print?’.

63