The list of tasks that can be completed in Excel is endless, some of the more popular uses are calculations, accounting, databases, creating charts, goal planning, task lists and project management. Consequently, discovering a few time saving tips can provide great benefits. Here are my top Excel tips.

Conditional formatting

This is my favourite of these Excel tips. Conditional formatting changes the format of cells based on a set of criteria making it visually quick to check your data. A cell will automatically be formatted in the style you choose when the criteria is met. There are many pre-built conditions, making it easy to use but you can also create your own.

Useful pre-built conditions include highlighting duplicate values and highlighting values that are greater, equal or less than a specified number.

You will find the conditional formatting function on the home ribbon.

VLOOKUP

On many occasions I have been given multiple Excel worksheets and asked by clients to consolidate the data. In most cases the rows are out of sync meaning combining the worksheets is not an option. An example of this would be consolidating worksheets with monthly employee holiday records, new employees will start and others will leave resulting in different names on each worksheet. Using the VLOOKUP allows you to look up and retrieve data from a specific column using a common identifier, in this case it could be the employee ID.

Know your short cuts

Here are a few of my favourite shortcuts that make it easier to navigate Excel
Alt and = to quickly autoSum a column or row of numbers
Ctrl and ’ to copy a formula from the above cell
Alt and F1 to create a bar chart based on the selected data
Ctrl and Space to select an entire column
Shift and Space to select an entire row
Ctrl and Shift and Space to select the entire workbook
Alt and Enter to create a line break within a cell

Ctrl and Shift and $/£/% to format numbers

Continue a series

Excel makes it easy to continue a series of data, for example days of the week or dates. Simply type the first piece of data and then drag from the bottom right hand corner to the cells below or next to. Click on the bottom right hand corner of the final cell and select fill series.

Create drop-down lists

You may want to limit the options available to be entered into a cell in Excel when sending it to others to complete. The best way to do this is to use a drop-down list.

In a new worksheet, type the entries that you would like as options in the drop-down list in a table. Select the cell that you would like the drop-down list to appear in and go to the data tab on the ribbon, choose data validation. In the settings tab select list in the allow box. In source select the list range from the Excel table that you just typed.

You can name the list and include a message that will pop up with the cell is clicked in the input message tab and also add an pop up for when someone enters something that is not on your list in the error alert tab.

Protect the worksheet

Another of my top Excel tips is related to protecting your work in Excel. There are various was to protect a worksheet, under file and info you’ll find the protect workbook option. If you select this option, you’ll see a number of options including adding a password to open the workbook, opening as a read only file and protecting the sheet by limiting the changes people can make such as deleting rows etc.

Remember to save the worksheet to apply the changes and keep a note of any passwords as it’s not possible to reset or recover.

Another option if you need to share a file is to save the file so that a password is needed to modify the sheet but view only access is allowed. For example, in the case of a staff rota you will want the team to be able to view the sheet but not make any changes. To set these restrictions, select save as, in tools select general options and add a password to modify.

Text to columns

If you want to separate data from one cell to multiple columns you can use the text to columns function. An example is that you have a full name in one cell, but you want to separate into first and surname.

To do this select the entries that you want to separate and choose date in the ribbon. Select text to columns and make sure delimited is selected and click next. Select space from the delimiters and click finish.

Instant formulas

Most people are familiar with the autosum function but there are other instant formulas on the editing section of the ribbon. These include average, count numbers, min and max, all handy for making quick calculations. Another way of finding this information is by looking in the right-hand side of the worksheet when highlighting the required cells.

Find and replace

You can use the find and replace feature in Excel to search for something specific that you need in a workbook. This could be a number, text, formula or for changing the formatting style. It is possible to use the function to locate a specific piece of information or replace it. Options include searching by column, row, comments, values or formulas. You can search within a given worksheet or across multiple worksheets. You will find this feature in the editing section of the top ribbon.

These are just a few simple Excel tips to help you work smarter, I hope you find them useful. There are so many more advanced options for you to discover once these are mastered.

Want to find out more about me, take a look at my website www.helenporterpa.com.