Excel tips series

💡 Create Beautiful Line Graphs in Excel in 6 Steps

Dave Sellick Founder, Sidgrove

Excel is still used regularly by accountants yet many are self taught and are missing out on lots of useful tricks!

To help us learn some more useful Excel functions, Dave Sellick will be giving us a great "Excel Top Tip" in each issue so we can learn how to really use Excel well. Dave is a super user of Excel and believes it is an absolute game changer and a central part of every accountant's toolkit - even in 2021.

📊Data Visualisation:

📈 It’s important to invest in this skillset

  • The ‘new age’ of data visualisation no longer has a place for average looking graphs
  • With new reporting apps like Fathom, Futrli & Spotlight all pumping out great looking graphs and visuals out of the box, I’m seeing business owners expecting a step-up in the way their financials are presented

👑 Excel is still king for on-the-fly data visualisation

I know most accountants will make the assumption that they need to make the move away from Excel in order to make this step up

  • However, this isn’t necessarily the case - Excel can produce great looking visuals, you just need to know how to work with it

I’ve spent huge amounts of time obsessing over the little tips and hacks that will take your Excel line graphs to a whole new level; in this article I’m sharing some of my key tips

  • None of what you’ll read below is difficult; it’s largely about creative application of tools & functions that you’ll likely already know of (if you’ve spent any time in Excel at all)

❗ Note: If you get stuck at any point through this process, refer to the video at the bottom of the article for a full run through

📚What you’ll learn in this article:

💡 How to turn stock, out of the box, Excel graphs like these:

🙌🏼 Into these - report pack ready:

  • I’ll also show you how to save these graph styles to templates that will allow you to initiate this transformation almost instantly in future
  • 💡 I’ll be walking you through two examples throughout the article: i) Single Line Graph (cumulative revenue) ii) A Comparative Line Graph with two lines (current vs prior year cumulative EBITDA)

❗ Note: I’ll only be covering off line graphs in this article, but many of the techniques can be applied to all graphs types

1. Select Data and Insert a Line Graph

  • Select the time series and the relevant data rows, including the title column of the row series
  • If you’re working with multiple years of data and/or multiple data streams then you’ll need to select multiple rows of data, as shown below: 💡 Tip: Keep the ‘Ctl’ (or ‘Cmd’ on a Mac) button held whilst selecting multiple regions, otherwise you’re initial selection will de-select

Whilst the Data is selected go to ‘Insert’ 👉🏼 ‘Recommended Graphs’ and select the line graph

  • This option will most likely be the first ‘recommended graph’ if you’re working with Data vs Time

❗ Note: you could also do this by going straight to ‘Insert’ 👉🏼 Line Graph drop down 👉🏼 ‘Line’

I tend to prefer to select ‘Recommended Graphs’ just to check that I’m using the most appropriate chart. I also don’t think it saves any time selecting via the line graph dropdown

🏆Step 1 Results:

  • What we’ll end up with is the ‘stock’ Excel line graph; not massively inspiring!
  • Time to get to work 💪🏼

2. Format the Axes

i) Define the Axis Lines

  • Select the X-axis
  • Open the formatting sidebar by either: a) Double clicking the Axis b) Using the Shortcut Ctrl+1 (Cmd +1 on a Mac) or c) Right clicking on the Axis and selecting ‘Format Axis’
  • Change the line colour to black
  • Repeat the same steps as above for the Y-axis

ii) Change the Axes Font Format to Black & Bold

  • Select the X axis and then select the ‘Home’ tab and change the font to black & bold
  • Repeat the same process for the Y-Axis

iii) ‘Neaten- up’ the Y-Axis number format

  • Where necessary, consider adjusting the number format of the Y-axis 💡In this instance the Y axis looks a lot neater when the number formatting is amended to show thousands (i.e. ‘£k’ format)
  • Double click the Y Axis and access the formatting sidebar (or use other methods to access as mentioned above)
  • Open up the number format section and input the following number format (if you’re dealing with thousands) £#,##0,"k";(£#,##0,"k") - Copy and paste the above code into the ‘Format Code’ box and click ‘Add’, as below - 💡 This format will then save into this worksheet for further reference

