Module 6 - LinkedIn - Excel: Introduction to Formulas & Functions

Get help using Excel functions

- Use 'Tell Me What You Want to Do" box- - F1 (open help pane directly) - More detailed help - start typing formula, click function name in tool tip that takes you to the office support sight

Copy and paste formulas & values

- copy formula - double click cell - Ctrl C - click cell you want to copy it to, enter - keep result of formula, but not formula itself: - Alt Key - H (home tab) - 1st letter, third row, press V (paste)

Which function counts the number of cells that contain numbers?

COUNT

How do you create a data table to display a range of values?

Click What If Analysis on the Data tab.

How do you create a new row at the bottom of an Excel table?

Click in the last table cell, then press Tab.

Suppose you have a file that imports data from other spreadsheets. How can you set this file to automatically refresh the imported data when the file is opened?

Select this option in the External Data Range Properties dialog box.

When using the SUM function, how will cells be treated that do not contain numbers?

They will be treated as 0 or null.

Which function is displayed by default in the status bar (if at least 2 numerical cells are selected)?

average

When using COUNTIF function, what must be done to the criteria argument?

enclose it in double quotes

Which choice is a valid way to name a range in Excel?

sales (not table top or D1 or 1Excel)

Resources

support.office.com

When a scenario summary is created, what happens to the scenarios?

They are deleted.

Performing What If Analysis

Create a Scenario without changing data in your spreadsheet - Data - Forecast Group (What If Analysis) - Scenario Manager - Add Scenario (enter options to see results), you can create a second scenario (name it, set up parameters) - Select Scenario - Show - CNTRL Z - removes scenarios - CNTRL Y - if last option removed will reinstate - Change max of 32 cells in a given scenario - ALWAYS WORK IN A BACKUP COPY OF YOUR DATA!! - If you save it, it will overwrite your data. Edit, delete & summarize scenarios - Look at scenario options - Edit by clicking the scenario, edit - Click summary button - you can select worksheet, pivot table or report - Creating a scenario summary deletes the scenario from your workbook Create a single element data table - Highlight info - What If - Data Table - formulas are part of the array table, not able to edit it Create a two variable data table - select cells - Data - What If - Data Table - identify row & column input cells Find Target Values Using Goal Seek - Data - What If - GoalSeek - set cell - to value (change value) - by changing cell (i.e. how many of a particular item do you need to cell to reach a certain percentage)

Which option in the Formula Auditing group allows you to step through a formula, one calculation at a time?

Evaluate Formula

Introducing Excel 2019 Formulas & Functions

Formulas (calculation type into a cell) - a statement that calculates a value - always begins with an equal sign - can also refer to worksheet cells (current or other worksheet or workbook) Functions (built in calculation that has a name & gives an answer based on the values you provide) - a built-in calculation - follows a known syntax - accepts inputs, or arguments, to calculate its result - i.e. =SUM(D3:D5,F3:F5) = signifies start of formula SUM is the function name D3:D5 and F3:F5 are the arguments optional arguments are displayed in [ ] Operator Precedence - used for negation - % used to indicate a percentage - ^ used to indicate an exponent - and/ used multiplication & division - + and - used to indicated addition and subtraction - Parentheses () change order of operations - <> checks if a value is not equal to another

Where is the Name Manager located in Excel?

Formulas tab

Preparing Data for Analysis in Excel

Import data into Excel - Data - Get & Transfer Data group - CSV (comma separated value) is one option - creates data connection to the source file, and can check contents of file (whether it changed or not) External Data Range Properties - select cell - Data - Queries & Connections group - Properties - select options - Data - Refresh or Refresh All Clean Data Imported into Excel - use find and replace - there are characters that will not translate into Excel - Clean Function - =CLEAN(A1) - Clear extra spaces = TRIM(A1) - Change all words to either upper or lower case = UPPER or =LOWER - Capitalize first letter of every word =PROPER(A1) - Combine them =proper(trim(clean(A2)))

Which function displays the number in the middle of a sorted list of number?

MEDIAN

Performing What-If Analysis

