Tag: Functions

20/06/2018

Use the SUMIF function to total only the cells that match your requirement

Use the SUMIF function to total only the cells that match your requirement.

For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a lot of time.

Prior to learning this function most people filter their data based on their requirement, e.g. the team member’s name, and then copy and paste the info into another worksheet. Once in the new worksheet they then use the SUM function to create the total. Continue Reading

20/06/2018

Insert an Excel function into your workbook

Learn how to insert an Excel function into your workbook.

Excel comes equipped with a large number of pre-defined formulas. These are known as functions.

There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few. Continue Reading

20/06/2017

Excel – Using VLOOKUP to compare two lists

If you want to save yourself a huge amount of time when reconciling data, learn how to use VLOOKUP to compare two lists or columns of data.

Years ago the Training and Development manager of a large organisation invited me to spend some time in their Accounts department in order to observe how the Accounts team dealt with their day-to-day tasks. My job was to report on any process that could be improved using Microsoft® Excel. Continue Reading

04/05/2015

Excel – How can I do a VLOOKUP based on more than one value?

There are times when you want to return a match for a VLOOKUP based on more than one value. For example, we may have customers who have multiple branches. Each branch is recorded under the same customer number. So how do we look up info for a specific branch if all of the branches are using the same customer number?

08/04/2015

Finding and removing duplicate data in Excel

Finding and removing duplicate data in Excel can be a quick process. However, for many people who haven’t learnt how to do this the quick way in Excel, it can be an absolute chore.

One of the many things I love about my job is that I get to work with so many different people from all kinds of organisations. However, it doesn’t matter what industry or region I’m working in the challenges of working with large lists of data are pretty much the same.

04/09/2014

Excel – change text to uppercase, lowercase or first capital

PROBLEM: having to retype text when your data is in UPPERCASE, lowercase or a Mix oF bOTH.

SOLUTION: quickly change the text back to the proper case without having to retype it. Continue Reading

26/03/2014

Excel – Generating random numbers

PROBLEM: Generating random numbers.

SOLUTION: Let Excel do it for you!

26/03/2014

Excel – remove unwanted spaces from a cell

PROBLEM: needing to remove unwanted spaces in Excel data. Doing it manually will suck up most of your day!

SOLUTION: get to know the Trim function and save yourself time (and headaches). Continue Reading

12/03/2014

Excel – Hide errors in a worksheet

PROBLEM: how do I hide errors in a worksheet? For example the #DIV/0 error or the #N/A error. Not only do they look not so great on the worksheet, they can cause other functions and formulas that include the cell holding the error to bring back an error as well (sigh).

SOLUTION: Start using the IFERROR function to hide the errors. Not only will it hide the errors in your workbook, your other calculations will no longer show errors. Continue Reading

02/11/2013

Excel – How do I move the negative sign in front of the number?

Anyone that regularly imports data into Excel will be familiar with the process of “data cleaning”. This is where you need to remove spaces and irrelevant data in order to get the data fit for use in Excel.

I recently had an excellent question regarding working with data imported into Excel where negative numbers are imported with the negative sign to the right of the number, e.g. 500-. These numbers are commonly referred to as “mirrored negatives”. Continue Reading