Ashish Mathur’s Post

Expectations: I've mastered Excel formulas and even macros, so now I can automate everything! Reality: Spent hours banging my head over a VLOOKUP error, only to discover the real issue was a data type mismatch. Yup, I was looking up a numeric value which was text data type in the location table. Classic. Real-world data problems are on a whole different level. 🤦♂️ #DataAnalytics #DataScience

"🚀 Unlock Excel's Potential with Index-Match! 💼 Dive into the world of efficient data lookup with our latest tutorial on the Index-Match function. Say goodbye to VLOOKUP woes and hello to seamless data retrieval! https://lnkd.in/gcWJTGxM #saartechnosoft #minz_space #Excel #IndexMatch #DataAnalysis #TechTips"

In PowerBI, compare dates between 2 tables and show only dates from Table1 which are lesser than the farthest date of Table2 https://lnkd.in/gZJGYeDv #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

In PowerBI, dynamic filtering of data based on user selected date https://lnkd.in/gDFpitQs #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

In PowerBI, assign a rank to each product via a calculated column formula https://lnkd.in/g-CJKjcu #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

𝐄𝐱𝐜𝐞𝐥 𝐭𝐢𝐩 𝐟𝐨𝐫 𝐛𝐞𝐠𝐢𝐧𝐧𝐞𝐫𝐬: While working with pivot tables on data that get replaced quite often, I have seen people either changing the pivot table data source (which is manual) or converting the data into a table and linking the table as the pivot source (which makes the excel file heavy). However, there is an easier way of solving this - 𝐃𝐲𝐧𝐚𝐦𝐢𝐜 𝐑𝐚𝐧𝐠𝐞𝐬. 𝑇ℎ𝑒 𝑖𝑑𝑒𝑎 𝑖𝑠 𝑡𝑜 𝑢𝑠𝑒 𝑎𝑛 𝑂𝐹𝐹𝑆𝐸𝑇() 𝑓𝑢𝑛𝑐𝑡𝑖𝑜𝑛 𝑡𝑜 𝑐𝑟𝑒𝑎𝑡𝑒 𝑎 𝑑𝑦𝑛𝑎𝑚𝑖𝑐 𝑎𝑟𝑟𝑎𝑦 𝑤ℎ𝑖𝑐ℎ 𝑐𝑎𝑛 𝑖𝑑𝑒𝑛𝑡𝑖𝑓𝑦 𝑡ℎ𝑒 𝑡𝑜𝑡𝑎𝑙 𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑟𝑜𝑤𝑠 𝑎𝑛𝑑 𝑐𝑜𝑙𝑢𝑚𝑛𝑠 𝑖𝑛 𝑡ℎ𝑒 𝑑𝑎𝑡𝑎𝑠𝑒𝑡. 𝑇ℎ𝑒 𝑤𝑎𝑦 𝑡𝑜 𝑔𝑜 𝑎𝑏𝑜𝑢𝑡 𝑖𝑡 𝑖𝑠 - 𝐺𝑜 𝑡𝑜 "𝐹𝑜𝑟𝑚𝑢𝑙𝑎𝑠" --> 𝐷𝑒𝑓𝑖𝑛𝑒 𝑁𝑎𝑚𝑒 --> 𝑃𝑢𝑡 𝑎 𝑁𝑎𝑚𝑒 (𝑙𝑒𝑡𝑠 𝑠𝑎𝑦 "𝑅𝑎𝑤𝐷𝑎𝑡𝑎") 𝑎𝑛𝑑 𝑖𝑛 𝑡ℎ𝑒 "𝑅𝑒𝑓𝑒𝑟𝑠 𝑡𝑜:" 𝑠𝑒𝑐𝑡𝑖𝑜𝑛 𝑢𝑠𝑒 𝑎 𝑡𝑦𝑝𝑖𝑐𝑎𝑙 𝑓𝑜𝑟𝑚𝑢𝑙𝑎 "=𝑂𝐹𝐹𝑆𝐸𝑇(𝑆ℎ𝑒𝑒𝑡1!$𝐴$1,0,0,𝐶𝑂𝑈𝑁𝑇𝐴(𝑆ℎ𝑒𝑒𝑡1!$𝐴:$𝐴),𝐶𝑂𝑈𝑁𝑇𝐴(𝑆ℎ𝑒𝑒𝑡1!$1:$1))". 𝑎𝑠𝑠𝑢𝑚𝑖𝑛𝑔 𝑦𝑜𝑢𝑟 𝑑𝑎𝑡𝑎𝑠𝑒𝑡 𝑠𝑡𝑎𝑟𝑡𝑠 𝑓𝑟𝑜𝑚 "𝐴1".  𝑇ℎ𝑒𝑛 𝑐𝑙𝑖𝑐𝑘 𝑂𝑘. Now every time you change the data, all you have to do is refresh the pivot and it will automatically consider all of the data. This formula creates a reference range starting from cell A1 on Sheet1 with the same height and width as the non-empty range in column A and row 1 on Sheet1. Also, here's a link to a short video which illustrates this example - https://lnkd.in/g2s--3kf #excelskills #exceltricks #pivottable #excel #dataanalytics

Pivot tables tutorial: https://bit.ly/4dia4h7 In this step-by-step guide, you'll learn how to create and use Pivot Tables to quickly summarize and analyze your data like a pro. No prior experience is needed! What You’ll Learn: -What is a Pivot Table and why it’s useful -How to create your first Pivot Table -Customizing Pivot Tables for better insights -Sorting, filtering, and grouping data -Practical tips and tricks for everyday use #codingninjas #pivottables #msexcel

In PowerBI, remove filters in a matrix visual https://lnkd.in/gGQiyd2c #powerbidesktop #powerbi #datamodelling #dax #powerpivot #microsoftexcel #msexcel #excel

Lambda to Unstack Data Range into Multiple Chart Series Often multiple sets of X and Y values are stacked, then plotted as one series. Here is a Lambda formula to unstack into multiple data series for charting. https://lnkd.in/ghJxdcuQ #Excel #Lambda #Charts #PeltierTech

🌶️ Hiding columns in Pivot Tables 🌶️ Building on the positive reviews of our Hiding Columns feature in Tables, this update gives you even more control to tweak and tailor your data views when working with Pivot Table.