Excel for Project Management

Cheatsheet Content

### Logical Functions Essential for conditional logic in project tracking. - **IF(logical_test, value_if_true, value_if_false)** - Checks a condition. - *Example:* `=IF(A2="Complete", "Done", "Pending")` - **AND(logical1, [logical2], ...)** - Returns TRUE if all arguments are TRUE. - *Example:* `=IF(AND(B2>DATE(2023,1,1), C2="High"), "Urgent", "Normal")` - **OR(logical1, [logical2], ...)** - Returns TRUE if any argument is TRUE. - *Example:* `=IF(OR(D2="Blocked", E2="Risk"), "Flag", "Proceed")` - **NOT(logical)** - Reverses the logic of its argument. - *Example:* `=IF(NOT(ISBLANK(F2)), "Has Notes", "No Notes")` - **IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)** - Checks multiple conditions and returns the value corresponding to the first TRUE condition. - *Example:* `=IFS(G2 ### Lookup & Reference Functions Crucial for retrieving data from tables or ranges. - **VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])** - Searches for a value in the first column of a table array and returns a value in the same row from a specified column. - *Example:* `=VLOOKUP(A2, 'Task Data'!A:C, 3, FALSE)` (Finds task status from 'Task Data' sheet) - **HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])** - Similar to VLOOKUP, but searches in the first row. - *Example:* `=HLOOKUP("Budget", 'Project Metrics'!1:5, 4, FALSE)` - **XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])** - More flexible and powerful than VLOOKUP/HLOOKUP. - *Example:* `=XLOOKUP(B2, 'Resources'!A:A, 'Resources'!C:C, "Not Found", 0)` (Finds resource email) - **INDEX(array, row_num, [column_num])** - Returns the value of an element in a table or array. Often used with MATCH. - *Example:* `=INDEX('Task List'!B:B, 5)` (Returns the 5th value in column B) - **MATCH(lookup_value, lookup_array, [match_type])** - Returns the relative position of an item in an array that matches a specified value. - *Example:* `=MATCH("Priority", 'Headers'!A1:Z1, 0)` (Finds column number for "Priority") - **INDEX/MATCH Combination** - More flexible than VLOOKUP, can look up left, right, up, or down. - *Example:* `=INDEX('Task Data'!C:C, MATCH(A2, 'Task Data'!A:A, 0))` (Same as VLOOKUP example, but more robust) ### Text Functions Useful for cleaning and manipulating text strings, like task names or codes. - **CONCAT/CONCATENATE(text1, [text2], ...)** - Joins several text strings into one text string. - *Example:* `=CONCAT(A2, " - ", B2)` (Combines Task ID and Name) - **LEFT(text, [num_chars]) / RIGHT(text, [num_chars])** - Extracts a specified number of characters from the beginning/end of a text string. - *Example:* `=LEFT(C2, 3)` (Extracts first 3 characters of a Task Code) - **MID(text, start_num, num_chars)** - Extracts a specified number of characters from a text string starting at the position you specify. - *Example:* `=MID(D2, 5, 2)` (Extracts 2 characters starting from the 5th position) - **FIND(find_text, within_text, [start_num]) / SEARCH(find_text, within_text, [start_num])** - Returns the starting position of one text string within another text string. FIND is case-sensitive, SEARCH is not. - *Example:* `=FIND("PM", E2)` (Finds "PM" in a string) - **LEN(text)** - Returns the number of characters in a text string. - *Example:* `=LEN(F2)` - **TRIM(text)** - Removes all spaces from text except for single spaces between words. - *Example:* `=TRIM(G2)` - **SUBSTITUTE(text, old_text, new_text, [instance_num])** - Replaces existing text with new text in a string. - *Example:* `=SUBSTITUTE(H2, "Pj", "Project")` - **TEXT(value, format_text)** - Converts a value to text in a specific number format. - *Example:* `=TEXT(TODAY(), "yyyy-mm-dd")` (Formats today's date) ### Date & Time Functions Critical for scheduling, tracking deadlines, and calculating durations. - **TODAY()** - Returns the current date. Volatile, recalculates every time the workbook changes. - *Example:* `=TODAY()` - **NOW()** - Returns the current date and time. Volatile. - *Example:* `=NOW()` - **DATE(year, month, day)** - Returns the sequential serial number that represents a particular date. - *Example:* `=DATE(2023, 10, 26)` - **DATEDIF(start_date, end_date, unit)** - Calculates the number of days, months, or years between two dates. - *Units:* "Y" (years), "M" (months), "D" (days), "YD" (days excluding years), "MD" (days excluding years & months), "YM" (months excluding years). - *Example:* `=DATEDIF(A2, B2, "D")` (Days between start and end) - **NETWORKDAYS(start_date, end_date, [holidays])** - Returns the number of whole working days between two dates. Weekends (Sat/Sun) are excluded. - *Example:* `=NETWORKDAYS(C2, D2)` (Working days for a task) - **WORKDAY(start_date, days, [holidays])** - Returns the date before or after a specified number of working days. - *Example:* `=WORKDAY(E2, 10)` (10 working days after start date) - **EOMONTH(start_date, months)** - Returns the serial number for the last day of the month before or after a specified number of months. - *Example:* `=EOMONTH(F2, 0)` (Last day of the current month) - **DATEVALUE(date_text)** - Converts a date in the form of text to a serial number. - *Example:* `=DATEVALUE("2023-12-31")` ### Statistical Functions For analyzing project data, progress, and resource allocation. - **COUNT(value1, [value2], ...)** - Counts the number of cells that contain numbers. - **COUNTA(value1, [value2], ...)** - Counts the number of cells that are not empty. - *Example:* `=COUNTA(A:A)` (Count all tasks in column A) - **COUNTBLANK(range)** - Counts the number of empty cells in a range. - *Example:* `=COUNTBLANK(B:B)` (Count unassigned tasks) - **COUNTIF(range, criteria)** - Counts the number of cells within a range that meet a given condition. - *Example:* `=COUNTIF(C:C, "Complete")` (Count completed tasks) - **COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)** - Counts cells that meet multiple criteria. - *Example:* `=COUNTIFS(D:D, "High", E:E, "Open")` (High priority, open tasks) - **SUM(number1, [number2], ...)** - **SUMIF(range, criteria, [sum_range])** - Sums the values in a range that meet a specified criterion. - *Example:* `=SUMIF(F:F, "Approved", G:G)` (Sum budget for approved items) - **SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)** - Sums cells that meet multiple criteria. - *Example:* `=SUMIFS(H:H, I:I, "Q1", J:J, "Actual")` (Sum Q1 actual costs) - **AVERAGE(number1, [number2], ...)** - **AVERAGEIF(range, criteria, [average_range])** - **AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)** - **MAX(number1, [number2], ...)** / **MIN(number1, [number2], ...)** - Returns the largest/smallest value in a set of values. - *Example:* `=MAX(K:K)` (Latest completion date) - **LARGE(array, k)** / **SMALL(array, k)** - Returns the k-th largest/smallest value in a data set. - *Example:* `=LARGE(L:L, 3)` (3rd largest value) ### Financial Functions (Basic PM Applications) While primarily financial, some functions can aid in project cost analysis. - **PMT(rate, nper, pv, [fv], [type])** - Calculates the payment for a loan based on constant payments and a constant interest rate. Can be adapted for recurring project costs. - *Example:* `=PMT(5%/12, 36, 10000)` (Monthly payment for a $10,000 project component) - **FV(rate, nper, pmt, [pv], [type])** - Returns the future value of an investment. Useful for forecasting project cost growth. - *Example:* `=FV(3%/12, 60, -100, -5000)` (Future value of savings/investment for project) - **PV(rate, nper, pmt, [fv], [type])** - Returns the present value of an investment. Useful for evaluating current worth of future project cash flows. - *Example:* `=PV(6%/12, 48, -200, 0)` (Present value of future project expenses) ### Array Functions (Dynamic Arrays & Legacy) Process multiple values and return multiple results, or a single result based on an array. - **New Dynamic Array Functions (Microsoft 365/Excel 2021+)** - **FILTER(array, include, [if_empty])** - Filters a range of data based on criteria. - *Example:* `=FILTER(A2:C10, C2:C10="Overdue", "No Overdue Tasks")` - **SORT(array, [sort_index], [sort_order], [by_col])** - Sorts a range or array. - *Example:* `=SORT(A2:C10, 2, -1)` (Sorts by second column, descending) - **UNIQUE(array, [by_col], [exactly_once])** - Returns unique values from a range or array. - *Example:* `=UNIQUE(B2:B10)` (List of unique assignees) - **SORTBY(array, by_array1, [sort_order1], ...)** - Sorts a range or array based on the values in a corresponding range or array. - *Example:* `=SORTBY(A2:C10, C2:C10, -1, B2:B10, 1)` (Sort by status descending, then by assignee ascending) - **XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])** - Returns the relative position of an item in an array (similar to MATCH, but more powerful). - *Example:* `=XMATCH("Critical", D2:D10)` - **Legacy Array Formulas (Ctrl+Shift+Enter - CSE)** - Often replaced by newer functions, but still useful for older versions or complex scenarios. - *Example (Average of top N values):* `{=AVERAGE(LARGE(A2:A10, {1,2,3}))}` - entered with Ctrl+Shift+Enter. ### Data Validation & Conditional Formatting Enhance data entry and visual project tracking. - **Data Validation (Data Tab > Data Tools > Data Validation)** - **List:** Restrict input to a predefined list (e.g., project phases, priority levels). - *Formula:* `Source: "Planning,Execution,Closure"` or `=$A$1:$A$5` - **Date:** Ensure dates fall within a range. - *Formula:* `Start date: =TODAY()`, `End date: =TODAY()+365` - **Custom:** Use formulas for complex validation rules. - *Formula:* `=AND(LEN(A2)=5, ISNUMBER(A2))` (5-digit numeric task ID) - **Conditional Formatting (Home Tab > Styles > Conditional Formatting)** - **Highlight Cells Rules:** - *Example:* Highlight tasks due in the next 7 days: `Cell Value > greater than or equal to =TODAY()` and `Cell Value ""` - **Top/Bottom Rules:** Identify top/bottom performers or costs. - **Data Bars, Color Scales, Icon Sets:** Visual representation of progress, status, or values. - *Example:* Progress bar for % Complete. - **New Rule (Use a formula to determine which cells to format):** - *Example:* Highlight entire row if task is "Blocked": `=$C2="Blocked"` (Apply to `$A$2:$Z$100`) ### Error Handling Make your spreadsheets robust and user-friendly. - **IFERROR(value, value_if_error)** - Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. - *Example:* `=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Not Found")` - **ISERROR(value)** - Checks whether a value is an error (`#N/A`, `#VALUE!`, `#REF!`, `#DIV/0!`, `#NUM!`, `#NAME?`, or `#NULL!`). - *Example:* `=IF(ISERROR(B2/C2), "Cannot Divide", B2/C2)` - **ISNA(value)** - Checks specifically for the `#N/A` error (often from VLOOKUP/MATCH failures). - *Example:* `=IF(ISNA(XLOOKUP(A2, 'ID'!A:A, 'ID'!B:B)), "ID Missing", XLOOKUP(A2, 'ID'!A:A, 'ID'!B:B))` ### Advanced PM Scenarios & Tips - **Gantt Chart with Conditional Formatting:** - Use `NETWORKDAYS` and `WORKDAY` to calculate task durations. - Apply conditional formatting to highlight dates between `Start Date` and `End Date` for each task. - *Formula (for a cell representing a date in the timeline, assuming Start Date in B2, End Date in C2):* `=AND(D$1>=$B2, D$1 0, "Red", COUNTIF(D:D, "At Risk")>0, "Amber", TRUE, "Green")` - **Resource Allocation Tracking:** - Use `SUMIF`/`SUMIFS` to aggregate hours assigned to each resource across multiple tasks. - *Example:* `=SUMIF('Tasks'!$C:$C, $A2, 'Tasks'!$D:$D)` (Total hours for resource in A2) - **Timeline Tracking:** - Use `OFFSET` (legacy) or `INDEX`/`MATCH` with named ranges to create dynamic ranges for charts or reports. - New Dynamic Arrays like `CHOOSEROWS`/`CHOOSECOLS`/`TAKE`/`DROP` offer even more flexibility for data manipulation. - **Named Ranges:** - Define meaningful names for cells or ranges (Formulas Tab > Defined Names > Define Name). Makes formulas easier to read and maintain. - *Example:* Instead of `Sheet1!$A$2:$A$100`, use `Task_Names`. - **Table Formatting (Ctrl+T):** - Convert your data into an Excel Table. This automatically expands ranges for formulas, makes sorting/filtering easier, and allows for structured references (`=[@[Task Name]]`). - **Pivot Tables:** - Quickly summarize and analyze large datasets (e.g., total budget by phase, count of tasks by assignee and status).