I have been asked twice in one week how to write Excel formulas to calculate GST at the rate of 15%.
You can find information on how to calculate the rate on the NZ Inland Revenue website. I’ve taken this a step further and put together a tutorial on how to put these calculations into Excel formulas. Read more
There are times when you want to return a match for a VLOOKUP based on two values, not one. 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?
PROBLEM: Wasting time removing unwanted spaces in Excel data.
SOLUTION: Get to know the Trim function and save yourself time (and headaches).
PROBLEM: wasting time manually changing cell references in Excel from relative (e.g. A3) to absolute (e.g. $A$3).
SOLUTION: learn the relative to absolute shortcut key!
PROBLEM: wasting time writing formulas to find the
Average, Count and Sum of a range.
SOLUTION: learn how in Excel you can quickly calculate the
Average, Count and Sum of a range without having to write a formula.
PROBLEM: Having to click and drag…and drag….and drag…to copy a formula down a large number of rows.
SOLUTION: Start using the AutoFill secret shortcut. You’ll love it!
PROBLEM: Dealing with formulas that result in error messages. For example the #DIV/0 error or the #N/A error.
SOLUTION: Start using the IFERROR function Read more
I recently ran an Advanced Excel course where I was asked “what is the quickest way to pull data from multiple worksheets into a summary worksheet?” Excel offers several solutions for consolidating and summarizing data from different worksheets and workbooks. Read more
Turning formulas into values is a very common procedure to most users of Excel. Paste Values allows you to replace a formula with the result. For example, a cell containing the formula “=3*2” can copied and using Paste Values the formula result, i.e. “6” will be pasted into the cell, replacing the formula.
Here’s a fabulous (and very popular) tip to save you time when you paste values in Excel. Read more
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”. Read more