Menu visibility control

Video

Events

Startup Surgery

Most Recent

Tech City Voices

Making gender pay gap reporting work for tech

News

Top tech stats: UK FinTech, Brexit, cybersecurity and more

News

Startup Weekly: Just Eat’s FoodTech cohort, TrueStart’s call for applications and much more

Press Releases

Redbox Digital Gains Prestigious Magento Global Elite Partner Status
More than £1m transacted using Loot in its first 4 months
HT2 Labs receives Series A investment from City & Guilds Group

7 Excel tricks that will make you a spreadsheet pro

spreadsheet

Topics

twitterlinkedinFacebookgoogle_plustwitterlinkedinFacebookgoogle_plus

If you’re running a startup or scaleup, likelihood is you’re using Excel spreadsheets to organise some form of data. If you’re not an Excel whizz, never fear, as Andrew Hewertson from Microsoft Office training provider STL is here to share his useful tips and tricks that will save you from spreadsheet nightmares.

The prospect of tackling Excel can fill many employees with dread. The software can be intimidating to spreadsheet novices and even those who use it on a day to day basis. Luckily, help is on hand with these top tips.

1. Flash fill

Tired of splitting first names from last names or sorting out UPPER and lower case entries by hand? Excel now looks for the pattern in your corrections and can complete the work on your behalf.

How to do it: In Excel 2013, click the column alongside the data to fix and type how it should look. Select your correction and the empty cells below and hit CTRL+E to flash.

Flash Fill

Perfect for managing and fixing mailing lists and tidying database records!

2. PivotTables

One of the most powerful and rewarding Excel tools to master, PivotTables allow you to quickly summarise large amounts of data in lists and tables without writing a single formula.

How to do it: Click in your table of data and go to “insert>PivotTable”. Once you’ve highlighted everything you want to analyse, click OK. Use the right hand bar to generate your table by dragging the fields in the top half to their respective slots in the lower four boxes.

Pivot tables

Perfect for reporting and analysis!

3. Goal seek

Sometimes you know the answer you want your report to show, you just need to know what inputs will produce that answer. Goal Seek does the legwork for you, telling you what figures to put in to formulas to produce the result you are looking for.

How to do it: In Excel 2013 and 2016, go to “Data>Data Tools>what-If Analysis>Goal Seek”.

In the “Set Cell2” box, select the cell with the formula you want to determine. In the “To Value” box, enter the answer you need. Lastly, use “By changing cell” to pick the input value that Excel can adjust, then click OK.

Goal Seek

Perfect for forecasting and setting targets!

4. Conditional formatting

When you’re reviewing spreadsheets with your boss, the results should be obvious at a glance. With Conditional Formatting you can automatically highlight the values you want them to look at.

How to do it: Select your cell range by going to “Home>Conditional Formatting” and choose from the range of formats available. For example, to highlight all values lower than 100, choose “Highlight Cells Rules>Less than” and type in 100. Before clicking OK you can also choose the format that will apply to any matching values.

Conditional formatting

Perfect for reporting and analysis!

5. Index and match

VLOOKUPs only search in the first column of your table. If you’re looking to deep-dive into the table, then you’ll need Index and Match. After the initial learning curve, Index and Match offers much more flexibility, speed and ease of use.

How to do it: Ensure your data is formatted in a grid with headers and row labels. Use Match to return the column that contains your search target and another Match to find the row that will contain your answer. Feed these two answers into Index, and Excel can retrieve the value where the two intersect.

E.G.= INDEX (array, MATCH (Lookup_VALUE, Lookup_array,0) MATCH (lookup_value, lookuo_array, 0))

Index and match

Perfect for: Retrieving data from complex sets of records.

6. Waterfall charts

One of the most popular visualisation tools for showing net values, Waterfall charts break down flow and contributions to a total – ideal for financial professionals and statisticians.

How to do it: In Excel 2016, select your data and go to “Insert>Insert Waterfall or Stockchart>Waterfall”.

Waterfall charts

Perfect for reporting performance!

7. Forecasting

Forecasting lets you project future outcomes from historical datasets. You can even calculate upper and lower confidence bands to include margins for error, or calculate for seasonality.

How to do it: In Excel 2016, select your two corresponding sets of data and go to “Data>Forecast>Forecast Sheet”. In “Create Forecast Worksheet” box, choose either a line chart or a column chart, pick an end date, and then click “create”.

Forecasting

Perfect for modelling performance expectations!

By adding one or more of these tricks to your repertoire, you’ll be able to effortlessly manage datasets, improve productivity and demonstrate value.

Enter your email address to receive updates straight to your inbox

* indicates required
Send me news on...
twitterlinkedinFacebookgoogle_plustwitterlinkedinFacebookgoogle_plus

Editor's picks

gender pay reporting

Making gender pay gap reporting work for tech
posted 6 hours ago

tech stats

Top tech stats: UK FinTech, Brexit, cybersecurity and more
posted on April 22, 2017

startup weekly

Startup Weekly: Just Eat’s FoodTech cohort, TrueStart’s call for applications and much more
posted on April 21, 2017

Divido EP winner

Meet our FinTech Startup of the Year: Divido
posted on April 21, 2017

shanghai

GP Bullhound: UK’s ranking in FinTech declines slightly as China takes the lead
posted on April 21, 2017

TWIT 21.04

Nauta Capital’s $100m raise, Cera’s £1.4m Seed top up, Appear Here’s US expansion and more in the Week in Tech
posted on April 21, 2017