Excel formulas: Covering the basics

It doesn't matter what type of job you applied for, pretty much any job today requires basic computer knowledge, and that means knowing how to use Google, Microsoft Word and Excel. Basically.

Word and Google are pretty straightforward and they’ve been around long enough for people to know what to do and what not to do.

For example, you don’t enter a full sentence in Google, but rather think of the keywords which might navigate you to what you’re looking for. And in Word, you don’t use Comic Sans MS. You just don’t.

But Microsoft Excel is where a lot of people get discouraged, disheartened and quit altogether. Simply opening a new Excel spreadsheet looks intimidating with all the boxes and a bunch of options that you can’t even pronounce, not to mention use.

But Excel is equally as important as Word, and it’s really not that complicated at all.

Here I will try to cover some of the basics of Microsoft Excel, including what it’s used for, what the menus are and how to find your way around it.

What is it for?

Excel can be an extremely useful tool, and is especially useful for data analysis, and for creating reports and charts. You can use it to enter and format data, calculate totals & summaries through formulas, and highlight data that meets certain conditions.

Here we’ll cover some basic formulas, how they’re used and what for.

Basic Formulas

  • =SUM(A1:A5) Adding blocks of numbers
  • Multiplying number using *
  • IF Function
  • How to do a pivot table
  • Using the filter function
  • Averages
  • Percentage change =(new value – old value)/ old value =(B2 – C2)/C2
  • Percentage total =Part/whole
  • Using $ sign to anchor that value
  • Maximum value =MAX (cell range)
  • Minimum Value =MIN (Cell range)
  • Upper or Lower case = Upper(cell) =Lower(cell)
  • Formatting currency, numbers, decimal places, dates etc

Math operators: Addition, Subtraction, Multiplication, Division, Exponents

Addition example (Adding values in cells A1 and A2) =SUM(A1,A2) creates a sum of the numbers in cells A1 and A2. If you wish to add multiple cells the formula goes =SUM(A1:A10). The same principle applies to other math operators such as subtraction, multiplication, division.

Simple exponents formula example: =B5^2 finds the square of cell B5.

IF Function

One of the more popular features of Excel is the IF function. With it, you ask the program to test a condition and to return one value if the condition is met, and another value if the condition is not met.

So for example, if a certain value in cell A1 is less than 5, we want Excel to tell us “Five or less”, and if it’s more, “Five or more”. The formula would be: IF(A1

How to create a pivot table

Pivot tables are extremely powerful Excel tools. They’re designed for querying large amounts of data in many user-friendly ways, subtotalling and aggregating numeric data, expanding and collapsing levels of data to focus your results, moving rows to columns or columns to rows, filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data and presenting concise and attractive results.

To create a Pivot table, select the cells you want to use. Click on the Insert menu and click the PivotTable button. In the box that opens up, you will see that your cells are selected. Press OK. There are now three separate elements of the Pivot Table on the screen: PivotTable report, Pivot Table Field List and the field layout area

To create the layout, first select the fields you want in your table, and then place them in the correct location.

Using the filter function

In Excel, it’s easy to filter your data so that the program only shows those which fit a certain criteria.

  1. Click any single cell inside a data set.
  2. On the Data tab, click Filter.

This will create tiny arrows in the column headers. Click on one arrow, and you will be prompted with the option of filtering specific criteria.

  1. Click OK.

Averages:

Calculating an average number from a set amount of cells is easy as it gets. For example, if you have entered certain numbers in cells A1 to A5, you want to see the average number in A6.

Click the A6 cell. Next to the formula bar is the FX button. When you press it, you’ll be presented with multiple choices, one of which is AVERAGE.

Press OK. You will then be asked which cells to use, in the Number 1 box type in A1:A5. Number 2 box can be left empty. Press OK. You’re done!

Percentage change

The percentage change formula is often used to calculate for example monthly change and total change. So for example, if you have certain values in cells A2 and A3, and you want to calculate the percentage change, enter this formula in the A3 cell: =(A3-A2)/A2. After that select the A3 cell and choose the Percentage format.

Percentage total

Similar thing works for calculating percentage total. So for example, if you only sold cell phones and batteries, you would enter “cell phones” in A2 and its total sales, for example 1,000 in B2.

Enter "=sum(B1:B#)" in the next empty cell in the B column, and replace "#" with the row number of the last filled cell in column B. In the example, you would enter "=sum(B1:B2)" in cell B3 to calculate the total sales of the two items.

Type "=item_sales/total_sales" in the C column for each item row. Replace "item_sales" with the individual item's sales and "total_sales" with the reference to the cell you filled out in the previous step. In the example, you would enter "=B1/B3" in cell C1 and "=B2/B3" in cell C2.

Right-click the "C" column header and select "Format Cells." Click "Percentage from the Number tab and click "OK" to format the results as a percentage. The data in column C will then display the percent of total sales for each item.

Using $ sign to anchor a value

There’s a formula-copying feature in Excel which allows automatic updates of formulas for each cell it’s copied to. When a formula refers to a specific cell, it requires an anchor for the automatic changes to update correctly. Here’s how you do it:

Example: A1+B1 should update to A1+B2. A1 will be the cell reference that will be anchored.

Replace "A1" with "$A$1". Select the cell with the formula in it. Drag the black square in the lower-right corner of the cell over the cells for the formula to be copied to.

Minimum and Maximum value

Excel can be used to find the lowest value in a range of cells, use the MIN function. For example, if you have 10 cells with numbers from A1 to A10, and want to find out which cell has the lowest number, you will do the following.

In a new cell, type =MIN(A1:10). The same works for maximum value, where the formula is =MAX(A1:A10).

Upper or Lower case

Copying and pasting tons of entries into Excel can leave your texts with uppercase and lowercase letters all over the place. Unlike Word, Excel doesn’t have a Change case feature (Shift+F3), but does have a formula to help you with that.

To change the casing of the A1 cell, select it, and in the formula bar type the following:

For a lowercase sentence: =LOWER(A1)

For an uppercase sentence: =UPPER(A1)

For the capitalization of each word: =PROPER (A1)

Formatting currency, numbers, decimal places, dates

Formatting currency and decimal plates is easy in Excel. All you need to do is find the Number box in the Home ribbon, and look for a drop-down menu which says General. Opening it will give you multiple options, such as Number (for proper number formatting), Currency (for proper currency formatting), etc.

These are just some of the basic Excel formulas which can help you with your everyday tasks.