Cheat Sheet: Microsoft Excel Intermediate Skills

Download a PDF of this page here.

Download the Spanish version here.

Essential Concepts

Using Excel for Data Analysis

Columns of data can be quickly sorted in Excel using the sort function. You can choose ascending or descending order. If you want the entire row to sort based on a single column, make sure all of the data has been selected before sorting.

Filtering data will hide anything that doesn’t meet the given criteria. The hidden data has not been deleted and when the filter is removed it will reappear.

There are many functions in Excel that are worth exploring, but two primary function are IF and COUNTIF. The IF function will print a word (by default True) if a condition has been met by a selected cell and a different word (by default False) if the condition has not been met. Common conditions are to test if a cell is equal to a specific word or value or if the cell is more or less than a given value. COUNTIF can also test a condition, but rather than print a result it will count the number of cells that meet the condition and return the count as a number.

Automating Data Entry

Flash Fill and autofill are features built into Excel that make data entry easier. In the case of autofill, you can enter a pattern like Monday, Tuesday, Wednesday… then highlight and drag the pattern down while Excel autofills the remaining days of the week to future cells. Flash Fill will recognize a function performed in one cell and recommend the same function be applied to the cells nearby.

Sum and AutoSum will add all the values in the selected cells. If no cells are selected, autosum will guess what cells need to be added based on what cells are nearby (typically in a single column).

One of the most useful features of Excel is the ability to perform calculations to the different cells. By entering = into a cell, you can specify a formula that adds, subtracts, multiplies, and divides cells by other cells or by fixed values. Paired with Flash Fill we can perform multiple calculations very quickly.

Charts

Excel has many different styles of charts including line graphs, column charts, and pie graphs. To turn data into a chart, first organize the data into columns, then highlight the cells containing data and Insert a Chart. Charts can be edited by size, data, labels, format, and design.

Glossary

addition

the process of finding the total or sum by combining two or more cells.

ascending

sorting data from smallest to largest, or from a to z for text.

AutoSum

a feature that automatically enters the sum function to add up a column or row of numbers.

charts

visual representations of data designed to make it easier to understand large quantities of data and the relationships between different parts of the data.

clustered column chart

a type of chart that displays values in vertical bars, grouping similar data together.

command

an instruction given to a computer application to perform a specific task.

CountIF

a function that counts the number of cells within a range that meet a single condition.

data analysis

the process of inspecting, cleansing, transforming, and modeling data with the goal of discovering useful information, drawing conclusions, and supporting decision-making.

descending

sorting data from largest to smallest, or from z to a for text.

division

the mathematical operation where the number in one cell is divided by another.

filter

a feature that allows you to display only the data that meets certain criteria, hiding the rest.

Flash Fill

a feature that automatically fills in values based on patterns it detects from the user’s input.

formula

an expression that calculates the value of a cell.

function

a predefined formula that performs calculations using specific values in a particular order.

IF

a function that checks whether a condition is met and returns one value if true and another value if false.

multiplication

the mathematical operation where the number in one cell is multiplied by another.

numerical data

data that represents quantities and is measured in numbers.

percentage

a way of expressing a number as a fraction of 100, denoted by the symbol %.

sort

the process of arranging data in a specified order, either ascending or descending.

subtraction

the mathematical operation where one number is taken away from another.

SUM

a function that adds all the numbers in a range of cells.