Please download IntroExcel-Part2-SampleWorkbook.xlsx to follow along with workshop activities.

Throughout this workshop Click refers to a Left Click with your mouse

Insert Advanced Functions

Excel offers many other functions that you can use to work with your data. These include math and trig, statistical, and text functions.

For a refresher on inserting functions, visit the Introduction to Excel Part 1: Insert Functions page.

Math and Trig

• Difference (no function exists)
  • Click the cell where you want to calculate the difference
  • Type: =CellA-CellB
    • Example: =A2-A3
• Divide (no function exists)
  • Click the cell where you want to calculate the quotient
  • Type: =CellA/CellB
    • Example: =A2/A3
• PRODUCT: multiply cells
• SUM: add cells

Learn more about Math and Trig functions.

Statistical

• AVERAGE: identify the average/mean for a range of cells
• COUNTIF: count the number of cells in a range that meet specific criteria
• MAX: identify the largest number contained in a range of cells
• MEDIAN: identify the median for a range of cells
• MIN: identify the smallest number contained in a range of cells
• MODE: identify the most frequently occuring value for a range of cells
• STDEV: identify the standard deviation for a range of cells

Learn more about Statistical functions.

Text

• CONCATENATE: merge data from two cells into one cell
• LOWER: convert text to lowercase
• PROPER: capitalize the first letter in each word
• UPPER: convert text to uppercase

Learn more about Text functions.

Practice the CONCATENATE Function

Let’s navigate to Sheet 1 and practice the CONCATENATE function on the Salesperson column.

Right now, the data in the Salesperson column appears as last-name, first-name. We can use the CONCATENATE function to make the worksheet more visually appealing by changing the data in this column to first-name last-name

This requires six steps:

1. Insert a new column
2. Split text into multiple columns
3. Insert another new column
4. Insert CONCATENATE function
5. Replace old data
6. Delete duplicate data

Insert a new column

• Click Column C (or the Sales Q1 column) to highlight it
• Right click then click Insert to add a column

Split text into multiple columns

• Navigate to the Data tab and Data Tools section
• Click Column B (or the Salesperson column) to highlight it
• Click Text to Columns
• Select the Delimited option in the pop-up window
• Click Next
• Click the check-mark next to Tab to uncheck the box
• Click to check the box for Comma then check the box for Space
  • We can now see a preview of what our data will look like
• Click Next
• Click Finish

Insert another new column

• Click Column D (or the Sales Q1 column) to highlight it
• Right click then click Insert to add a column

Insert CONCATENATE function

• Click Cell D2
• Type =CONCATENATE(D2," ",B2)
  • Be sure to place a space between the quotation marks in this function
• Hit Enter on your keyboard
• Click Cell D2
• Hold down the Shift key on your keyboard
• Scroll to and click Cell D23 to highlight the entire data range
• Hit Ctrl + D on your keyboard (or Cmd + D on a Mac) to copy the function to all the highligted cells

Replace old data

• Click Cell D2 to highlight it
• Hold down the Shift key on your keyboard
• Scroll to and click Cell D23 to highlight the entire data range
• Right click the highlighted data, then click Copy
  • You can also hit Ctrl + C (or Cmd + C on a Mac) on your keyboard to copy data
• Click Cell B2 (in the Salesperson column) to highlight it
• Right click Cell B2
• Click Paste Special
• Under Paste, click Values
  • This will let us copy and paste just the text

Delete duplicate data

• Click Column C to highlight it
• Hold down the Shift key on your keyboard and click on Column D to highlight it too
• Right click, then click Delete

More Help

To learn more about functions, open Excel and navigate to the Formulas tab and the Function Library section.

You can also visit Microsoft’s Excel Functions (by Category) website to learn about other functions.