Using Excel Formulas to Automate Calculations

Use Excel Formulas to Automate Calculations

Usually, I’ll jog in the evenings with my friend Mark 2-3 times a week. Mark is an exceptional runner and has participated in several 10KM runs and even marathons. I take advice from him on how to run, and how to keep fit. We talk fitness most of the time. But last month Mark was often late for the jog. When I asked,  he lamented how hard things were getting at work with some new responsibilities, and how this new job required him to do some data entry and some data processing using Excel… a tool he did not use much previously.

He stayed back to check for data entry errors, remove any duplicates etc. but his manager always found silly mistakes and duplicates that now seemed so easy to spot. Mark was simply unaware of the hidden functions and power of Excel and was using it simply as a data entry tool, which was not helping the situation.

Learn Excel Formulas to Automate Calculations

“Your ignorance about Excel is the root cause of this problem. If you took the time to learn some simple, basic Excel commands, you wouldn’t have to suffer or stay back late. You could get a lot done, without mistakes, and on time too!“, I said.

Mark was instantly hooked. “You mean you could teach me some simple things and I could knock off on time?“.

“Sure. Let’s sit down this weekend for a short Excel tutorial.” I said.

Actually, Microsoft Excel is a wonderful tool for professionals, executives, managers, technicians, service, students, housewives… practically everyone with a computer or a tablet in hand.

Microsoft Excel opens like a blank page, and you can do any kind of calculations – monthly budget, salary calculation, leave computation, sales projections, grocery list… almost everything that has items, numbers can be used within Excel to get something out of data.

The Excel spreadsheet has a huge number of cells – divided into rows and columns. These rows and columns are labelled, and at each row/column cross-section, you will find a unique cell, named after the row and column. So for example, at the cross-section of Column C, and row 5, you will find the cell called C5. Intuitive isn’t it!

Basic Excel Formulas to Automate Calculations

The first thing I covered with Mark was some simple, Excel formula basics and tips.

1. I taught him how to write simple formulas using cells, +, -, *, /, use of brackets… just like in school. And then I showed him 3 different ways to copy the formulas into adjacent cells. This alone reduces errors, and makes it easy to have standard, consistent formulas, and eliminate typing errors.
2. Mind Your Brackets: Do take note of where to Open Brackets, and were to close the brackets. Not closing all the brackets is a common issue, which prompts an error by Microsoft. You can get different results when you key in= B1 * C1 + D1 / 24  – A1 OR= (B1 * (C1 + D1) / 24) – A1  OR= B1 * C1 + (D1 / 24) – A1
3. Absolute Referencing vs. Relative Referencing: By default, when you copy a formula, it is copied relatively. So B1 & C1 in the formula will become B2 & C2, when copied to one cell below. If you want to fix a cell to remain as it is, and not change automatically, you need to apply absolute references with the $ sign. For example, you could use A$1, $A1 or $A$1. This is called Absolute Referencing in Excel Formulas. It is important to learn the difference in each of the absolute references, and then decide when to use which one.

   Don’t think that you will not be using Absolute referencing. It is one of the most commonly used features in Writing Excel formulas, and will be helpful when you are reading formulas written by others… your predecessors included :) .

4. Get Excel In-Built Functions to do the Work: Whenever you have to add some numbers or find the average figure, do not attempt to write the formula yourself. Chances are that it will become out of date soon, and you will have to edit it every time you add a row or column. There are ready to use, inbuilt functions like SUM, COUNT, AVERAGE which work great, out of the box. They have some built-in intelligence, and will automatically factor in newly added rows and columns within the data.

Like this, I worked with Mark for a couple of hours, teaching simple tips, tricks and smarter ways to enter data, find duplicates, and spot errors.

It all is simple, once you understand it, one thing at a time.  Excel is a simple to use the tool. It has a number of features, and as you begin to master them, you will feel more confident, more powerful, and more in control of your data.

Mark was back on time next week for our evening jogs. And he no longer laments on his long days. In fact, we agreed to spend a couple more afternoons this month to covering more simple things in Excel, like Conditional Formatting, Sorting, Filtering, Tables etc.

You can Learn Excel Formulas Too!
Simply join me in our 2 day Excel Formulas workshops in Singapore, or ask our team of ACTA certified trainers to come to your office and train your entire team.

For the people who already know Excel basics, they can register for our Advanced Excel Training programs – covering Pivots, Charts, Macros, Advanced Formulas and Functions.

Tap of Government Grants for Training
The Singapore government encourages Singapore SME and MNC companies to send their staff for training, and increase their productivity. You can get up to 70% funding for the training. Thus, you pay only a small portion of the total training cost.

Contact us for more information on what training is suitable for you, grant information, and upcoming training dates.

Make sure you are not late for your appointments, just because of some simple Excel skills.