How to use Slicers in Excel

excel friday tips

Welcome to 3 tip Friday. These updates are just a quick and easy way to learn some extra tools and tips in Excel. This week’s tips will help you make your data more easily searchable, help you navigate your sheets better, and fix dodgy Excel dates (I mean dates in a spreadsheet, not an awkward geek-meetup!).

A quick win you can learn today -

If you use formatted tables to organise your data (you should, it makes things a whole lot easier), you’ll get the added benefit of filters on the header row of your data. But you can go one step further and add a Slicer. What’s a slicer, I hear you say. It’s a floating box which contains all of the items you can filter on for a given field - and for files where you’re constantly filtering the same fields they can be useful, and look good too.

To add a slicer to your formatted table, click anywhere in the table and go to the ‘Table Tools’ menu in the menu ribbon, and click insert slicer. You’ll be prompted to choose a field to filter on, and that’s about it! Another easy way to make your work look super professional.

A keyboard shortcut I'm adding to my list -

You know when you’re typing data into Excel, and you hit Enter to move onto the next cell, that Excel always moves down a row? It’s not always where you want to go next. What if you want to go up, left or right? Thankfully there are a few easy shortcuts to do this, and it makes navigating the spreadsheet with the keyboard much quicker.:

  • Enter will move down 1 cell

  • Shift + Enter will move up 1 cell

  • Tab will move right 1 cell

  • Tab + Shift will move left 1 cell

A function I've found super useful this week -

Sometimes, working with dates in Excel can be tricky. I know I’ve occasionally received spreadsheets where the date is encoded as text - and you can’t perform any sorting or date formulas on it …. it makes no sense and is frustrating! Luckily there is a function which can fix this. DATEVALUE will take certain types of text formatted date and convert them to readable Excel dates. Just type =DATEVALUE(A1), or wherever your text-date is, and if it’s in a sensible format, Excel will convert it to a date. At first, it will appear as a number though, just be sure to convert it to a date formatted cell!

Check out more Excel worksheet functions categorized by their functionality here!

As always, I hope you find these tips useful, and please do reply if you have any comments or questions!

- Martyn

Edinburgh, Scotland

October 2018