Excel Data Analytics

Cheatsheet Content

1. Data Cleaning & Preparation Remove Duplicates: Data Tab $\rightarrow$ Data Tools Group $\rightarrow$ Remove Duplicates. Text to Columns: Data Tab $\rightarrow$ Data Tools Group $\rightarrow$ Text to Columns. Delimited: Choose a delimiter (comma, tab, space). Fixed Width: Set breaks manually. Find & Replace: Ctrl + H. Use for inconsistencies, typos. TRIM: Removes leading, trailing, and excessive spaces between words. =TRIM(A1) CLEAN: Removes non-printable characters. =CLEAN(A1) UPPER/LOWER/PROPER: Changes text case. =UPPER(A1) Data Validation: Data Tab $\rightarrow$ Data Tools Group $\rightarrow$ Data Validation. Restrict input values. 2. Essential Functions for Analysis SUM, AVERAGE, COUNT, MAX, MIN: Basic aggregate functions. =SUM(A1:A10) COUNTIF/COUNTIFS: Counts cells based on single/multiple criteria. =COUNTIF(A1:A10, ">50") =COUNTIFS(A1:A10, ">50", B1:B10, " SUMIF/SUMIFS: Sums values based on single/multiple criteria. =SUMIF(A1:A10, "Category A", C1:C10) AVERAGEIF/AVERAGEIFS: Averages values based on single/multiple criteria. =AVERAGEIF(A1:A10, ">100") VLOOKUP: Looks up a value in the first column of a table and returns a value from another column in the same row. =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) range_lookup : TRUE (approximate match), FALSE (exact match). Always use FALSE for exact. INDEX & MATCH: More flexible lookup (can look left, multiple criteria). =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) MATCH(lookup_value, lookup_array, [match_type]) : 0 for exact match. IF/IFS: Conditional logic. =IF(logical_test, value_if_true, value_if_false) =IFS(condition1, value1, condition2, value2, ...) AND/OR/NOT: Combine logical tests. =IF(AND(A1>10, B1 CONCATENATE/&: Joins text strings. =CONCATENATE(A1, " ", B1) or =A1&" "&B1 LEFT/RIGHT/MID: Extracts characters from text. =LEFT(A1, 3) LEN: Returns the number of characters in a text string. =LEN(A1) 3. Data Visualization Chart Types: Insert Tab $\rightarrow$ Charts Group. Column/Bar: Comparison between categories. Line: Trends over time. Pie: Proportions of a whole (limited categories). Scatter: Relationship between two numerical variables. Histogram: Frequency distribution of numerical data (Data Analysis Toolpak). Sparklines: Small charts in a single cell. Insert Tab $\rightarrow$ Sparklines Group. Conditional Formatting: Home Tab $\rightarrow$ Styles Group. Highlight cells based on rules. Data Bars, Color Scales, Icon Sets. Highlight Cells Rules (e.g., Greater Than, Text That Contains). Top/Bottom Rules. 4. PivotTables & PivotCharts Create PivotTable: Select data $\rightarrow$ Insert Tab $\rightarrow$ Tables Group $\rightarrow$ PivotTable. Fields: Rows: Categories to group by. Columns: Categories for column headers. Values: Numerical data to aggregate (SUM, COUNT, AVERAGE, etc.). Filters: To filter the entire PivotTable. Calculated Field: Analyze Tab $\rightarrow$ Calculations Group $\rightarrow$ Fields, Items, & Sets $\rightarrow$ Calculated Field. Create custom calculations. Slicers & Timelines: Analyze Tab $\rightarrow$ Filter Group. Interactive filters for PivotTables/Charts. Refresh: Right-click PivotTable $\rightarrow$ Refresh (or Analyze Tab $\rightarrow$ Data Group $\rightarrow$ Refresh). 5. Data Analysis Toolpak Enable: File $\rightarrow$ Options $\rightarrow$ Add-ins $\rightarrow$ Manage: Excel Add-ins $\rightarrow$ Go... $\rightarrow$ Check "Analysis ToolPak". Access: Data Tab $\rightarrow$ Analyze Group $\rightarrow$ Data Analysis. Key Tools: Descriptive Statistics: Provides mean, median, mode, standard deviation, etc. Regression: Linear regression for analyzing relationships between variables. Histogram: Frequency distribution. Correlation: Measures the strength and direction of a linear relationship between two variables. ANOVA: Analysis of Variance (single factor, two factor) for comparing means of two or more groups. t-Test: Compares means of two groups. 6. What-If Analysis Access: Data Tab $\rightarrow$ Forecast Group $\rightarrow$ What-If Analysis. Scenario Manager: Create and manage different sets of input values (scenarios) and view their results. Goal Seek: Find the input value needed to achieve a specific result (goal). Set cell: The cell containing the formula you want to solve. To value: The target value for the Set cell. By changing cell: The input cell you want to adjust. Data Table: Show how changing one or two input variables affects formula results. Row input cell: Cell to substitute for row values. Column input cell: Cell to substitute for column values. 7. Advanced Techniques Power Query: (Get & Transform Data Group on Data Tab) Connect, combine, and refine data from various sources. Transform data: unpivot columns, merge queries, append queries. Power Pivot: (Add-in) Create sophisticated data models. Use Data Analysis Expressions (DAX) for advanced calculations. Handle millions of rows of data. Forecasting: Data Tab $\rightarrow$ Forecast Group $\rightarrow$ Forecast Sheet. Create a new sheet with a forecast chart and underlying data table. 8. Shortcuts & Tips Select All: Ctrl + A Copy/Paste: Ctrl + C / Ctrl + V Cut: Ctrl + X Undo/Redo: Ctrl + Z / Ctrl + Y Fill Down/Right: Ctrl + D / Ctrl + R Insert/Delete Rows/Columns: Ctrl + Shift + + / Ctrl + - Go to Special: Ctrl + G $\rightarrow$ Special... (e.g., Blanks, Formulas) Format Cells: Ctrl + 1 Absolute Reference: F4 (toggles $A$1, A$1, $A$1) Table Formatting: Ctrl + T (converts range to Excel Table) Named Ranges: Formulas Tab $\rightarrow$ Defined Names Group $\rightarrow$ Define Name. Makes formulas more readable.