Excel Productivity for Lazy (but Smart) People

So, some years ago I ran into an Excel problem  at work which I couldn't solve. I had Excel   2010 even though it was 2014. To give you  some context here's the task I had to do.   We had a new reporting structure. The master  data we had for products was changing and I had   to assess how that would impact our numbers and  if we should go ahead and make the changes in our   financial system or not. Here's a simplified  version of what I was trying to achieve.   I got this file extract from the system. It's  sales data by transaction, product, quantity,   and some other fields. It had 1.2 million  rows. I knew Excel could handle a lot of rows   so I copied it over to Excel. But unfortunately I  got this error. I went ahead anyhow and then this   came up. No choice but go with ok. I got my data  in but it's not all there. It got cut off. This   means I had to find the last row that got imported  in the text file and then copy and paste the rest   manually in. A few moments later. So I ended up  with my data on two tabs. Okay so so far so good.   I also copied over the product master detail  on two separate tabs. So the new master data   has a different allocation of product code for  department and section. What I needed to do was to   create a report showing total quantity for the new  department and compare that to the old department.   I rolled up my sleeves and I started writing  VLOOKUPs. My plan was this. I would add new   columns to the sales sheets for department old and  section old. Then another one for department new   and another one for section new. With the VLOOKUP  formulas I could bring over the corresponding   department for each row. Once I finished that for  part one, I had to repeat everything for part two.   Then I created my first pivot  table based on part one data.   I put department new into rows, and quantity in  the values. I repeated that for part two data   and then I created a report that added the  values from the two pivot tables together.   So this gave me the breakdown of quantity based  on the new master data. Now because we wanted to   compare this report to the old master data I had  to repeat the process. Create two pivot tables   based on the old master data and then  a report that adds the values together. Approximately 10 hours later. It took some time.  I had to do a lot of double checking because I   wasn't sure if I'd made a mistake during the  process since a lot of steps were involved.   I showed the report to my manager  and they were happy with the outcome.   But, because there's always a but, they  also asked for a breakdown by year.   And the manager of the electronics department  wanted to get a year-to-date view of their   data as well. I was so close to saying it's not  possible because it felt like an impossible task.   But I kept my frustrations to myself and  I spent many hours trying to do this. But   that wasn't the end of the request because then  the next department wanted to see their numbers   and compare it to the previous period as well. Of  course all of this took hours and at the time, so   remember this was 2014, no one expected it to be  fast anyway. But if you use this approach today,   so the same approach that I used a few years back,  then you're probably going to be seen as slow   and inefficient. Because if you're dealing with  a lot of data from different places then you need   to take advantage of the power side of Excel. If  you don't, it shows that you don't have the skills   that are needed today. So now let's take a look at  how you'd solve this today. Plus, I'm also going   to show you how you can easily get the breakdown  by year and do year-to-date calculations,   or any type of calculations that you might need on  this data. First thing you do is upload the data   to the data model which you can do through Power  Query. Instead of writing formulas you'll create   relationships. Once the relationships are set up  you can insert a pivot table and have access to   each separate table. The relationships act like  VLOOKUPs between the tables. This makes it very   easy for us to analyze quantity based on the new  master data. Or we can create another pivot table   based on the old master data. So you have both  views side by side. And if we want we can analyze   both, so old and new in the same pivot table. We  can create a matrix and see how everything fits   in. Now notice the numbers have no formatting. So  you'd have to format these every time you add them   into a new pivot table. So if you're planning  to reuse a value in the values field,   like I'm reusing the quantity field, then you'd  be smart to create a measure. This way the number   formatting is embedded in the value. It stays with  it. Every time you bring it to a new pivot table   the formatting is there. Also sometimes you have  more complex calculations. So for example, you   want to calculate total quantity for products that  were on sale. In our data set this means on sale   has a value of 1. You can do this calculation  with a simple measure that looks like this.   Instead of adding separate columns to your  data set you'd add measures. These measures   get automatically calculated after  you drop them in your pivot table.   So they don't increase the size of your  data model. So as you can see doing the   first comparison new and old master data is  so much easier this way than the old way.   Another alternative would be to merge the data in  Power Query but Power Query has its limitations   too. It's going to be difficult and inefficient  to do time intelligence analysis in a single   table. So the better way of breaking down the  data by year and doing year-to-date reporting,   or calculating moving averages, or running totals  is to use the data model and DAX. And here's how   easy it is. Go to the data model, to design,  date table new. This will automatically create   a calendar table for you with a bunch of columns  like year, month, month and year combination,   weekday, week number, and so on. Now you just  have to go and create a relationship between your   sales table and this calendar table. Then go ahead  and insert a pivot table. Bring year to columns,   department based on the new master data to the  rows, and quantity to the values. That's it.   What about the year-to-date calculation? Just go  to measures > new measure. Call it YTD quantity   and type in total YTD. The expression is our  total quantity measure, and then we just need   the date column from the calendar table. Adjust  the number formatting and add this to your model.   Now let's move these around. Add year and month  to the rows. Bring department to the filters.   Add the year-to-date quantity to values. We  can filter for any department. So it was the   electronics department that wanted to check  their year-to-date data. Let's filter for   them. Check this out: the sum of this, equals this  number. What if we have different fiscal periods?   It's no problem. Just update the function. Adding  quotation marks the last day of the period.   So here I'm going to add 6 30 for June 30th. Click  on ok and your year-to-date values reset in July.   It's that easy to get more complex insights from  our data. There are many simple formulas like   these you can use in DAX and the data model. The  great part is that data modeling and DAX is not   just for Excel but it's also a skill that you need  to have for Power BI. So you learn one feature   and you can apply it to two different apps. In  fact these are the two most used applications at   companies today. Here's the thing though. DAX can  get quite complex. And if you see the complex side   first you might be discouraged to learn it. At  least that was the case for me. But the reality   is that there is so much you can achieve with  just a little bit of DAX knowledge. Within one   hour you can create a complete interactive sales  dashboard that brings data together from different   files. In fact this is one of the first dashboards  that we create from scratch in the basic section   of my Power Pivot and DAX course which by the way  is now open for enrollment. There's a link below   the video if you want to find out more about the  course to see if it's for you. So what it comes   down to is that you have standard Excel in the  one side and Power Excel on the other side. Both   of these exist in the same Excel application.  It's easy for us to stay in our comfort zones.   But the ones who use the power side will be more  efficient and they're going to be able to create   better quality reports in a short period of time.  Now some might call them lazy, but smart. Let me   just give you some more ideas about what you can  do with DAX and the data model. You can create   reports that show 10-day running totals, or moving  average average, daily sales. You can get the   total sales value of your top three products. Find  out which date had the highest sales, or which   product brought in the most revenue. You can do  time intelligence calculations on custom calendars   like weekly calendars. All of these topics and  a lot more is covered in my comprehensive Power   Pivot and DAX course. In fact it's three  courses in one: We start with essentials,   then move on to master difficult DAX concepts.  And we create dashboards along the way. This way   you don't just learn these concepts in isolation  but you learn how to integrate them with standard   Excel. You also get a lot of challenges and "find  the mistake" type of exercises which will help you   learn better and remember better. So come join me  in my course and master the Power side of Excel.

Channel: Leila Gharani

Views: 804,751

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, excel, microsoft excel, power pivot, excel tips and tricks, power query, pivot table, powerpivot, pivottable, excel powerpivot, dax formulas, power pivot tutorial, data model, dax, business intelligence, excel pivot, what is power pivot

Id: Gf4HmkR7_FE

Length: 10min 40sec (640 seconds)

Published: Thu Nov 18 2021