Excel Pro Flashcards
create custom number formats
Customize numbers in cells to have a decimal, dollar sign, comma etc.
home tab/ number group/ more number formats
Apply International Currency Formats
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.
Outline Data
is to outline data that is important within a group of data w. collapse button.
Clk data tab/outline group/group button/auto outline
Insert Subtotals
is to outline a group of data to collapse showing the subtotal of data.
data tab/ outline group/ subtotal button/format function
Insert References
is to insert cell addresses within cell within formulas reflecting the row or column calculation.
example(=sumC6:F3), where the semicolon represents through
Reference Data in Another Workbook
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**
Reference Data by Using Structured References
is to reference data from a table name (structured reference).
Consolidate Data
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. *
Configure Data Validation
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
Analyze Data Excel 365
formerly known as “ideas” it anaylzes data and give ideas like tables, charts etc.
home tab/ anaylisis group/analyze data button
Power Query Editor
Import, Transform, and Connect to Data
****data tab/ get data button/ find file, clk import/ choose data from Navigator window {load-import} {transform data-opens Power Query Editor}
Power Query Editor
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.
Power Query Editor
Combine and Display Data
data tab/get data button/launch power query, home tab, combine group, merge queries
OR
**get data button/combine queries/ merge **
Format Text by Using RIGHT, LEFT, and MID Functions
copying text from either side of text using all functions.
**formulas tab/text button/Right function/select the data to copy **
formula
is a user-created expression that performs calculations
function
is a predefined, built-in formula designed for specific tasks.
Functions that convert data in caps, lowercase and proper case letters.
formulas tab/text/upper function- all caps
Format Text by Using the CONCAT Function
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. **
Perform Logical Operations by Using the IF Function
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
Perform Logical Operations by Using the AVERAGEIF Function
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
Perform Logical Operations by Using the SUMIF Function
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
Perform Statistical Operations by Using the COUNTIF Function
counts the number of cells within a specified range that meet a given criterion.
**statiscal button, COUNTIF/ critera **
Perform Statistical Operations by Using the SUMIFS Function
calculates the sum of a range of cells based on multiple criteria, unlike SUMIF which only supports one criterion.
more function button, SUMIFS
Perform Statistical Operations by Using the AVERAGEIFS Function
calculates the average of cells in a range that meet multiple criteria
**more functions button/statiscal button, AVERAGEIFS **
Perform Logical Operations by Using AND, OR, and NOT Functions
NOT function is a logical function that reverses the value of its argument, meaning it converts TRUE to FALSE and FALSE to TRUE.
Reference the Date and Time by Using the NOW and TODAY Functions
formula tab/function library/date&time button/
Serialize Numbers by Using DATE and TIME Functions
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
Perform What-If Analysis by Using Goal Seek
It’s tool that helps with calculations by the criteria
data tab/forcast group/what-IF analysis button/Goal Seek
Perform What-If Analysis by Using Scenario Manager
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
Calculate Data by Using Financial Functions: PMT
**formula tab/financial button/PMT(payment)/criteria **
Calculate Data by Using Financial Functions: PPMT & IPMT
PPMT (principle payment) & IPMT (Interest payment)
**funciton library/ financial button **
functions
Calculate Data by Using Financial Functions: FV
formulas tab/ financial button
error checking
Display Formulas
Displaying all the formulas within the sheet.
formulas tab/ formula auditing/ show formulas
Trace Precedence and Dependence
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
Evaluate Formulas
formula tab/formula auditing group/ evaulate formula button
Validate Formulas by Using Error Checking Rules
**formula auditing group/ error checking button **
Error checking
Monitor Cells and Formulas by Using the Watch Window
formula auditing / watch window button
conditional formatting
Apply Conditional Formatting
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
Create Custom Conditional Formatting Rules
conditional format/ new rule
Create Conditional Formatting Rules That Use Formulas
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”
Insert Sparklines
are tool for little charts relating to a row or column.
insert tab/ sparkline group/creating window
Create a New Chart
is tool to show visual data.
insert tab/ charts group
Add Additional Data Series
to a Chart
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.
Switch Between Rows and Columns in Source Data
To switch up the data’s position within a chart.
Select chart/chat tools/ design tab/ data group/ switch row/column button
Analyze Data by Using Quick Analysis
analyzes data range and suggest charts/formatting/tables etc.
clk cell in table/ right clk drop down menu,Quick analysis
Resize Charts
Clk chart and drag to desired size by the white dots.
Move Charts to a Chart Sheet
**select object (chart)/ Chart tools/design tab/ Location group/Move chart button/ Move chart windown/ clk New sheet **
Add and Modify Chart Elements
is to change Chart Elements (parts of chart).
clk chart/ Chart Tools/ Design tab/Chart Layout/ Add Chart Elements
Apply Chart Layouts
clk chart/ chart tools/design tab/ chart layout/ Quick layout button
Apply Chart Styles
**Chart Tools/ Design tab/ **
Add Trendlines to Charts
Adding lines that reveal a trend.
Design tab/ Add Chart Element/ trendline
Create Dual-Axis Charts
is an chart element between bars/axis bar for extra measure.
insert tab/ charts/insert column or bar chart/
