Excel

HLOOKUP Per the website, "HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row." Below, I wanted to return the number of Basis invoices paid on week 1, week 2, week 3, etc. I used the right formula here because the row index number is always one more than the week. Alternatively, on another sheet, I also just used an array constant that allowed me to use just one single formula to output the same result.

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.  

IF, NOT, AND, OR To get more practice with the logic behind using the IF function with NOT, AND, OR logic: Below, I wanted to make a file where I showcase how I would use each in a AP/AR/Billing position such as my role as a Finance & Accounting Specialist. In my examples, I use them to see the following: 1) if we are billing over budget 2) if we have any urgent amounts to pay 3) if invoice is okay to pay as long as we billed client and receive client payment 4) if an invoice is due within 30 days.

IF, NOT, OR If it is either this or that, then mark and x. Either wise, do not mark it as an x. (i.e. Red or Green) If it is neither this or that, then mark an x. Either wise, do not mark it as an x. (i.e. Neither Red or Green) Below, I do a version 1 formula without using a constant array and formulas that uses constant array and produces same result.

RIGHT  &  SUMIF As a Finance & Accounting Specialist, I need to report he unbilled media actual or the final numbers after billing is sent out to the client. I use the RIGHT function to return the first 2 letters of the invoice number because that represents the media type. Then, I can use the SUMIF function to return the net, commission, and gross for GENERAL media types, Out of Home (OH) media type, and Digital (DG) media type. Finally, everyone reports their numbers on an excel file to track the company's gross profit.                                  

IF, AND, OR More practice with the IF, AND, OR logic formula. I used the IF and OR to identify certain colored apples and mark an x if so. I used the IF & AND to determine if it is a round apple or not.

IF and OR Constant Array See below 2 examples using IF and OR logic together. The first example is returning whether the individual pass or fail if either test is over 50. The second example is returning whether the invoice is okay to pay if the status column is either billed to client or cash receipt entered (client paid). For the second example, I need to use a constant array, a hard-coded set of values provided in an Excel formula for "Billed to Client" and "Cash Receipt Entered." A curly brace is used within the IF formula.  

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. 

IF  &  Operators Below is file with example of arithmetic operators and comparison operator. I used the if statement along with comparison operator to determine result of "fail", "pass", or "perfect." I also used a conditional formatting rule to fill a certain color for each result.

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.

Remove Duplicates When you use the Remove Duplicates feature, the duplicate data is permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information. 1) Select a cell in your range 2) Data > Remove Duplicates 3) Check off the criteria for removing the duplicate rows 4) Click OK See below as an example:

Formulatext I can reference a cell and the FORMULATEXT function will return the formula as a string.

LEFT, RIGHT, MID, LEN, FIND To find the whole number: 1) Use LEFT function because whole number is left of the decimal 2) FIND position of decimal and then subtract 1 (this will allow user to return everything before the decimal) To find the first name: 1) Use LEFT function because first name is left of the space 2) FIND position of space and then subtract 1 (this will allow user to return everything before the space) To find the last name: 1) Use the RIGHT function because last name is right of the space 2) Find the length of the full name using LEN function 3) FIND position of space 4) Subtract and get difference between length of full name and position of space (the full length minus characters spots the first name and space is using will result in the number of spots the last name is using)

Left, Right, & Mid Extract characters using the left, right, and mid function.  It was super useful to use the mid function to extract the area code and landline number (the 7 numbers after the area code). There is more to learn such as using FIND and LEN functions and combining them together.

As a Finance & Accounting Specialist, I am always filtering through different media types and other columns. It is quite annoying to having to revert my filters back to its original data. To remove all filters, simply Alt + A + C. Note that when you do this, all these menu animations will appear. 

  AGGREGATE FUNCTION WEBSITE The aggregate function is an Excel function with multiple functions within it. What if the data is filtered and also has errors like N/A or DIV/0? Well, as shown below, the user can ignore both hidden rows and errors. The sum would have been a 100 of the full data, but when filtered it is actually 70 by using the aggregate function. The count would have been 4, but when filtered it is actually 3 if we are excluding the error by using the aggregate function.

AGGREGATE FUNCTION WEBSITE The aggregate function is an Excel function with multiple functions within it. One example is below where user can sum up values even if there is a "N/A" or "DIV/0" error.

Flash Fill:  Flash Fill Website Flash Fill will auto populate based on a pattern from a list. Say Less. Check out some examples below.

Textjoin:  Textjoin Function Website Textsplit:  Textsplit Function Website Below I show an example of using textjoin and textsplit. Textjoin would be helpful for reports that I pull where the header has the name broken apart and I need to put them together into one cell. Textsplit is just like text to columns but in a formula form.

 Website:  https://www.careerprinciples.com/resources/index-match-with-multiple-criteria Below, a user can test out the Xlookup & Concatenate criteria OR Index/Match & Concatenate criteria to find the commission for a particular row. In my example, I want a particular media type and different column criteria to find the commission.

Index/Match 2 Way Xlookup Website:  https://www.excel-easy.com/examples/two-way-lookup.html Match:  https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a Index:  https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd Below I use MATCH function to find the row and column number of a certain month or certain Pokemon Plushy. Then, I use the INDEX function to find the insertion of the row and number and that gives us the sales for that Pokémon Plushy for that month.

Xlookup Syntax:  https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 Below is an example of using a 2 way Xlookup that I learned from Linkedin Learning.  1) Use xlookup to find the price of item xlookup(item we are looking for, item array from price grid, output price array from price grid) 2) Use 2 way xlookup to find the discount First xlookup the item against the item array from price grid Nested within is another xlookup the quantity against the quantity array from price grid Output the discount table where item and quantity intersect  match mode is -1 (exact match or next smaller numbe 3) Find the cost by finding the product of the price, quantity, and discount

Website:  https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9#:~:text=If%20you%20want%20to%20maintain,formula%20stays%20exactly%20the%20same. To absolute reference or to lock down a cell, use F4. Continue to use F4 to toggle and switch between relative, absolute, and mixed referencing. My laptop combines features on my F1, F2, F3, etc buttons so I must use FN + F4 to absolute reference. This is helpful especially when using formulas so the user can lock certain arrays or columns even when looking up certain cells for instance. I use an xlookup with locked and absolute reference lookup column and output column. 

Website:  https://www.microsoft.com/en-us/power-platform/products/power-bi I used Power BI to create 3 visualizations for my fast food table data. The waterfall chart shows sum of unbilled gross by product. The bar chart shows sum of unbilled gross by media type. The line graph shows sum of unbilled gross by ATB number. In addition, I have included a slicer by media type for the individual to customize what is shown on the visualizations. Fast Food Table Data:   Screenshot of Power BI visualizations: PowerPoint version of Power BI: Scan QR Code to see my Power BI visualizations (requires a PowerBi License to view):

Website:  https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576 As a Finance & Accounting Specialist, I used pivot tables for analyzing numbers that is calculated through Excel versus what is generated on our billing system Mediaocean. Also, the pivot table is a great way to summarize the data given so many fields such as media types, ATB#, est# and vendor name. Below, I go step by step of me transforming a table about fast food into a pivot table helpful for reporting and summarizing information. Please see below how I transformed my basic pivot table v1 to a more advanced pivot table v6. I also display pictures of using difference Excel features like field settings, tabular form, formatting cells, and freeze pane.