Excel Pro

Question 1

create custom number formats

Answer 1

Customize numbers in cells to have a decimal, dollar sign, comma etc.
home tab/ number group/ more number formats

Question 2

Apply International Currency Formats

Answer 2

To format present currency in cells into the international currency.
select cells: Clk home tab/number group/format cell window/drop down to desired country/select & apply
Note: Excel doesn’t do conversion.

Question 3

Outline Data

Answer 3

is to outline data that is important within a group of data w. collapse button.
Clk data tab/outline group/group button/auto outline

Question 4

Insert Subtotals

Answer 4

is to outline a group of data to collapse showing the subtotal of data.
data tab/ outline group/ subtotal button/format function

Question 5

Insert References

Answer 5

is to insert cell addresses within cell within formulas reflecting the row or column calculation.
example(=sumC6:F3), where the semicolon represents through

Question 6

Reference Data in Another Workbook

Answer 6

To input cell addresses for calculations from other workbook’s sheets.
input = sum( open parenthese /select cell in other workbook sheet reference, input comma in formula bar prior to the next workbook sheet reference/ input close parenthes in formula bar and hit enter back to the orignal sheet**

Question 7

Reference Data by Using Structured References

Answer 7

is to reference data from a table name (structured reference).

Question 8

Consolidate Data

Answer 8

To consolidate data from other worksheets/workbooks into a designated sheet
Data tab/data tools group/consolidate button/clk what function, clk reference/select data/add button of selected data, selecting the next sheet/when finish selecting clk ok in window. *

Question 9

Configure Data Validation

Answer 9

is to stipulate certain data w. error message into specific cell address/row/column.
data tab/ data tools group/ data valadation button/ settings window, inputting tittle, input message and error message

Question 10

Analyze Data Excel 365

Answer 10

formerly known as “ideas” it anaylzes data and give ideas like tables, charts etc.
home tab/ anaylisis group/analyze data button

Question 11

Power Query Editor

Import, Transform, and Connect to Data

Answer 11

****data tab/ get data button/ find file, clk import/ choose data from Navigator window {load-import} {transform data-opens Power Query Editor}

Question 12

Power Query Editor

Answer 12

is a data connectivity and preparation tool that allows users to import, clean, and transform data from various sources, streamlining the data preparation process and enabling more effective analysis.

Question 13

Power Query Editor

Combine and Display Data

Answer 13

data tab/get data button/launch power query, home tab, combine group, merge queries
OR
**get data button/combine queries/ merge **

Question 14

Format Text by Using RIGHT, LEFT, and MID Functions

Answer 14

copying text from either side of text using all functions.
**formulas tab/text button/Right function/select the data to copy **

Question 15

formula

Answer 15

is a user-created expression that performs calculations

Question 16

function

Answer 16

is a predefined, built-in formula designed for specific tasks.

Question 17

```

Format Text by Using UPPER, LOWER, and PROPER Functions

Answer 17

Functions that convert data in caps, lowercase and proper case letters.
formulas tab/text/upper function- all caps

Question 18

Format Text by Using the CONCAT Function

Answer 18

Concat function- is to join text together.
**formula tab/function library/text button [concat] functin argument/selected cells in text box,clk ok when completed. **

Question 19

Perform Logical Operations by Using the IF Function

Answer 19

compares a value to what is expected and returns a result based on whether the comparison is true or false.
clk selected cell /formulas tab/function library/logical button, [IF}/ function argument, logical test

Question 20

Perform Logical Operations by Using the AVERAGEIF Function

Answer 20

calculates the average of cells that meet a specific condition (criteria) within a given range.
formula tab/function library/more functions buttons, AVERAGEIF/arguement win, input critera box

Question 20

Perform Logical Operations by Using the SUMIF Function

Answer 20

sums values within a range that meet a specified condition, allowing you to calculate selective totals based on criteria.
formula tab/function library /insert functions/search SUMIF/fuction arguments

Question 21

Perform Statistical Operations by Using the COUNTIF Function

Answer 21

counts the number of cells within a specified range that meet a given criterion.
**statiscal button, COUNTIF/ critera **

Question 22

Perform Statistical Operations by Using the SUMIFS Function

Answer 22

calculates the sum of a range of cells based on multiple criteria, unlike SUMIF which only supports one criterion.
more function button, SUMIFS

Question 23

Perform Statistical Operations by Using the AVERAGEIFS Function

Answer 23

calculates the average of cells in a range that meet multiple criteria
**more functions button/statiscal button, AVERAGEIFS **

Question 24

Perform Logical Operations by Using AND, OR, and NOT Functions

Answer 24

NOT function is a logical function that reverses the value of its argument, meaning it converts TRUE to FALSE and FALSE to TRUE.

Question 25

Reference the Date and Time by Using the NOW and TODAY Functions

Answer 25

***formula tab/function library/date&time button/***

Question 26

Serialize Numbers by Using DATE and TIME Functions

Answer 26

There a excel serial number ex. 43537.68598 that can be converted to dates/times ex. 3/13/19 & time. ***formulas tab/function library/ date & time button***

Question 27

Perform What-If Analysis by Using Goal Seek

Answer 27

It's tool that helps with calculations by the criteria ***data tab/forcast group/what-IF analysis button/Goal Seek***

Question 28

Perform What-If Analysis by Using Scenario Manager

Answer 28

is "What-If Analysis" tool that allows to create / compare different scenarios by changing multiple variables simultaneously, saving and switching between them to see how various input values affect the final result. *****data tabe/forcast group/what-IF anaylsis/scenerario manager*****

Question 29

Calculate Data by Using Financial Functions: PMT

Answer 29

***formula tab/financial button/PMT(payment)/criteria ***

Question 30

Calculate Data by Using Financial Functions: PPMT & IPMT

Answer 30

PPMT (principle payment) & IPMT (Interest payment) ***funciton library/ financial button ***

Question 31

# functions Calculate Data by Using Financial Functions: FV

Answer 31

***formulas tab/ financial button***

Question 32

# error checking Display Formulas

Answer 32

Displaying all the formulas within the sheet. ***formulas tab/ formula auditing/ show formulas***

Question 33

Trace Precedence and Dependence

Answer 33

to trace errors from other preceding cells in the formula error and trace it's depending cells to the formula. Formula tab/ formula auditing group/ precendce button

Question 34

Evaluate Formulas

Answer 34

formula tab/formula auditing group/ evaulate formula button

Question 35

Validate Formulas by Using Error Checking Rules

Answer 35

***formula auditing group/ error checking button ***

Question 36

# Error checking Monitor Cells and Formulas by Using the Watch Window

Answer 36

***formula auditing / watch window button***

Question 37

# conditional formatting Apply Conditional Formatting

Answer 37

allows you to automatically apply formatting (like colors, font styles, or borders) to cells based on their content or a defined condition, making it easier to highlight patterns and trends in your data. ***home tab/ styles group /conditional formatting button***

Question 38

Create Custom Conditional Formatting Rules

Answer 38

conditional format/ new rule

Question 39

Create Conditional Formatting Rules That Use Formulas

Answer 39

allows you to automatically format cells based on the results of a formula, highlighting or changing the appearance of cells that meet specific criteria. ***conditional formula button/ new rule box/"use formula to determine which cells to format"***

Question 40

Insert Sparklines

Answer 40

are tool for little charts relating to a row or column. ***insert tab/ sparkline group/creating window***

Question 41

Create a New Chart

Answer 41

is tool to show visual data. ***insert tab/ charts group***

Question 42

Add Additional Data Series to a Chart

Answer 42

When clk a a chart the chart tools appears: ***Design Tab/ data group/ select data button /Data source window/add button*** Note: data range is a selection of data series of a row or column.

Question 43

Switch Between Rows and Columns in Source Data

Answer 43

To switch up the data's position within a chart. ***Select chart/chat tools/ design tab/ data group/ switch row/column button***

Question 44

Analyze Data by Using Quick Analysis

Answer 44

analyzes data range and suggest charts/formatting/tables etc. ***clk cell in table/ right clk drop down menu,Quick analysis***

Question 45

Resize Charts

Answer 45

Clk chart and drag to desired size by the white dots.

Question 46

Move Charts to a Chart Sheet

Answer 46

***select object (chart)/ Chart tools/design tab/ Location group/Move chart button/ Move chart windown/ clk New sheet ***

Question 47

Add and Modify Chart Elements

Answer 47

is to change Chart Elements (parts of chart). ***clk chart/ Chart Tools/ Design tab/Chart Layout/ Add Chart Elements***

Question 48

Apply Chart Layouts

Answer 48

***clk chart/ chart tools/design tab/ chart layout/ Quick layout button***

Question 49

Apply Chart Styles

Answer 49

***Chart Tools/ Design tab/ ***

Question 50

Add Trendlines to Charts

Answer 50

Adding lines that reveal a trend. ***Design tab/ Add Chart Element/ trendline***

Question 51

Create Dual-Axis Charts

Answer 51

is an chart element between bars/axis bar for extra measure. ***insert tab/ charts/insert column or bar chart/***