🏆Step 2 Results:

3. Add Chart Labels & Markers

  • I’ll walk through two options that you could go for at this stage

i) Option 1: Add Circular Markers with Data Labels on the outside of the line

  • Select the line and open up the formatting sidebar in the same way as explained in pt 1 (e.g. by double clicking or pressing Ctrl +1/Cmd +1)
  • Add circular markers, size 5
  • Set the fill to white
  • Set the border to black
  • Select the Line again (if it’s been de-selected) and go to ‘Chart Design’ 👉🏼 ‘Add Chart Element’ 👉🏼 ‘Data Labels’ 👉🏼 ‘Above’
  • Select the data labels that have just been added to the graph and change the font colour to the same colour (or darker) as the line
  • Change the data label format, where necessary; in this instance, I’m going to apply the same format as the axis
  • This can be done by activating the formatting sidebar and inserting the following format #,##0,"k";(#,##0,"k") - This is the same format as the axis, but without the ‘£’ icon
  • Optional: Select the last value (i.e the current cumulative figure for the last 12 months) and increase the font size, bold it and put a black border around it - This helps to draw attention to the most important figure on the graph

🏆Step 3 i) Result:

i) Option 2: Place data labels on the line graph(s)

  • Select the Line and go to ‘Chart Design’ 👉🏼 ‘Add Chart Element’ 👉🏼 ‘Data Labels’ 👉🏼 ‘Centre’
  • Change the data label format & colour, where necessary - refer to the process in Option 1
  • Select Data Labels and go to ‘Format’ 👉🏼 ‘Shape Fill’ 👉🏼 Select White
  • Optional: as in Option 1, select the last data label and bold, increase font size and surround with a black border

🏆Step 3 ii) Result:

4. Dull-down the Gridlines & Add Drop/High-Low Lines

i) Dull-down the gridlines

  • Select one of the horizontal gridlines - This will then select all of the gridlines on the graph
  • Open the formatting sidebar (as denoted earlier in the article) - double clicking on the gridline is probably the easiest way to do this
  • Select ‘Line’ 👉🏼 ‘Solid Line’ 👉🏼 ‘Colour’ 👉🏼 Select the lightest grey colour

ii) Add in Drop/High-Low Lines

  • Select anywhere on the graph
  • Go to ‘Chart Design’ 👉🏼 ‘Add Chart Element’ 👉🏼’Lines’ 👉🏼 either ‘Drop’ (if 1 line graph) OR ‘Up/Down’ (if 2 or more line graphs)
  • Select the drop/ high/low lines and initiate the formatting sidebar (see earlier in the article); again, double clicking is probably your best option for initiating the sidebar
  • Select ‘Fill & Line Options’ 👉🏼 ‘Solid line’ 👉🏼 ‘Colour’ 👉🏼 Select a dark grey colour (or a colour consistent with the line) 👉🏼 ‘Dash type’ 👉🏼 ‘Choose the 4th Option down

🏆Step 4 Results:

5. Amend & Reposition the Title & Legend (Key)

i) Format the Legend

1 Line:

  • If you only have one line then I’d recommend that you delete the legend - It offers no value as long as you have an appropriate title
  • Click the legend and press delete; literally as simple as that

2 or More Lines:

  • If you have more than one line then select the legend and change the font to black, bold it and adjust the font size as required
  • Optional: reposition the legend onto the body of the graph
  • Double click the legend, (open up formatting sidebar) and go to ‘Legend Options’ 👉🏼 Untick ‘Show the legend without overlapping the chart’
  • Click the legend and drag it onto the face of the graph

🏆Step 5 part i) Results:

ii) Format the Title and Re-name to Something Appropriate

  • This step is pretty self explanatory - click the title and change the font colour to black, bold it and take the font size down to something that doesn’t distract too much from the actual chart
  • Optional: re-positioning to the left & underline
  • Click into the chart title box and rename the title to something appropriate (obviously!)

🏆Step 5 part ii) Results:

