Articles
01 Simple Math
Who remembers PEMDAS and the order of operations? PEMDAS => Parentheses, Exponents, Multiplication, Division, Addition, Subtraction It may seem like a waste of time at first, but remembering simple math rules can go a long way in writing solid formulas in Excel. Take a look at this example that tries to see what 20+30 is [...]
Read Article ›
02 Basic Referencing
Referencing is Excel’s way of using the contents of a cell (or cells) other than the one that is in use. There are two basic types: Relative reference Absolute reference Absolute references stay locked, even if you drag or copy the formula elsewhere, it will still maintain the same reference. Example: =$B$5 (locked reference to [...]
Read Article ›03 Simple Logic
This is a straight forward concept, but once you understand the basics of logic statements and logic tests, you’ll be able to write IF statements like a pro! What I mean by “logic test” is a statement that results in either TRUE or FALSE. For example, 2=2 is TRUE, 2=3 is FALSE. This differs from [...]
Read Article ›04 Named Ranges
One commonly overlooked feature in Excel is the ability to give a name to a particular cell or range that can then be used directly throughout the workbook. There are two ways to do this: (1) Select the range that you would like to name. In the upper left hand corner, type over the cell [...]
Read Article ›01 Intro to formulas and functions
Before we get into formulas, let’s go over some quick definitions: Functions: are specific commands that are predefined in Excel. Simple examples include SUM or AVERAGE. Formulas: any cell content that tells Excel to calculate something. Formulas may or may not contain functions. Examples include =2+2, or =SUM(2,2). Note: more experienced users will recognize that [...]
Read Article ›02 Some Basic Functions, Part 1 (SUM, AVERAGE, MAX, MIN)
The simplest way to use a function is using the “insert function” wizard. It contains a long list of various functions (SUM, COUNT, etc.) that is searchable and provides a structure to complete the various inputs. Open “insert function” by clicking on the “fx” button next to the formula bar, or by going to Formulas [...]
Read Article ›03 Counting Functions (COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS)
After SUM and the basic number functions, Counting functions are probably the next most common functions in Excel. Unlike SUM, which adds values together, COUNT(s) do exactly what you’d expect: they count! Here are the three basic types: =COUNT(cells or ranges) Counts the number of cells that contain a number =COUNTA(cells or ranges) Counts the [...]
Read Article ›04 Some Basic Functions, Part 2 (ROUND, TRUNC)
To round off [pun intended] the discussion on basic functions, we’re going to look at two other number functions that might come in handy. =ROUND(cell reference or number,# digits to round) With round, you are telling Excel to take a number (let’s use 132.5678 as an example), and round to a certain digit. For example, [...]
Read Article ›05 Text Functions (LEFT, RIGHT, LEN, FIND, MID, Concatenating)
Text functions are some of the unsung heroes of Excel. They often get overlooked because they don’t deal with numbers, but text functions will pop up all over the place. So let’s get into it. We’re going to cover: =RIGHT() =LEFT() =LEN() =FIND() =MID() Concatenating Let’s start with some basics. We’re going to use a [...]
Read Article ›01 CELL function
In the course of creating Excel spreadsheets, you might find from time to time that you want to display information about the file itself. CELL is often the easiest way to do this. The CELL function allows you to return information about a particular cell in the sheet. The basic format is pretty straight-forward. Put [...]
Read Article ›