Excel

Creating Image Overlay Char t The video shows how to create an overlay chart which is just really cool visual to create in addition to a waterfall chart Use 100% Stacked Column Swap Rows & Columns No Fill for $ Remaining Couple more edits like shrinking width, removing title, and bottom x axis, highlight $ raised/billed to red fill Move image behind opaque thermometer

I made a Version 2 of the Waterfall chart and provided an example that goes from Jan through Dec.  In addition, I think it was helpful to have a bar in the waterfall chart to show bill YTD. From left to right, it is the ATB amount, Bill YTD amount, billing for each month, and finally the remaining to bill. With so many budgets and billings throughout the month, the waterfall chart is a great visualization tool to understand if we are on track regarding that ATB.

Waterfall Charts I made a waterfall chart for a specific ATB estimate with made up ATB amounts and numbers. The starting amount is in green and each red bar represents a particular month's billing and the yellow shows remaining amount to bill. It is a great visualization to see if we are on track billing up to the ATB amount or behind or even over.

Index & Match "To summarize, INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column numbers." "The MATCH function is designed for one purpose: find the position of an item in a range" Now, using index and match together, I can find the billing numbers for particular client and month. Feel free to utilize the data validated dropdown to change client and month to find its respective billing numbers.

Credits to this YouTuber Great Excel tips from this YouTuber. Column and Row grouping is helpful for hiding and unhiding in quick efficient manner.  The classic (New-Old)/Old then convert into percentage to find percent change Use the insert --> Symbol and I used geometric shapes. Also used a conditional formatting for easy reading Ctrl + 1 is hotkey shortcut to format cells

Merge Queries Often, ERP systems will have some sort of key column that can connect to reports. For example, one ERP system can have an internal invoice number, an invoice number to connect to another system and the other ERP can also have same connecting invoice number, and a different number that is sent to client. Here, merging two queries that are a connection can be used to put together into one table that shows the two internal ERP invoice number and the invoice number sent to client.  One ERP   Second ERP   Merge

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.

ICON SETS Below is an example of green if we billed a job a 100%, yellow if billed 50% but below 100%, and red if 0% using icon sets.

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.

EXCELISFUN Tutorial RECORD.FIELD Please refer to the video by EXCELISFUN. 1) Use two xlookups where 1st one is returning the row and the second gives you the column array 2) Use Power Query Have two query connections for 2 tables For the lookup table, add a custom column that outputs the table with amounts In the custom column, first have it find the vendor code row Then outside of that, use Record.Field to refer to that row field and return the city amount