5 Helpful Functions in Excel

Top tips for using this common workplace program

Excel is one of the basic tools you will probably need to understand no matter what sector you end up working in. In your first job you will probably have to get used to using it, but learning all the things that are possible on Excel can take a long time. This article outlines 5 useful Excel functions that will help you to boost your skills and speed up your work!

Creating a drop down list

If you are assigning people to tasks, categorising content or planning a project, drop down lists can be very helpful. For example, if you have lots of accounts, you can add a drop down menu where you fill in the associated account manager for each client.

  1. First type out the list of options you want in a list of cells.
  2. Then highlight the cells you want to make into dropdown lists and select Data>Validation.
  3. Then type in the formulae of the cells you have typed the options into and the highlighted cells will become drop down lists!

Conditional formatting

Whether you are tracking stock, complaints, absences or similar variables in your data, conditional formatting can be an easy way to spot when something needs attention! For example, you may want highlighting when payments are overdue or when targets have or have not been hit by highlighting the cells in red or green.

  1. Select the data that you would like to conditionally format by highlighting it. Then select Format>Conditional Formatting.
  2. Enter your rule into the box. You can sort based on values, keywords or other variables. Also select your desired formatting.
  3. You can select to add another rule.
  4. This will conditionally format the data!

Sorting a list of data by creating a filter

If you are looking at a big list of data and you want a basic overview of what is going on, a filter can be very helpful. For example, if you want to see what your top-earning services are, or you want to order your products by cost.

  1. Select the whole table, not just any column that you want to filter. Then select Data>Create a Filter.
  2. You can then select the drop down menus and search for specific values, or sort the data highest to lowest etc., without messing up your data!
  3. At the end simply remove the filter by selecting Data>Remove Filter.

Fixing columns and rows

If you have a big spreadsheet that you are constantly scrolling across, it can be helpful to freeze columns or rows, so that they stay put as you scroll. For example, if you have all your client information on one spreadsheet, you can freeze their names in a column and scroll through the rest of their details.

  1. Select the column you want to freeze up to and then select View>Freeze>Up to selected column.
  2. The columns will then be frozen, and you can unfreeze them by selecting View>Freeze>No Columns.

Date and time formatting

If you are typing out dates and times it can be annoying when Excel changes their format. Formatting your cells for date and time can avoid this and make the spreadsheet easier to read and use. For example, if you are listing the date that different products need to be shipped, or the date that emails were last sent to potential clients, you can use this type of formatting.

  1. Select the cells you want to format, then select Format>Number>Date/Time.
  2. When you type in the cells they will correct to a standardised system of dates and times, and you can click into your date to change it on the calendar.
A Guide to Communicating in a Business Environment→