Posts

Posted on

Get more out of formulas in Excel using functions

It’s easy to fall into a habit, and manually type in a formula and copy and paste it within a worksheet. If so, you may not be getting the most out of the Formula tab on the Ribbon. From here, you can save yourself time by using Excel’s ready made formulas using Functions.

The Formulas tab has the regularly used calculations and set them up as functions in 12 different categories.  So you have these at your fingertips to apply to a range of cells that you specify.

The 12 categories include: financial, date and time, maths and trig, statistical, database, text, look up and reference, logical, information, engineering, cube and compatibility.

Each of these categories has a drop down list with a selection of common functions, ready for you to apply to your selected cells.

functions-microsoft-excel-training-course
The Formula tab has functions ready for you to apply.

For example, if you need to calculate the cost of loans, or interest, yield and depreciation, you can access these functions via the Financial Function.

If-then statements are in the Logical Functions – so you can analyse data setting up logical statements, setting out whether a value is true or false, or using AND or OR.

If you regularly want to work out averages, minimums, maximums, or interest rates, these are stored in the Statistical Functions.

To get the most of Excel’s functions, it is worth exploring what the Formulas tab has to offer.  An excellent way to find out more and learn how to use them is to attend a  Microsoft Excel training course, so you can see what is available and practise using them before using them on your own live data.  From Intermediate Excel to Advanced and beyond, our courses cover Formulas and Functions in more detail https://www.stl-training.co.uk/microsoft/excel-training-london.php

Posted on

Formatting dates in Excel to get the results you need

Dates are key pieces of information in Excel spreadsheets used for everything from planning, assessing progress and analysing results.

Sometimes you may find that when you use dates in formulas, or as part of sorting or filtering data, the results don’t come out in the way you’d expect.  This can be frustrating and slow down your work.  Why does Excel do this?

When you are working with dates in Excel, it will show you dates and times in the familiar format, but it will store them, and make calculations with them as numbers.   

The tip is to remember to tell Excel that you want it to work with dates, and not just numbers.  You can do this by formatting the cell, column or row to use dates.

The format of dates in Excel
Try this….right click in a cell, you can select the date format – and here is where you can see the range of ways of expressing dates in Excel.  Formatting is key to making sure that your dates show in the way you want them to and Excel knows how to use them.  Choose the one that suits your needs.

Date-format-cells-excell-training courses
Who knew there were so many ways of showing a date….

Tip list for working with dates 

  1. Use a consistent date format throughout your worksheets – it makes it easier to work with and view patterns and trends.
  2. Format the cell, column or row with the date format you need.
  3. You can use format painter to copy the format within the document.

Working with date formats is part of our excell computer training courses.  Take a look at  https://www.stl-training.co.uk/excel-2007-introduction.php