Excel

Dynamic Table If I add new data to my Billing Table, my sumif formula will not need to be updated to capture the full data. Within my sumif formula, I can refer to my Billing Table and then use bracketed columns and this will allow the sumif formula to capture all the data even if new data is added to the dynamic table.

Sample Employee Data Linkedin Learning Power Query: Get & Transform Drill Down Article I took this sample employee data that contains a 1000 rows of data and drilled down to variables, job title & department. I created 2 connections from a 2 tables that contain data validation. I learned this technique through the Linkedin Learning video, which is great for convenience and easy identification of honing on certain details instead of having to filter and unfilter manually.  Shout outs to Alt + A + V shortcut for data validation. Please note while you can adjust the job title & department, you cannot refresh the query on this blog or by opening on a workbook on another window. However, this does show the steps and idea behind taking extremely large data sets to hone in on x,y,z variables.

Vlookup Hlookup Below use the dropdown list to select a client and the cells below with the Vlookup & Hlookup formula will return billing and commission numbers. Note that the first tab is using Vlookup because we are looking for the Client vertically in a column and the second tab is using Hlookup because we are looking for the Client horizontally in a row.

GETPIVOTDATA The GETPIVOTDATA function returns visible data from a PivotTable. Honestly, at its simplest, just use "=" and then click on a cell and then the whole syntax will autopopulate. That is how I returned the Billing Gross, Net, and Commission YTD. I think is helpful to use a data validation list. First, use "=" and reference a cell and then just change the item to reference the cell that has data validation. For example, there when you change the media type on the green cell F12, you can get its unbilled gross, net, and commission. Note that the data validation list dropdown may not appear unless you open the workbook on your Excel Desktop and open the workbook in Edit mode.  

Multiple Condition IF Below, I made up a scenario where a seller is offering 1 item for $2.00, 2 to 9 items for $1.50 each, and 10 items or more for $1.00 each.  In green is the syntax for the IF statement and a IF AND statement. The idea is that if the first condition is not true, then it will check if the next condition is true. If the second condition is not true, then it will check if the third condition is true. Finally, in the How Many Purchased Column is a Data Validation that only allows for whole numbers from 1 through a 100 to prevent any decimals or nothing purchased scenario. 

Data Validation Below is an example of data validation where I created a custom rule that makes it that the date a vendor received via ACH must be in year of 2024, more than 2 days after processed date, and cannot fall on either a Sunday or Saturday. I used the AND, YEAR, WEEKDAY, and < >(does not equal) symbols in my formula.

Data Validation Below is an example of data validation where the entry for an individual's salary is limited to values that are whole numbers between 40k and 80k.   Below is a data validation list based off a master file. Originally I had the master file on the same sheet tab. Then, I Ctrl + X and then Ctrl + V the master file data into another sheet tab.

As a Finance & Accounting Specialist, I need to check that the finalized client invoices match our billing summary gross amount. I created this conditional formatted list to keep track what match and did not match. Then, I would make the appropriate changes to the commission rates so they would match. Our system Mediaocean does not always calculate at the exact commission rate due to rounding so that is why we must go through this process to ensure our numbers on Excel match with what we are sending to the client. Data --> Data Validation Make it a list and enter your 2 options Conditional Formatting --> Manage Rules Set the cell to be highlighted green when cell says "Match" and highlighted red when cell says "Do Not Match" See below what it will look like: