Excel Basics | Yext Hitchhikers Platform

What You’ll Learn

In this section, you will learn:

• What Excel is and what it’s used for
• Commonly used shortcuts and functions

What is Excel and what is it used for?

Microsoft Excel is a software program used to create, manipulate, and store spreadsheets. Within a spreadsheet, you can store different types of data in cells that are organized in rows and columns.

Read these “Intro to Excel” articles from Microsoft Office Support for instructions on how to create a new spreadsheet and enter data. In the next module, you’ll learn how to use Excel effectively and efficiently using shortcuts and functions.

Guide to shortcuts

You can use shortcuts to quickly move around a spreadsheet and take actions on the data set you’re working with - see this comprehensive guide for shortcut keys on both Mac and Windows. Below you can find a list of commonly used shortcuts and how to use them:

Move to Bottom (Top/Left/Right) of Data Region

Mac: Ctrl + ↓ to move down (Ctrl + ↑ to move up, Ctrl + ← to move left, or Ctrl + → to move right)

Windows: Ctrl + ↓ to move down (Ctrl + ↑ to move up, Ctrl + ← to move left, or Ctrl + → to move right)

This allows you to move to the bottom, top, left, or right of the data series you’re working in.

Select Data Series

Mac: Ctrl + Shift + ↓ to select down (Ctrl + Shift + ↑ to select up, Ctrl + Shift + ← to select left, or Ctrl + Shift + → to select right)

Windows: Ctrl + Shift + ↓ to select down (Ctrl + Shift + ↑ to select up, Ctrl + Shift + ← to select left, or Ctrl + Shift + → to select right)

This selects data in the direction you select on the arrow keys.

Select Full Spreadsheet

Mac: Cmd + A + A

Windows: Ctrl + A + A

This selects the entire spreadsheet (including blank cells).

Select Data Set

Mac: Cmd + A

Windows: Ctrl + A

This selects only the data set.

Select Row

Mac: Shift + Spacebar

Windows: Shift + Spacebar

This selects the row you’re working in.

Select Column

Mac: Ctrl + Spacebar

Windows: Ctrl + Spacebar

This selects the column you’re working in.

Copy

Mac: Cmd + C

Windows: Ctrl + C

This copies selected cells (does not remove source data) to the clipboard, so you can paste the data in another cell or set of cells.

Copy Cell Contents by Direction

Mac: Copy contents from above: Ctrl + D, Copy contents from the left: Ctrl + R

Windows: Copy contents from above: Ctrl + D, Copy contents from the left: Ctrl + R

This copies cell contents down (use Select Set of Cells above to select cells). An easy way to remember these shortcuts is D for ‘down’ and R for ‘right.’

Cut

Mac: Cmd + X

Windows: Ctrl + X

This cuts selected cells (removes source data) to the clipboard, so you can paste the data in another cell or set of cells.

Paste

Mac: Cmd + V

Windows: Ctrl + V

This pastes the contents of the clipboard.

Paste as Values

Mac: Ctrl + Cmd + V, then Cmd + T

Windows: Alt + H + V + V

This pastes the contents of the clipboard as values (removes formatting from source data when pasted).

Undo

Mac: Cmd + Z

Windows: Ctrl + Z

This undoes the previous action taken.

Find

Mac: Cmd + F

Windows: Ctrl + F

This finds the value you input in the window that appears when using this shortcut.

Find and Replace

Mac: Ctrl + H

Windows: Ctrl + H

This finds and replaces the value you input (in the “Find” and “Replace” fields) in the window that appears when using this shortcut.

Auto-Fit Column Contents

Mac: Cmd + A, then double click the right border of the first cell in the row header

Windows: Alt + H + O + I

This auto-fits (i.e. adjusts the width of) columns based on the longest cell contents in each column of your selection.

Auto-Fit Row Contents

Mac: Cmd + A, then double click the bottom border of the first cell in the column header

Windows: Alt + H + O + A

This auto-fits (i.e. adjusts the height of) rows based on the largest cell in each row of your selection.

New Sheet

Mac: Fn + Shift + F11

Windows: Shift + F11

This creates a new spreadsheet within the workbook.

Add New Row or Column

Mac: Cmd + Shift + +

Windows: Ctrl + Shift + +

This creates a new row above or a new column to the left of your selected cells (you’ll need to highlight the full row or column).

Remove Row or Column

Mac: Cmd + Shift + -

Windows: Ctrl + Shift + -

This removes the selected row or column (you’ll need to highlight the full row or column).

Functions

You can also use functions to calculate values based on logical operations. After applying a function, you’ll likely want to use the Paste as Values shortcut to paste the resulting values from your function. Otherwise, further data manipulation (such as moving cells referenced in the function) could cause issues.

To use one of Excel’s preset functions, type = in the cell you’re working in. You’ll then type one of the inputs below to apply the function (i.e. =CONCAT(B2:C2)):

Concatenate - CONCAT(B2) or CONCAT(B2:C2) for data series - This creates a string with inputs from the selected cells (i.e B2:C2 selects cells B2 and C2 to combine without spaces).

This function is useful for quickly combining the contents of multiple cells. You could use this function to append the NMLS number to a Name for an MLO. You could also use Text Join for this use case which we’ll cover later in this section. However, the main benefit of Concatenate over Text Join is the ability to quickly merge content without a delimiter (or with a single delimiter). For this use case, you may be creating Entity IDs and inputting a hyphen to separate the underlying values. Your function might look like =CONCAT(B1,“ - “,C1) if the Internal Employee value is in B1 and the Additional Identifier is in C1.

Trim - TRIM(A1) or TRIM(A1:A50) for data series - This removes leading and trailing spaces from the selected cells.

This function should be run on any data set where there may be leading and trailing spaces. This could include fields such as Name, Address, City, Description, Products, Services, etc.

Match - MATCH(A1,B2:B4,0) - This returns the position of a cell’s value (2 if value is in 2nd row of column being searched) from an array of selected cells (i.e. finds “Sam“ in a column of names and returns position). The last value is optional, but can be 0, 1, or -1. For this value, 1 (default) returns an exact match or next smallest value (lookup array must be sorted in ascending order), 0 returns an exact match only, and -1 returns an exact match or next largest value (lookup array must be sorted in descending order).

This function is useful for quickly returning whether a cell’s contents is found in another column. For example, you might want to check whether a set of Entity IDs is already in Yext. In this case, you’d export Entity IDs from the platform and pull down (Ctrl + D) the Match function for all Entity IDs you’re checking. Your function might look like this =MATCH(A1,Sheet2!$A$1:$A$3,0) where the Entity IDs exported from Yext are pasted on to a new sheet (i.e. Sheet2).

Index Match - Read this article for detailed information on using INDEX and MATCH. You can use these functions to match values dynamically, such as returning all Address values for each Entity ID in a column.

There are many use cases for the Index Match function. The most common is pulling data across multiple spreadsheets using a unique identifier. For example, if a client provided Products and Services data in one spreadsheet and Name/Address/Phone data in another with the same Entity IDs between them, you could use Index Match to pull the Products and Services data onto the other sheet. You could also use Index Match to match on a value that exists between data sets if Entity IDs are not present (such as Address).

Left/Right - LEFT(A1,5) / RIGHT(A1,5) - This selects the left or right number of characters input after the comma. In the example here, we’d be returning the 5 characters starting from the beginning (LEFT) or end (RIGHT) of the cell A1.

This function is useful for returning a subset of characters to create a match value. For example, you might want to return only the leftmost 5 digits of an Address in two data sets that have differing Address formats (i.e. 10 Main Street will not match to 10 Main St). You can then use Index Match to match between them where the Index Match would’ve failed prior due to the differences of Street and St.

TextJoin - TEXTJOIN(“,”,true,A2:C2) - This joins cells with the delimiter you input as the second value. In the example here, we’re joining the contents of cells A2, B2, and C2 delimited by a comma and ignoring any empty cells. If B2 was not populated, the result of the formula would be A2Text,C2Text. If we input the second value as false, the formula would result in A2Text,,C2Text (note the comma that was added for the empty value).

This function is similar to Concatenate except that a delimiter is input to separate the values being combined. There are many use cases for this function, but the most common usage is formatting a text list field for upload (i.e. Products, Services, Languages, etc. or any custom field structured as a Text List). For a text list field, you would need to store the data in a comma-separated list, so your function might look like this =TEXTJOIN(“,”,true,A1:E1) where the data being combined is in columns A1-E1.

Length - LEN(A1) - This returns the length of a cell (i.e. if A1 was populated with “cat”, 3 would be returned).

This function can be used to check consistency in the length of values in a column. For example, you could use Length to check that all provided US Postal Codes are 5 digits (and resolve as needed). Often, Postal Codes beginning with 0 are sent as 4 digits - you can resolve by prepending a quotation mark (‘) and 0 before the Postal Code (i.e. ‘01234).

Proper - PROPER(A1) - This capitalizes the first letter in each word of a cell.

This function is useful for sanitizing/standardizing data such Name, Address, City, etc. If you notice that a column has inconsistent capitalization and should be standardized, you should use Proper to complete this efficiently.

Substitute - SUBSTITUTE(A1,”xyz”,”123”) - This substitutes the first input value with the second input value for the selected cell(s).

This function can be useful in a variety of ways - it’s most useful if you need to change a specific piece of text that exists in another column. For example, if you wanted to change the prepended value for a set of Entity IDs from FAQS1, FAQS2, FAQ3 to FAQ1, FAQ2, FAQ3, you could substitute “FAQS” with “FAQ” and your formula might look like this =SUBSTITUTE(A1,”FAQS”,”FAQ”). Alternatively, you could also use Find and Replace by finding “s” and replacing with null to serve the same purpose.

Other Functions to Consider

Locking References - If you’re pulling functions down or to the right, you may need to lock certain references to ensure they stay the same for all cells. For example, if you’re pulling down an INDEX MATCH, you’ll likely lock all aspects of the arrays, but not lock the reference cell so that it updates as the function is applied in each row. To lock, include a $ before the cell reference (i.e. $A1). You can also use F4 to move through the different locking options for the value you’re inputting in a function (i.e. $A1, $A$1, A$1).

Text to Columns - You will likely encounter a situation where you need to separate text in a cell by a common delimiter (i.e. a comma). You can use the Text to Columns function (Alt + A + E) to do this. To use this function, highlight the column to be separated and input the shortcut. The first selection is either “Delimited” or “Fixed Width” - only use “Fixed Width” if all cells in the column should be separated at the exact same place (by width). If you select “Delimited”, you will then need to choose from a set of delimiter options:

• Tab
• Semicolon
• Comma
• Space
• Other (any letter or symbol)

Checking for Duplicates - When cleansing a data set, you should check for duplicate values before uploading to the platform (i.e. duplicate Entity IDs, duplicate Address values with different Entity IDs, etc.). You can use Alt + H + L + H + D to apply Conditional Formatting (i.e. formatting based on a rule) that highlights duplicate values in a column.

Once you’ve highlighted duplicate values, you’ll likely want to use a filter to view only these highlighted duplicates or sort them to the top of your data set. See this Microsoft Office Support article for more information on filters and this article for more information on sorting.