• Crunch by Nuffnang

A Guide To Basic Excel Formulas You Should Know

by Nur Sarah Adha. |


Microsoft Excel. We hear about this application from time to time, be it in the form of arranging your personal expenditures or tasks in the workplace. A lot of times, this poses as a lifesaver for many tasks that would be troublesome if done by pen and paper.


Source: CMIT Solutions


Calculations and accuracy hold utmost importance and can be easily achieved if we grasp the basics of Excel and the functions this application can offer. This is a useful skill that you can use in any given industry especially when it comes to working with data, be it sales numbers, budgeting and more. Having to export or import data into Microsoft Excel can be a tedious and daunting task to many.


In this article, we’ll be sharing with you useful shortcuts, formulas and tips on how to work around using Microsoft Excel or Google Sheets for you to be able to work better and more efficiently! This would also allow you to save more time to be able to work on more meaningful tasks.


1. Let’s Make Things Neat With Borders

Shortcuts to apply outline border: Ctrl+Shift+&:

Shortcut to add a border: Alt+H+B: Add a border


Why it is useful?

These shortcuts are useful for when you want to segregate different types of information on a single Excel sheet, to make it easier for your eyes to read and analyse. The information would be easier on your eyes if there exist proper borders around your data, especially when there are many different types of information on your Excel sheet. It does not necessarily make it look professional, but it saves time.


When is it useful?

It is useful for almost any situation because there is no reason for data to look anything but neat and tidy! Since there are many different types of borders to choose from, let your professional creativity flow here.


Ctrl+Shift+&
Alt+H+B
Proper, neat borders

How to use this?

To use the outline border function, highlight the set of data you would like a border around, and press these three buttons: Control, Shift and &, at the same time. You should get the desired border as shown above. For the border function, press these three buttons: Alt, H and B, at the same time. A dropdown menu should appear, showcasing the different types of borders you can apply to your graph, shown above.



2. Time, A Concept

Why it is useful?

These features are useful for inserting the time and date on your excel sheet.


When is it useful?

These features are great if you do not want to manually type down the time and change the format of the number to Time and insert the current date into a cell of an excel sheet.


Shortcut to inserting time: Ctrl+Shift+colon (:)

Shortcut to inserting date: Ctrl+semicolon (;)

Ctrl+Shift+colon (:) Ctrl+semicolon (;)


How to use this?

Simply click on any cell that you want its contents to include the time on your device, and press these three buttons: Ctrl, Shift, and colon (:) (time), or Ctrl, and semicolon (;) (date), at the same time. Refer to the images above.



3. Basic Formulas for Calculation


Multiplication, Percentage & Division


Why it is useful?

Percentage is a function that is useful for counting stock and having useful numbers to remember easily, while multiplication and division are useful for inventory and prices.


When is it useful?

To find the percentage, multiplication and division result that can be showcased in a separate cell by using these functions. Good for store inventory and price tracking.


Multiplication

The concept is similar to that of percentage; simply place the cursor and drag down.


How to use this?

  1. Click on your preferred cell and start typing. Take note of the row and column of cells that you want to be calculated together. For percentage, first, type ‘=’, and then start typing the cell number, followed by a slash ‘/’ and then another cell number.

  2. Change accordingly and press Enter. The result should be there.

  3. On the Home tab, you can select ‘Percentage’ from the dropdown menu to convert decimal to percentage. For multiplication, type ‘=’, and then start typing the cell number, followed by an asterisk ‘*’ and then another cell number.

The same concept goes for all three different types of calculation. Refer to the images for guidance.


Percentage


To have the same formula for the same column, simply put your cursor on the bottom right corner of the cell with the result (H5 in the image) and drag down. The formula should follow suit in the other cells.

Division


SUM & Subtraction


Why it is useful?

Like all things mathematical, sum and subtraction are functions that are basic yet also very important.


When is it useful?

To add or subtract numbers together so that a total number or a remainder can be showcased in a separate cell, use this function. Useful in almost any circumstances.


SUM

Instead of writing every cell number, place your cursor on the bottom right corner of the first cell, and drag down. Refer below. Type in the right bracket ‘)’ and press Enter.


Subtraction

Subtraction
Subtraction Result

How to use this?

  1. Click on the cell which you would like the result of the addition to be located in. Then, start typing.

  2. All formulas related to Excel begin with ‘=’, and then the function you would like to use. In this case, it is ‘SUM’. Take note of the cells that you want to perform calculations on. After typing ‘=SUM’, start typing the left bracket ‘(‘ and then the cell number, followed by a comma, and then another cell number.

  3. For subtraction, after typing ‘=SUM’, start typing the left bracket ‘(‘ and then the cell number, followed by a comma and a hyphen ‘-‘ before the next cell number. Finally, end it with a right bracket ‘)’ and press Enter. The results should be there.

Final Result

There are definitely more formulas out there but these are a few of the basics to get you started if you work with simple data.


Getting accustomed to Excel formulas will take time, but it is important in the long run for one to be familiar with these functions and features of the Excel application. With practice, one can achieve success in remembering all that has been mentioned in this article.

You can find out more about the author on Instagram.

  • Facebook - White Circle
  • Instagram - White Circle
Copyright © 2007-2019 Nuffnang Sdn. Bhd. (762669-K) Kuala Lumpur, Malaysia