Helpful Excel Tips – a pick ‘n’ mix of hints and tips for you

Helpful Excel Tips – A ‘pick n mix’ of hints and tips to help you in your day to day use of Excel!

Helpful Excel tips: Excel iconExcel Worksheets

Four things you may not know about Excel worksheets:

  1. To colour a worksheet simply right click and choose a Tab colour
  2. To move a worksheet to a different position simply drag and drop the sheet to the left or right
  3. To rename a worksheet its quicker to double click on it and then type over the worksheet name
  4. When using a worksheet in a formula where a sheet has an embedded space in it use an apostrophe e.g. =sum(‘jan 18:mar 18’!b2)

 

Consolidate command

The Consolidate Command found on the ‘Data Tab’ in Excel allows you to summarise data from separate ranges across several worksheets on to a single range on a master sheet. This also has a wonderful drill-down capability if required.

 

IF Statement

The IF statement in Excel is one of the most useful functions which allows you to return an answer to the function depending upon whether the test or condition you are considering is true or false.

It takes the form: =IF(logical test, value if true, value if false)    For example, it may take the form   =IF(c12>100, c11*1.1,c11*1.2)   this may be used if considering price updates in pricing or in costing.

Remember: if you are testing against numbers in your condition you do not need speech marks but if you are testing against a letter or word you would need speech marks.

 

Data Validation

Excel is not normally known for its data validation but there is a simple feature found in it called Data Validation. Data Validation in Excel allows you to verify the contents of cell and display error messages if incorrect data is inserted. This could be used to verify a range of numeric data or to verify from a list of possible values.

 

Customisation

Excel has some non numerical wonderful features that can be used within the package to aid you in its use. One feature is customisation of the ribbon, where you can create your own ribbon with its own groups that you can organise commands in to that you use on a regular basis to make it easier to find them at a later date. Another is customising your QAT (Quick Access Toolbar) which allows you to customise the top left hand ‘toolbar’ in your screen with some commands that you use on a regular basis too.

 

Conditional Formatting

Conditional Formatting in Excel allows you to apply rules to a set of data so that they are highlighted or display a symbol depending on what rule the figure falls in to. A good example of this is to show sales; £0-£150 highlight red, £150-£300 highlight amber, £300+ highlight green. This feature is found on the Home Tab within Excel.

 

Remove Duplicates

One lesser known feature is Remove Duplicates. This feature allows you to remove any duplicated data within the range of data present in your spreadsheet. To use this feature, you go on to the Data Tab, and click on the Remove Duplicates option. This will then allow you to choose what you want to check for duplication and then remove any duplication. This will then remove the duplicated data but it will not tell you what you have removed. If you are unsure about using this option, you can use conditional formatting to ‘highlight duplicates’ which might be a safer option for you!

These are just a selection of helpful Excel tips to try and help you in your jobs! There are lots more on our general hints and tips page if you want more, take a look!

 

If you liked this post, why not take a look at our previous one on some tips from our Advanced Excel course?