12 Excel tips to take your career to the next level

Microsoft Excel skills are indispensable in a wide range of different industries…

But even though more than a quarter of a billion of us use Excel on a daily basis, we might not be aware of all the functions available to us – let alone know how to use them.

To help take your spreadsheets to the next level, the experts at Excel with Business have put together a list of their top 100 most useful Excel tips. Here are 12 of our favourites:

 

Conditional formatting

Consistently named as the most important Excel feature by users and industry experts alike, conditional formatting allows you to instantly find patterns in your data – just by changing a cell’s colour.

Even the simplest colour changes can be hugely beneficial, helping you highlight everything from profit and loss right through to improvements in employee performance.

Time to learn: 180 mins

 

PivotTables 

PivotTables help you sort, count, total or average your data – and display it all in a new table, customised to your needs.

Not only do they allow your data to be summarised in a simple and flexible way, they also make masses of data easier to analyse. Warning: make sure your data is clean first…

Time to learn: 240 mins

 

Paste special

If you use Excel a lot, chances are you’ll be pretty comfortable using Ctrl+C and Ctrl+V. But you don’t always want to include everything when you copy a cell over.

Whether you just want the formatting, values, comments or formulas, paste special will help you choose exactly what you want to copy, and customise your spreadsheet to suit you.

Time to learn: 10 mins

 

Add multiple rows

Undoubtedly one of the most frequently carried out activities in Excel. Ctrl Shift + is the shortcut, but it’s actually much easier to simply right-click on the row numbers on the left of the Excel display.

And if you want to add more than one, select as many rows or columns as you’d like, and then right-click and add. Simple.

Time to learn: 10 mins

 

Absolute references ($)

Adding a $ sign in front of a letter fixes the column; adding the dollar in front of the number fixes the row. So instead of constantly having to copy and paste formulas, you can anchor your data throughout a row or column.

It’s easy to change too, with F4 toggling through the different possible combinations.

Time to learn: 180 mins

 

Print optimisation  

Everyone has problems printing from Excel.

But rather than simply clicking Ctrl+P and hoping for the best, getting comfortable with the print preview screen will allow you to do everything from fit your sheets on one page, through to adjusting margins and printing headers. So invest your time wisely early on.

If all else fails, remember three simple words: Page. Break. Preview

Time to learn: 120 mins

 

Extend formula across/down

One of the best things about Excel is its scalability.

In other words, get the formula right once, and the software will do the hard work for you. All you need is the cross hair (‘+’), and double-click – then you can extend your formulas for as long as you’ll ever need.

Time to learn: 5 mins

 

Flash fill

Say you have two columns of names and you need to construct email addresses from them.

Just do it for the first row and Excel will work out what you mean and do it for the rest. It actually couldn’t be easier. Just bear in mind that flash filling is a post 2013 feature.

Because filling out cells manually is so 2012…

Time to learn: 30 mins

 

VLOOKUP

Ever wanted to search for something in the first column of a table? Meet your new best friend.

Especially useful when you have a massive amount of data to go through, the only downside of VLOOKUP is that it only works with rows – and if your info goes to the right of the formula. That’s where index-matching comes in…

Time to learn: 15 mins

 

Index-match

Index-matching enables you to look up a value in a table of data – whether it’s left or right, in a row or in a column. You can then display the relevant data in a shiny new table.

Think of it as a more flexible alternative to VLOOKUP. It may take a little longer to master, but it’ll definitely be worth it in the long run.

Time to learn: 45 mins

 

Upper, lower, proper

If you’re stickler for good grammar, this is definitely the tip for you.

Using ‘upper’, ‘lower’ or ‘proper’, you can change multiple words to appear in upper case, sentence case or lower case. It’s a great way to ensure names are formatted correctly. It’s also a great way to make your spreadsheet look more consistent.

You know, if you like that kind of thing.

Time to learn: 180 mins

 

& and CONCATENATE

Finally, these two features allow you to combine cells quickly and easily.

For example, if you have ‘Mr’, ‘James’ and ‘Smith’, all in separate cells, adding a few simple ‘&’ symbols (e.g. ‘=A1&””&B1&””C1’) will pull all three words together.

If you have a lot of things to combine, CONCATENATE is even better. It also sounds impressive. And, let’s face it, isn’t that what being efficient in Excel is really all about?

Time to learn: 15 mins

 

Why study Excel?

Excel is one of the most in-demand skills for employers, in almost every industry.

Becoming Excel certified will help make you even more efficient when working with spreadsheets, not to mention add more value to your CV when you next look for work.

Excel with Business are offering a Microsoft Excel Bundle, which includes everything you need to get certified – whether you’re a beginner, or you’ve used Excel for a while

And with excellent discounts on offer right now, there’s really no excuse not to build your skill set and become an Excel expert right now.

 

find-out-more

 

Want to learn more? Become Excel certified now.

 

 

Excel with BusinessExcel with Business is one of the UK’s leading providers of online Excel training. They’re committed to helping their students learn Excel in the most efficient way possible, as well as ensuring their courses are always business-relevant – and they’ve taught nearly 1 million students in the last 5 years.

 

Find a job