6. Final Touches (Optional, based on preference)

💡 In my opinion, these final touches really take the aesthetic to another level 🔥; but feel free to adapt based on the desired look & context of where the graphs will be presented

i) Round Edges of the Graph

  • Select the outside border of the graph and double click to open the formatting sidebar
  • Go to ‘Fill & Line’ Options 👉🏼 Tick ‘Rounded Corners’ at the bottom

ii) Add a Subtle Shadow

  • The key word here is ‘subtle’! Be careful not to overdo this one - However, used with care, this can be a game changer if you’re looking for that contemporary visualisation aesthetic (which I am!)
  • Select the outside border of the graph and double click to open the formatting sidebar
  • Go to ‘Effects’ Options 👉🏼 Choose the ‘Shadow’ dropdown 👉🏼 Select one of the presets - I tend to go for a preset with a shadow all the way around the graph (see selection in the image below); however you can experiment with this
  • Stay in the same formatting section and select ‘Color’ 👉🏼 Choose a dark grey
  • Again, in the same formatting area 👉🏼 Change the transparency to 95% (or somewhere in that region)

iii) Make the Graph Border Slightly Darker

  • This is optional, but I prefer a slightly darker border around the graph
  • Select the outside border of the graph and double click to open the formatting sidebar
  • Select ‘Fill & Line Options’ 👉🏼 ‘Border’ 👉🏼 ‘Solid line’ 👉🏼 ‘Colour’ 👉🏼 Select a darker grey colour, according to preference

iv) Adjust the Colours of the Line Graphs/fonts to Fit in with the Desired Aesthetic/Colour Scheme

  • Make sure you put that cherry on top 🍒 and align the graphs to the company branding or something that looks great 👍🏼
  • Select the line graphs & data labels, as necessary, and use the home tab & formatting sidebar (which you’re probably sick of me talking about now 😂) to change the font & line colours as desired

🏆Step 6 Results 🔥:

Pro Step) Save Template for Future Use

  • For those that are serious about their workflow optimisation, you can save the formatting of the graph you’ve just created for instant future use 🚀
  • Right click on the chart 👉🏼 ‘Save as Template’ 👉🏼 Give it a name 👉🏼 Save
  • When you want to initiate the template in future, just select your data per Step 1) 👉🏼 ‘Insert’ 👉🏼 ‘Recommended Graphs’ 👉🏼 ‘All Graphs’ 👉🏼 ‘Templates’ 👉🏼 Select the template you’ve just created
  • You’ll need to make a couple of tweaks - usually just giving the graph a name; otherwise this is amazing time saver for graphs formatted perfectly to your taste
  • This template will remain in your templates folder for selection at any point in the future 👍🏼

🎥 Video Walkthrough

A bit about Dave

  • I am the founder of a purpose led accounting practice & consultancy based in London called Sidgrove, working exclusively with founder-owned businesses & scaling startups. I initially trained in audit with PwC in banking, before moving into industry for around 6 years with numerous scaling startups; I’ve been running Sidgrove full time for just over a year.
  • I’ve spent more hours than is probably healthy🤦‍♂️😂 working with Excel 📊as an Accountant over the last 12 years in both industry and practice. During that time I’ve continued to push the boundaries 😇 of how it can be used to deliver efficiencies and superior insights to my clients.
  • My Excel journey really took off 🚀during my time as a Head of Finance of various startup businesses, where efficiency🛠, quality commercial insights 📈& creative presentation🌈 was imperative.
  • I’ve now taken all this experience into my practice, Sidgrove, 💪🏼where amongst a wide suite of software and hardware tools, it is still my most prized day-to-day tool 🏆and canvas ✒.
  • I believe Excel is still an absolute game changer 💯🚀and it needs to stay centre stage in the Accountant’s Toolset, even in 2020.
  • However, I know the full functionally of Excel can seem daunting many accountants 😨so I am starting a top tips series to help you get the best out of Excel’s most relevant functionality for Accountancy in the modern-day 😊.

Read next article

Innovate like Netflix or become blockbusted