If you're just starting out with Excel, there are a few basic commands that we suggest you become familiar with. These are things like:
- Creating a new spreadsheet.
- Executing basic computations like adding, subtracting, multiplying, and dividing.
- Writing and formatting column text and titles.
- Using Excel's auto-fill features.
- Adding or deleting single columns, rows, and spreadsheets. Below, we'll get into how to add things like multiple columns and rows.
- Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you're filling as you move further down the document.
In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel formulas and functions you’ll need to know.
It’s easy to get overwhelmed by the wide range of Excel formulas that you can use to make sense out of your data. If you’re just getting started using Excel, you can rely on the following formulas to carry out some complex functions — without adding to the complexity of your learning path.
- Equal sign: Before creating any formula, you’ll need to write an equal sign (=) in the cell where you want the result to appear.
- Addition: To add the values of two or more cells, use the + sign. Example: =C5+D3.
- Subtraction: To subtract the values of two or more cells, use the - sign. Example: =C5-D3.
- Multiplication: To multiply the values of two or more cells, use the * sign. Example: =C5*D3.
- Division: To divide the values of two or more cells, use the / sign. Example: =C5/D3.
Putting all of these together, you can create a formula that adds, subtracts, multiplies, and divides all in one cell. Example: =(C5-D3)/((A5+B6)*3).
Excel functions automate some of the tasks you would use in a typical formula. For instance, instead of using the + sign to add up a range of cells, you’d use the SUM function. Let’s look at a few more functions that will help automate calculations and tasks.
- SUM: The SUM function automatically adds up a range of cells or numbers. To complete a sum, you would input the starting cell and the final cell with a colon in between. Here’s what that looks like: SUM(Cell1:Cell2). Example: =SUM(C5:C30).
- AVERAGE: The AVERAGE function averages out the values of a range of cells. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2). Example: =AVERAGE(C5:C30).
- IF: The IF function allows you to return values based on a logical test. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). Example: =IF(A2>B2,"Over Budget","OK").
- VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s rows. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)). Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
- INDEX: The INDEX function returns a value from within a range. The syntax is as follows: INDEX(array, row_num, [column_num]).
- MATCH: The MATCH function looks for a certain item in a range of cells and returns the position of that item. It can be used in tandem with the INDEX function. The syntax is: MATCH(lookup_value, lookup_array, [match_type]).
- COUNTIF: The COUNTIF function returns the number of cells that meet a certain criteria or have a certain value. The syntax is: COUNTIF(range, criteria). Example: =COUNTIF(A2:A5,"London").
Call me