I know what you’re thinking… “Excel? why would I use that? I’m a serious data analyst with SQL superpowers.

I get it.

While it doesn’t replace databases and BI tools for regular reporting, Excel may be just what you need for quick analysis and cleansing of small data sets.

Here are 25 tips, tricks, tweaks to make it work for you.

Getting set up
Formulas and Functions

Getting set up

If you’re like most people you probably don’t spend a lot of time customising your Excel experience using File > Options.

By exploring the menus here and making a few tweaks you can make your experience more ‘you’ and avoid having to make the same change manually each time you open a workbook.

1 – Hide the things you don’t need

If you’d like to free up real estate by hiding away the search bar you can do so in File > Options > General.

Excel general settings

2 – Change what happens when you press enter

You may not want the selection to move down when you press enter. To change this to right, left, or even up use the option in File > Options > Advanced.

3 – Customise the ribbon

There’s no reason to keep the Ribbon as it is ‘out of the box’. Add what you need and hide what you don’t in File > Options > Customize Ribbon.

Excel customise ribbon

4 – Make a map

Microsoft PowerMap, and other useful add-ins, can be set up from File > Options > Customize Ribbon.

DISCLAIMER: This may not be available on all operating systems

Excel map tool


Shortcuts will save you time clicking through menus and remembering where to find what you need.

5 – One click to select all

Selecting all cells is a useful way to make a change to everything in your workbook, whether that’s the cell colour, font, or adding borders to all.

This can be done by either clicking the Select All button at the top left corner or using CTL+A.

Excel shortcuts

6 – Highlighting large numbers of rows

When your sheets start getting large you can spend more time than you’d like navigating around. Save time by highlighting large areas of data by using CTL+SHIFT+up/down/left/right. That way you won’t spend your day scrolling forever.

7 – Paste special

You probably know you can use CTL+C to copy, and CTL+V to paste. You may even know about the Paste Special menu. But did you know you can use keyboard shortcuts to do it all?

  • Values only – Alt+E+S+V + Enter
  • Formatting only – Alt+E+S+T + Enter
  • Comments only – Alt+E+S+C + Enter
  • Set column width same as copied cells – Alt+E+S+W + Enter
Excel paste special

8 – Paste formatting with the paintbrush

If you like the look of a formatted area, you can quickly use the format paintbrush to apply it anywhere else you like.

Highlight the area you have in mind, click the paintbrush, then click the destination area to copy it over.

If you have multiple areas to format just double-click the paintbrush.

Excel format painter

9 – Formulas without the formulas

To see SUM, COUNT, AVG super quickly, there’s no need to use formulas. Just highlight the data you need and check out the bottom right corner for the result.

Excel formulas

10 – Fill down fast

If you have data that you need to copy down a column you can do it with a double-click. Hover in the bottom right corner of the cell until a small black cross appears, then pull down to copy down.

11 – Format the column to fit the text

To quickly format one or more columns to be the right width for your data, position your mouse over the right border of the column heading until the double-headed arrow appears, and then double-click the border.

Excel fill down

12 – Add a new line to a cell

In Excel, if you hit Enter you will move to the next cell. If you’d rather have a new line in the same cell use ALT+Enter instead.


Now it’s time for some fun with formatting. These tips make it easy to make your numbers look great.

13 – Remove gridlines for cleaner dashboards

Gridlines are great when wrangling data, not so great when presenting dashboards. These can be turned off by finding the option in the View menu on the ribbon.

Excel remove gridlines

14 – Rotate headings

Headings don’t need to stay horizontal. If you have narrow columns they may look better rotated on an angle or even vertical. Find this option on the ribbon under Home > Alignment.

Excel rotate headings

15 – Wrap text

While we are on the alignment menu this is also a good time to bring up the other options here. Use Wrap Text to wrap extra-long text across multiple lines.

Excel wrap text

16 – Merge cells

If you have text that needs to be merged across multiple cells you can find Merge & Center here to do just that.

17 – Conditional formatting

The quickest way to get a feel for trends in your data is to add some conditional formatting. You can find this menu either on the Ribbon under Home > Styles or when highlighting a data set.

There’s more to it than just highlighting the top and bottom results. You can add icons, data bars, sparklines, or add some logic to make sure the right data points stand out.

Excel conditional formatting


18 – Split a cell in two

This is probably one of my favourite tools in Excel to use when cleansing data. To split a cell by a space, a symbol, a letter, or any other text qualifier use the Text to Columns tool under Data Tools.

Excel text to columns

19 – Remove duplicates

Alongside the Text to Columns tool you’ll find the Remove Duplicates tool. Highlight a column where you suspect there are duplicates and click the button to delete the duplicated data.

If you’d prefer to use a formula to do this use:

=UNIQUE(A:A) -- returns a list of distinct values from column A

Formulas and Functions

There are hundreds of functions in Excel that can be used from anything from cleansing text to forecasting and adding complex logic.

20 – Clean up text

Cells with text can be quickly converted to the same case with the help of functions. This is especially useful when a csv or sheet of names or email addresses needs to be cleansed.

=UPPER(A:A) -- returns a list of UPPER case text from column A
=LOWER(A:A) -- returns a list of lower case text from column A
=PROPER(A:A) -- returns a list of Proper case text from column A

21 – Trim

To further clean up text, use TRIM to remove trailing and leading spaces.

=TRIM(A:A) -- removes leading and trailing spaces 

22 – Concatenate quickly

To join cells together, maybe if you’ve been supplied a First Name and Last Name column and want a Full Name column, use CONCATENATE.

=CONCATENATE(H15, ,H16) -- concatenates H15 and H16 into one cell with a space between

23 – Count text

You may have heard of the COUNT function which counts the number of cells in a range that contains numbers. But there are more options in this function family.

=COUNTA(H5:H29) -- counts the number of cells in a range which are not empty 
=COUNTIF(E65:E76,2015) -- counts the number of cells in a range which meet a condition, in this case =2015

24 – Create a countdown

Returning the system time and date is a useful function to create a countdown. Use it on a sales dashboard to countdown till the end of the month, or count down till your trip on a sheet where you track savings goals.

=TODAY() -- returns the current date
=NOW() -- returns the current date and time


25 – Protect your sheet

There’s nothing worse than sending a sheet and finding someone has edited a cell in error. Prevent this by using the options in the ribbon under Review to protect cells or the whole workbook.

Excel is easy to learn and allows analysts and stakeholders to speak the same language. While it doesn’t replace databases and BI tools for regular reporting, Excel may be just what you need for quick analysis and cleansing of small data sets.

What are your most-used Excel functions and tools?

Photo by Ricardo Esquivel from Pexels