Formatting Excel Sparklines

Formatting Excel Sparklines

In the last lesson, you saw how to add a Sparkline to a cell. In this lesson, you'll learn how to format your Sparklines.

You can liven up your Sparklines by clicking on the Design ribbon at the top of Excel. You'll then see five panels: Sparkline, Type, Show, Style, and Group. To add markers to your Sparkline, click on the Show panel. Check the box for Markers:

When you check the Markers box, your Sparklines will look like this:

You can change the style of your Sparkline via the Style panel on the Design ribbon:

Expand the Style options by clicking the down arrow in the bottom right:

Select a style that takes your fancy:

Changing the Colour of Sparklines

You can change the colour of your Sparkline by clicking the Sparkline Color option on the Style panel:

You'll then see a dropdown list of colour options:

As well as selecting a colour, you can change the thickness of your Sparkline by clicking the Weight option at the bottom:

The Marker colours can be changed, too. Click on Marker Color, just below Sparkline Color. Expand the Markers option:

Select a colour for your markers. Click More Colors for an expanded colour palette.

You can change the Sparkline type from the Design tab, too. Click on the Type panel, then Column:

Your Sparklines will then look like this:

Click back on the Line option, if you don't like Columns.

When you format the background cell from the Home ribbon, you can really bring your Sparklines to life:

Sparklines give you a nice visual representation of your data. From the Sparklines above, you can quickly see that Lisa is progressing well, and that John's scores are plummeting rapidly. We need a word with this boy!

In the next section, we'll move on to Formulas in excel.

Read More

Sparklines in Microsoft Excel

Sparklines in Microsoft Excel

Sparklines are mini graphs that you can add to cells in your spreadsheet. They were introduced in Excel 2010, and look like this:

The graph in the cell A8 shows the scores that Lisa achieved in her tests. You can quickly see that Lisa's scores are going up all the time, with no dips.

