Posts

View Excel Workbook

View Excel Workbooks and Formulas

Image
To view any excel workbook and the formulas within their cells, please click on the bottom right icon. Also, once I learned about the FORMULATEXT function so I starting utilizing that at some point so you can see the whole formula without having to click into a cell.  Note the following: 1) All numbers are made up; I often use the RANBETWEEN() function to randomize numbers. It allows you to restrict the randomized number with a min and max value. 2) On the Excel Blogspot article, you can utilize the dropdowns and filters. However, once you open he excel workbook with the bottom right icon, it no longer allows you that option. For example, look at the article below and try the dropdown and filters on the blogspot website vs. opening it in an excel workbook using bottom right icon. Article:  GETPIVOTDATA
Post a Comment
Read more

Ranking of UC Schools from Top 110 Ranked National Universities of 2024

Link to GitHub:  https://github.com/sgng-hub/2024unviersityranking Link to Dataset: https://www.kaggle.com/datasets/raymondtoo/the-world-university-rankings-2016-2024 Dataset Official Source: https://www.timeshighereducation.com/world-university-rankings/2024/world-ranking This project is about writing SQL query to find the ranking of top 110 world universities that are part of the University of California System I included a link to Looker Studio visualization of the query     
Post a Comment
Read more

Excel F4

F4 Excel Repeat Button Video Example I always thought F4 was only to lock the cell. A common use would be using the shortcut key Ctrl + Shift and plus sign to add a new row and then using F4 button to repeat the action of adding rows. 
Post a Comment
Read more

Power BI

Image
First of all, all credit goes to Linkedin Learning and Joshua Rischin, Company Director of Axium Solutions. I watched the video and followed along how to create a dashboard using Power BI. There is a title, a line chart, waterfall chart, and a slicer to filter for a specific state's sale on top right. Most notably, if I clicked on one of the bars on the waterfall chart, then it the line chart above will also filter for that specific product category. Please see Power BI screenshots below How it Looks in Power BI Exported into Powerpoint
Post a Comment
Read more

Copy and Paste Visible Cells

Copy & Paste Visible Cells When Filtering You can copy and paste visible cells by highlighting the cells and then going F5 --> Special --> Visible cells Only Then click copy and paste the cells in rows that are not filtered
Post a Comment
Read more

Remove Duplicates

Image
One Way to do this is to use the UNIQUE funciton, which is what I often use. For example, I have several vendor codes for different invoice number. Use the Unique function and it will remove all duplicates and give you only the unique vendor codes. This is helpful when I do entry in the accounting software, especially when there is over 500+ lines of vendor codes. Alternatively, you can use ALT + A + M to remove duplicates, so definitely will start using that more!
Post a Comment
Read more

Image Overlay Chart

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
Post a Comment
Read more

Waterfall Charts Version 2

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.
Post a Comment
Read more

Waterfall Charts

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.
Post a Comment
Read more

Index & Match - Reporting Billing

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.
Post a Comment
Read more

Column/Row Groups & Symbols & Percent Change

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
Post a Comment
Read more