Unlock powerful insights by using advanced Excel formulas to analyze and visualize complex data with precision.
Let's have a look at some formulas:
1. When you open the live sheet, you'll notice some sheets highlighted in green. These green sheets display live raw data, extracted from your Fresh Projects data. To preserve the integrity of the report, these sheets should not be edited.
2. Table Referencing vs Cell Referencing
Your live data in Fresh Projects is stored in an Excel Table format, rather than a traditional sheet of data.
Unlike a simple range of cells, an Excel Table has a defined name (for example, Monthly_Financials) and clearly labeled column headers. Headers can be referenced directly by their names using square brackets [ ].
For example, Monthly_Financials[Project] refers to the Project column in the Monthly_Financials table.
Differences from traditional cell references:
Traditional cell references (e.g., Sheet1!B2:B100) rely on fixed ranges of cells. If columns are added or the order of columns change, formulas can break which produces #REF! errors.
Table references (e.g., Monthly_Financials[Project]) refers directly to the column name within the table. This immediately shows which column is being referenced.
Excel Tables are dynamic by design. They automatically expand when new data feeds are added, ensuring that formulas adapt smoothly without requiring manual fixes to sheet cell ranges. Excel also recognizes and tracks the column names, so even if the order of columns change, your formulas remain stable.
3. Note: The Month Code is formatted as a text string. When using formulas that reference this field, ensure they account for its text format to avoid calculation errors.
4. The FILTER function is used to return a dynamic list of values that meet specific criteria. It’s especially useful when you want to extract only the rows that match certain conditions — without manually sorting or hiding data.
The FILTER function becomes even more powerful when combined with dropdown menus (Data Validation lists), allowing you to create interactive and dynamic reports.
5. To sum values based on specific conditions in Excel, the SUMIF and SUMIFS functions are essential tools. Use SUMIF when you need to sum values based on a single condition, for example, totaling hours worked by a specific employee, or summing expenses for a particular category.
When you need to apply multiple conditions, SUMIFS is the more powerful option. For instance, if you want to sum projected billing amounts only for a Project Manager during a specific month - you can set both criteria within the formula.
6. VLOOKUP is a function in Excel that allows you to search for a value in the first column of a table and return a corresponding value from another column in the same row. The basic formula looks like this: =VLOOKUP(lookup_value, table_array, column_index, FALSE).
One important thing when using VLOOKUP is to lock the table range so it doesn’t shift when you copy the formula down. To do this, add dollar symbols $ around the range — for example, use $A$2:$B$10 instead of A2:B10
7. The combination of INDEX and MATCH in Excel creates a powerful and flexible way to look up values. Used together, INDEX(MATCH(...)) allows you to dynamically retrieve data based on variable conditions. Unlike VLOOKUP, which can only search to the right, INDEX and MATCH can look in any direction.
8. A Pivot Table is a powerful tool used to summarize & filter large amounts of data quickly and efficiently. Instead of manually sorting or filtering raw data, a Pivot Table lets you group, count, total, or average data with just a few clicks.
Pivot Table fields can be customized and built up using multiple fields : Rows, Columns, Filters, and Values. They are dynamic, meaning they update automatically when your data changes - making them ideal for interactive reports.
9. The IFNA function in Excel is used to handle the specific error #N/A, which usually appears when a lookup formula can't find a match. The function returns a custom message or value you specify, allowing your data to look clean and more professional.