Manage Excel formula error indicators - green triangle top left corner of cell = error - #DIV/0! = trying to divide by a cell that does not have a value entered - Hover mouse over yellow action button to determine error (may be making suggestions) - To get rid of the yellow action button, you can select ignore error - Error Checking Rules: File - Options - Formulas - Error Checking Section options, you can reset any errors you have ignored, you can set rules Trace formula precedents and dependents - Precedent: cell providing values - Dependent: receives values from other cells - Select cell - Formula tab - Trace Precedents - shows the path, you can do the same for dependents - can use these across worksheets and workbooks - select dotted line and it will take you to the source doc - When done with arrows, click Remove Arrows Identify & Trace Errors select cell - Formulas - Formula Auditing - Trace Error click Remove Arrows button afterward Watch cell values - Formula - Watch window - Formula & other details display - Great for formula debugging or scenario analyses

Basic Summary Functions

Mode - most common value or multiples if they happen to exist Formulas to summarize cells conditionally - - Use IF function - SUMIF / AVERAGEIF / COUNTIF /SUMIFS / AVERAGEIFS / COUNTIFS Summarize data on the status bar - summaries appear near bottom right worksheet - customize what you want to add or remove from status bar - highlight cell(s) info displays in status bar

Preparing Data for Analysis in Excel (2)

Separate data in one field into other columns - Data - Select Cells - Data Tools - Text to Columns - Convert Text to Columns - Next - Identify 'divider', Next - finish - Flash Fill: start typing & Excel fills in the data for you Create Data Validation Rules - select column, click table column - Data Tab - Data Validation button (under Data Tools) - Select "Allow" and other criteria - Input Message - create title and enter data - Error Alert - select title and error message - You can circle invalid data Use lists to limit values entered into a cell - Create your acceptable list/values - Click cell - Data - Data Tools - Data Validation - Settings - Select List - Select Other Options - Source (select your list)

Which tab in the Data Validation dialog box contains the validation criteria?

Settings

Managing Named Ranges and Excel Tables

Summarize data using name ranges - select cells with data, top left corner name box (shows active cell), click in name box, type in range name, enter = sum(revenue) - Use Name Manager (another option) Formulas - Define Name - New Name Dialog Box - Select cells you want to name - Formulas Tab - Define Names - Create From Selection - follow check boxes - Negative - they cannot change in size to capture new data entered Create dynamic names range - main reason, compatible with Excel 2003 and earlier - define name range so it will expand - Formulas tab on ribbon - Name Manager - Refers To Formula (change to expanded cell range) type in =OFFSET - you cannot have any blank cells in the list or it interrupts the count Edit & Delete Named Ranges - Formulas - Define Names Group - Name Manager - Click & edit range and refers to

Managing Names Ranges & Excel Tables (2)

Summarize list data by creating subtotals - data must be arranged in columns, with headers, and NO blank rows, values sorted in ascending order - click cell - Data - SubTotal (Outline Group) - hide detail buttons will be displayed on the left side of the screen Group and Outline List Data - Highlight rows or columns - Data - Group - Grouping Levels are displayed in left column - Click Ungroup button to ungroup Summarize data using an Excel table - click any cell in data list - Format as Table on home tab - select table style - if you tab at the end of the range, it creates a new row within the table - if you hit enter, you go to a blank cell - Table Tools Design tab appears (turn filter buttons off or on, totals on or off, etc.) - Table Name - can change name under "Table Name" top left corner, part of the Table Tools Design toolbar Create Table References - click any cell in the table for a design tab - =SUM(tablename[column list appears]) - Google Sheets does NOT have table reference abilities VLOOKUP Formulas (Vertical Lookup) - =VLOOKUP(field name for look up item, table array - data without headers, column index number, approx or exact match - if use "True", must be sorted in ascending number use "False"

Which Excel function removes extra spaces from a cell so there is only a single space between words?

TRIM

When using VLOOKUP, what should be the value of the last argument if you want to find an approximate match?

TRUE

What happens to the original cell values if a workbook is saved while a scenario is applied?

The original values are lost and cannot be recovered.