To add Sparklines to your own spreadsheets, start with some data. (You need more than one value, otherwise you'll just have a dot.) In a new Excel spreadsheet, type the following exam scores:

Change the height of row 8 to create more room for the Sparklines. To do this, you can simply move your mouse to just below the number 8 on the left of Excel. When your mouse pointer changes shape, hold down the left mouse button. Keep it held down and drag to a new height.

If you want an exact value for the height, click inside of any cell on row 8. Locate theCells panel on the Home ribbon at the top of Excel:

From the Cells panel, click on Format. From the Format menu, select Row Height:

When you select Row Height, you'll see a small dialogue box appear. Type in a new value for the height and click OK:

Row 8 on your spreadsheet should now look like this:

Adding a Sparkline to a Spreadsheet Cell

To add a Sparkline, click inside of cell A8. Now select the Insert ribbon from the top of Excel. From the Insert ribbon, locate the Sparklines panel:

You can see that there are three options: Line, Column, and Win/Loss. The first two are the most used options. Click on Line and you'll see a dialogue box appear. This one:

The first text box is where you specify your Data Range. Click inside this box and enter A1:A7. For the second text box, Location Range, you specify where on your spreadsheet you want your Sparkline to appear. We want the Sparkline to appear in cell A8. The dollar signs before the A and 8 mean it will be an Absolute cell reference, as opposed to a Relative cell reference. (We go into this in more detail in a later tutorial, here: Absolute Cell References.) If you leave the Location Range box blank then your Sparkline will appear in the currently selected cell.

Your Create Sparklines dialogue box should look like this:

Don't worry if your Location Range text box is blank, though. As long as your Data Range box is filled in you can click OK.

When you do click OK, your A8 cell will look like this:

You can use AutoFill for the other exam scores. Move your mouse pointer to the bottom right of cell A8 until the pointer turns into a black cross. Hold down your left mouse button. Keep it held down and drag to cell D8. You should see all four cells fill with Sparkline charts:

You can liven up your sparklines by added markers and changing line colours. We'll do that in the next part below.

Read More

Extending the Trendline in Microsoft Excel

Extending the Trendline in Microsoft Excel

In the first part of this tutorial you saw how to add a Trendline Chart. The type of Trendline we used was an Linear Regression one.

You can get Excel to extend the linear regression line on your chart. Click on your chart to highlight it. Click on the plus symbol again (Or bring up the Format Trendline dialogue box again, if you're using Excel 2007 and 2010.). Expand the Trendline option and select More Options. From the Trendline Options, have a look at the Forecast boxes. Type a 6 into Forward:

Press the Enter key on your keyboard and you should see the line extend on your chart:

New values have been added to the X-Axis. It now goes from 0 to 14. Trace the vertical line up from 12 until you come to the sloping line. Now trace a straight horizontal line the left, all the way to the Y-Axis, and you can see it reads a value of just above 20000:

If you look in cell B14 of your spreadsheet, you can see that the value is a more precise 20382.56. So Excel is predicting we'll earn this much in 2018.

If you want to add the new values as dots to your chart. Click on one of the dots on your chart. This will highlight all the dots:

The cells B2 to C9 (the data points) will also be highlighted on your spreadsheet:

To add more data points, drag the blue square in the bottom right of cell C9. Drag it down to cell C15. Now drag the purple square in cell B9. Drag it down to cell B15.

Now have a look at your chart. Click outside of the dots to deselect them:

Excel has added the 6 new dots. It has added them all on the line. (They are all on the line because they are predictions, rather than real data.)

And that's it for X Y Scatter charts and linear regression. The above techniques come in quite handy for when you need to show future predictions: predicting revenue streams, predicting future crime statistics, share prices, and lots more besides. If you're looking for work, one thing that may impress an employer is to say that you can do linear progressions on an X Y Scatter chart!

In the next section, we'll have a look at something called Sparklines.

Read More

Predicting future values with Excel Charts

Predicting future values with Excel Charts

Excel can help you make predictions about future values, or help you spot a linear trend. What we'll do in this section is set up something called a Trendline. We'll use an X, Y Scatter chart for this. We'll take a look at future income predictions based on what was earned in previous years. If you're a bit confused, don't worry: it will all become clear as we go along.

Type the following headings into cells A1 to C1:

Year Years since 2006 Income

Format the cells, if you prefer. Your spreadsheet will then look like this:

Enter the years 2006 to 2019 into cells A2 to A15:

As an X axis for our chart, we can have the years since 2006. These values will be used in a later formula. In Cells B2 to B15 enter the values 0 to 13:

We now need some income values for the years 2006 to 2013. This is income that has actually been earned, rather than income that might be earned in the future. We'll then use this hard data to predict future values. Enter some income values, then, into cells C2 to C9. We made up the following values:

We're now ready to insert an X, Y Scatter chart.

Highlight the cells B1 to C9:

This will be the data for our chart.

From the top of Excel, click on the Insert ribbon. From the Charts panel, locate and click on the Scatter charter icon. The icon looks like this:

Select the first item to get a chart with just dots:

(If you can't see the icon above, click on Recommended Charts. Switch to the All Charts tab, then select X Y Scatter).

A new chart will then appear on your spreadsheet. It should look like this:

The figures along the bottom, the X Axis, are our years since 2006. The figures on the Y Axis are our income values. The first dot, the one on the far left, tells us that we made just over 12000 at Year 0, (Year 0 is 2006). At Year 1 (2007) we made just under 16000. At Year 2 (2008) we made just over 14000, and so on.

All these dots seem to form a loose line going up from the left. You could add a line yourself using the Shapes item on the Illustrations panel. What you'll then have done is to create a linear regression.

Rather than add the line ourselves, however, Excel can add the line for us. Not only that, it can give us the formula it used to create the line. We can use that formula to predict future incomes.

Click on your chart to highlight it. You should see three icons appear on the right, in Excel 2013. (See below for Excel 2007 and Excel 2010.) Click on the Plus symbol, and put a check in the box for Trendline:

When you check Trendline, you should see a line appear on your chart:

To get the line in Excel 2007 and 2010, select your chart then click on the Layouttab. From the Analysis panel, click the Trendline option. From the Trendline menu, select Linear Trendline.

The line represents Excel's best fit for a linear regression. It's trying to put as many as the dots as it can as close to the line as possible.

To see the equation Excel used, click on the Plus symbol again (Excel 2013). Then click on the arrow to the right of Trendline. A new menu appears. Select More Options at the bottom:

You should see a panel open on the right of Excel, like the one in the next image.

For Excel 2007 and 2010 users, Click the Layout tab again. Then click theTrendline on the Analysis panel. From the Trendline menu this time, select More Trendline Options. You'll then see a dialogue box with options the same as the ones in the image below.

The Trendline option we've chosen is Linear. Have a look at the bottom, and check the box next to Display Equation on chart.

When you check the box you should the following equation appear on your chart:

y = 564.88x + 13604

This is something called the Slope-Intercept Equation. If you remember you Math lessons from school, the equation is usually written like this (the "b" at the end may be a different letter, depending on where in the world you were taught Math):

y = mx + b

In this formula, the letter "m" is the slope (gradient) of the line, and the letter "b" is the first value on the y axis. The x is a value on the X-Axis. Once you have the slope of the line, a value for the X-Axis, and the starting point of the line, you can extend the line, and work out other values on it. This will be the letter "y" in the equation.

Excel has already worked out two values for us, the "m" and the "b". The "m" (the slope) is 564.88 and the "b" is an income value of 13604.

To work out the y values we just need an "x". The "x" for us will be those "Years since 2006" in our B column.

Click inside cell C10 on your spreadsheet, then. Enter the following:

=564.88 * B10 + 13604

Press the enter key and you should find that Excel comes up with a value of 18123.04. This is the predicated income for the year 2014. Use Autofill for the cells B11 to B15. The rest of the predicted values will then be filled in:

So Excel is predicting we'll earn 18123.04 in 2014. By 2019, it's predicting we'll earn 20947.44.

In the next part of this tutorial, you'll see how to extend the Trendline so that these values are added to your chart.

Read More

Format Axis Titles in Microsoft Excel

Format Axis Titles in Microsoft Excel

From the previous lesson, your 2D Excel Line Chart should look like this:

To format the dates on the bottom Axis, click on them with your left mouse button. With the dates Axis selected, right click. You should see this menu:

Select Format Axis from the menu, and you'll see the following dialogue box appear (Excel 2013 users will see a panel appear on the right of the screen, instead of a dialogue box):

Under Axis Type, select Text Axis:

Your dates should end up in the middle. (Our version of Excel was a little buggy. We had to click Date axis, then click back on Text axis to get the dates in the middle.)

 

Adding an Axis Title

To add an Axis label at the top of your chart, if you have Excel 2007 or Excel 2010, click the Layout menu at the top of Excel. Then locate the Labels panel:

Click on Chart Title. From the menu, select Above Chart:

If you have Excel 2013, however, stay on the Design Ribbon and locate the Chart Layouts panel on the left, just under the File menu. Click Add Chart Element, thenChart Title > Above Chart:

You will then see a default title appear at the top of the chart. Highlight the text, and type a title of your own:

 

Add a Left Axis

We now need to add an Axis for the numbers running up the left of the chart. The numbers are the hours per week that people watch each channel - 0 to 6.

From the Labels menu still, select Axis Titles > Primary Vertical Axis Title > Rotated Title:

(Excel users won't have Rotated Title option - the title will roate by itself.)

This will add a title like the following one:

Highlight the default title and type Hours. You can move the title to the left by clicking and dragging. This is a little tricky, though! Use the Zoom tool at the bottom of Excel to zoom in on your target:

Move the Axis in to position:

When you're done, your chart should now look like this one:

Spruce it up a bit by adding a bit of fill colour, rounded edges, and shadow. You've already done this previously, so we won't go through it again. When you're done, it may look like ours:

And that's it for line charts. If you've been following along from the beginning, you should now have some impressive Excel chart skills.

In the next part, we'll take a look at how to predict future values with Excel charts.

Read More