Excel Concepts Flashcards
Data Analysis steps using Excel Features.
1) Understand the Problem.
2) Data Collection : Using Power Query or Excel’s Data tab. External Data Connections: Import data from databases.
3) Data Cleaning :
Text-to-Columns, Find and Replace,Remove Duplicates, Data Validation by restricting the types of data entered in cells, Remove Blank/Empty Cells or Rows, Flash Fill, Power Query to automate.
4) EDA : Descriptive Statistics using Analysis ToolPak, Pivot Tables and PivotCharts, Conditional Formatting, Charts and Graphs.
5) Modeling in Excel : Using Toolpak for Regression Analysis, Solver for optimization with certain constraints, Trend() for trend analysis, Forecast() for predictive modeling, Goal Seek to test different inputs for a target output.
6) Evaluation : Error Metrics like RSQ() and MSE() to evaluate model performance and fit.
Residual Plots: Visualize the residuals (errors) using scatter plots to identify patterns in model errors. Scenario Manager: Test different input values to evaluate outcomes and sensitivity.
7) Analyze and Interpret Results : Summary Statistics using PivotTables and PivotCharts, Correlation Functions using CORREL() to identify relationships between variables, Charts to visualize results and reveal patterns.
8) Derive Actionable Recommendations : PivotTables to summarize key insights and metrics, Solver to suggest optimal actions based on constraints, What-If Analysis: Use Scenario Manager and Data Tables.
9) Communicate recommendations : Dashboards, Charts. Conditional Formatting, Comments.
10) Monitor continuously : Data Connections to automate updating of the analysis with refreshed data using Power Query connections, Conditional Formatting to monitor key indicators and trends with alerts for specific conditions, Solver and Goal Seek help adjust models dynamically based on new data to continuously optimize results.
11) Document the process : Comments/Notes help add notes to document changes, assumptions, and decisions, Track Changes allows to enable tracking to see what changes were made and by whom, especially useful in collaborative environments, Macros to automate documentation processes by embedding comments and notes or generating reports.
Excel functions vs Formulas diff
In Excel, formulas and functions are both used to perform calculations, but they have some key differences:
Definition: A formula is an equation created by the user, while a function is a predefined calculation.
Customization: Functions cannot be customized, but formulas can be.
Syntax: Formulas have no structure or syntax, but functions do.
Parameters: Formulas cannot have pre-defined parameters, but functions do
.
Use: Formulas can be used to calculate values within a range of cells or a single cell, while functions can be used to find maximum or minimum values throughout the spreadsheet.
If sum is applied and output is off, common things to check :
Common Issues to Check:
- Hidden Rows or Filters
- Text Values in the Range
- Hidden decimals or rounding issues that could affect the sum. - Empty Cells or Special Characters
- Check the Cell Format and ensure they are set to “Number.”
Ways to apply the same formula to the entire column :
There are multiple ways to add formulas:
- Dragging down the fill handle.
- Double click the fill handle: select the cell with the formula and double click the fill handle.
- Shortcut: select the cell with the formula and the empty cells below it. Press CTRL + D to apply the formula. Make sure you are not selecting anything above the formula cell.
- Copy-pasting using Ctrl+C and Ctrl+V.
Line break shortcut (for new line in a cell as Enter doesnt work)
alt+enter
Applying function on multiple columns :
Option 1: Sum Multiple Columns for a Single Row
=SUM(A2:C2)
Option 2: Sum Each Column Separately
=SUM(A:A, B:B, C:C)
Option 3: Sum Multiple Columns for Multiple Rows
=SUM(A2:C10)
Option 4: Sum Each Column in a Table or Array
=SUM(A2:A10), SUM(B2:B10), SUM(C2:C10)
Option 5: AutoSum for Multiple Columns
Same as Option 4 but without requiring to create a formula
