Data Management Fundamentals

When working with any Excel workbook, the first and most critical step is creating a backup of your source data. Right-click on the worksheet tab, select "Move or Copy," check "Create a copy," move it to the end, rename it to "Source_Data_Backup," and then hide this worksheet to prevent accidental modifications.

Essential Shortcuts and View Features

• Filter Toggle: Ctrl+Shift+L enables or disables AutoFilter on the current dataset
• Pivot Table Creation: Insert → Pivot Table from the ribbon
• Slicers: Standalone filtering controls that can exist on separate worksheets while controlling pivot tables, functioning like global reference variables
• Dual Window View: View → New Window → Arrange All → Tiled allows viewing the same worksheet in two separate windows simultaneously
• Windows Split Screen: Win+RightArrow for quick window management

Critical Data Handling Rules

Never store dates in Excel's native date format. Always use text representation instead. This prevents calculation errors and formatting issues when working with date-based data.

The SUBTOTAL function dynamically recalculates based on filtered data, making it invaluable for working with large datasets that require frequent filtering.

Business Metrics Framework

Result Metrics

• GMV (Gross Merchandise Value)
• Merchant Revenue
• Revenue Rate: Merchant Revenue ÷ GMV
• Valid Orders
• Invalid Orders
• Average Order Value: GMV ÷ Valid Orders

Process Metrics

• Impression Count
• Store Visit Count
• Visit Conversion Rate: Store Visit Count ÷ Impression Count
• Order Count
• Order Conversion Rate: Order Count ÷ Store Visit Count
• Marketing Spend Ratio: Total CPC Cost ÷ GMV
• Business Progress: Total GMV ÷ Target

Data Validation and Conditional Formatting

Creating Dropdown Lists

Data → Data Validation → Allow: Sequence → Source: Enter options separated by commas

Progress Bars

Home → Conditional Formatting → New Rule → Format Based on Data Bars → Set minimum to 0 and maximum to 1 → Choose gradient fill

Color-Coded Values

For values above zero: Home → Conditional Formatting → New Rule → Format only cells containing → Cell Value greater than 0 → Format font color to green

For values below zero: Same path but Cell Value less than 0 → Format font color to red

Icon Sets

Home → Conditional Formatting → New Rule → Icon Sets → Configure as needed → Remember to change type to Number

Managing Existing Rules

Home → Conditional Formatting → Manage Rules

Highlighting Below-Average Values

Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format:

=$C13<Average($C$13:$C$19)

Apply bold and underline formatting to highlight GMV values below the average.

Comprehensive Function Examples

SUMIF and SUMIFS Functions

=SUMIF('DataSource_Jan_Aug'!A:A,B16,'DataSource_Jan_Aug'!J:J)

Syntax: SUMIF(range, criteria, sum_range) — where the first parameter is the range to evaluate, the second is the matching criteria, and the third is the range to sum.

=SUMIFS('DataSource_Jan_Aug'!J:J,'DataSource_Jan_Aug'!A:A,B15)

SUMIFS extends SUMIF by supporting multiple criteria.

=SUMIFS('DataSource_Jan_Aug'!$J:$J,'DataSource_Jan_Aug'!$A:$A,$B30,'DataSource_Jan_Aug'!$H:$H,"Meituan")

This formula sums column J where column A equals B30 AND column H equals "Meituan". Multiple condition pairs can be added sequentially.

Absolute and Relative References

Precede cell references with $ to lock them. Press F4 to toggle through reference types:

=SUMIF('DataSource_Jan_Aug'!A:A,B15,'DataSource_Jan_Aug'!J:J)
=SUMIF('DataSource_Jan_Aug'!$A:$A,$B15,'DataSource_Jan_Aug'!$J:$J)
=SUMIF('DataSource_Jan_Aug'!$A:$A,$B$15,'DataSource_Jan_Aug'!$J:$J)

The first formula uses relative references and adjusts when copied. The second locks the column letters but allows row changes. The third locks both column and row, creating an absolute reference.

Date Calculations

=EDATE("2020/7/31",-1)

Returns: 2020/6/30 (exactly one month prior)

=DATE(YEAR("2020/7/31"),MONTH("2020/7/31")-1,DAY("2020/7/31"))

Returns: 2020/7/1 (demonstrates this method lacks precision)

Date Range Queries

Use ">="& concatenated with date values:

=SUMIFS('DataSource_Jan_Aug'!J:J,'DataSource_Jan_Aug'!H:H,"Meituan",'DataSource_Jan_Aug'!A:A,">="&DATE(YEAR(A39),MONTH(A39),1),'DataSource_Jan_Aug'!A:A,"<="&DATE(YEAR(A39),MONTH(A39)+1,1)-1)

This calculates sums within a specific month range for Meituan platform data.

Nested IF Statements

=IF(H80=0,IF(I80=0,"A&B are 0","A is 0,B is 1"),IF(I80=1,"A&B are 1","A is 1,B is 0"))

Nested IFs evaluate condditions sequentially, returning different values based on multiple criteria.

VLOOKUP Function

=VLOOKUP(A96,'DataSource_Jan_Aug'!D:E,2,0)

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

• lookup_value: What to search for
• table_array: Range containing both lookup and return columns
• col_index_num: Column number to return (starts at 1)
• range_lookup: 0 for exact match, 1 for approximate

=VLOOKUP(H96&"*",E96:F103,2,0)

Using wildcards with concatenation for partial matching.

=VLOOKUP(H99&"??",E96:F103,2,0)

Using "??" placeholders for exactly two characters.

MATCH Function

=MATCH(A113,A112:A126,0)

Returns the position of lookup_value with in the specified range. The third parameter (0) specifies exact match. Works for both row and column searches.

INDEX Function

=INDEX(A95:B103,4,1)

Returns the value at the intersection of row and column within a range. Both parameters start from 1.

INDEX-MATCH Combination

This is a powerful combination that overcomes VLOOKUP limitations:

=INDEX('DataSource_Jan_Aug'!$1:$1048576,MATCH($A112,'DataSource_Jan_Aug'!$I:$I,0),MATCH(C$111,'DataSource_Jan_Aug'!$1:$1,0))

This formula performs a two-way lookup: MATCH finds the row position in column I, MATCH finds the column position in row 1, and INDEX retrieves the corresponding value.

=SUMIFS(INDEX('DataSource_Jan_Aug'!$A:$X,0,MATCH('Functions_Complete'!H$111,'DataSource_Jan_Aug'!$A$1:$X$1,0)),'DataSource_Jan_Aug'!$I:$I,'Functions_Complete'!$B112)

This advanced formula dynamically selects which column to sum based on the header match, then sums values where another column matches a specific criterion.

Result Integration

When all formulas are properly implemented, the spreadsheet becomes fully interactive — modifying any value automatically updates all related calculations throughout the workbook. This creates a dynamic data model where changes propagate instantly across interconnected formulas, demonstrating the true power of Excel's calculation engine.