Compare two Excel sheets or datasets to find matches and differences | Prashant Panchal | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Compare two Excel sheets or datasets to find matches and differences

teacher avatar Prashant Panchal, Excel Expert and Data Transformation

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

23 Lessons (2h 12m)
    • 1. Compare data Workbook Full Introduction

      5:16
    • 2. Compared Data Workbook Introduction

      3:58
    • 3. Ideal data types for Comparision

      3:07
    • 4. Excel as our Chosen Tool for data Comparision

      5:51
    • 5. Overview of the Compare data workbook

      8:50
    • 6. Most Important thing to remember while comparing data

      2:59
    • 7. First Column of your dataset must be a Unique key column

      8:36
    • 8. Deep Dive into Mechanisms of Compare data Workbook

      9:33
    • 9. Importnat - Changing Dataset headers for new kind of Data

      5:55
    • 10. How to Modify VBE Code for CDW-1

      4:08
    • 11. How to Modify VBE Code for CDW-2

      5:18
    • 12. How to Modify VBE Code for CDW-3

      5:05
    • 13. How to Modify VBE Code for CDW-4

      3:33
    • 14. How to clean and shape data into propert structure with Power Query

      6:40
    • 15. How to clean and shape data into propert structure with Power Query -2

      6:49
    • 16. Practical Application of Compare Data workbook

      7:01
    • 17. Build Insightful Payroll Analysis Report with CDW-1

      5:15
    • 18. Build Insightful Payroll Analysis Report with CDW-2

      6:39
    • 19. Build Insightful Payroll Analysis Report with CDW-3

      8:25
    • 20. Build Insightful Payroll Analysis Report with CDW-4

      7:14
    • 21. Build Insightful Payroll Analysis Report with CDW-5

      8:23
    • 22. Important Facts about using Compare Data Workbook

      2:43
    • 23. Thank you and Class conclusion

      0:52
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

110

Students

--

Projects

About This Class

Who this course is for:

  • Accountants, Auditors, Analysts who want to accurately compare data with few clicks
  • Data Analysts, Data Science Professionals who want to find out data variances quickly
  • Busy HR Professional, Sales Professional, Small Business Owners, Entrepreneurs

What you'll learn

  • Find out critical variances at each CELL level in Excel worksheets, which is not possible with Formulas and Functions.
  • Compare simple to complex datasets or worksheets with few clicks and get accurate variances in the form of matching data, new data added and data removed.
  • Make an adjustment in the compare data workbook to suit one's requirements.
  • Create Effective, Reusable and Dynamic Excel Model effectively by harnessing Compare Data workbook's ability
  • Modify Excel VBA code behind the compare data workbook to get accurate analysis and variances

Requirements

  • Basic Knowledge of Microsoft Excel
  • Basic to Intermediate knowledge of Excel Formulas and Functions
  • Basic to Intermediate Knowledge of Excel VBA

Description

Have you ever had a need to Compare two datasets or worksheets?

As an entrepreneur, accountant, HR personnel, stockbroker, etc. comparing two datasets becomes a regular task in your career.

Whether it is to compare employee datasets, salary or payroll, stocks, sales commission and so much more; you will definitely find yourself coming across tabular worksheets from time to time.

It can get pretty tasking and most discouraging is the fact that traditional methods which you often used in Microsoft Excel such as functions, formula and power query; are not always reliable or can provide you answers.

If you are in the category mentioned above, worry no more. The perfect solution is here — Smart VBA Excel Workbook. With it, you can now find new data, deleted data, variances.

What if I say you could find out CELL level Variances with just a few clicks using the Compare data workbook? Sounds amazing?

That is what Compare Data Excel VBA workbook is designed to do; it compares two similarly structured datasets or worksheets by Row and then by Each CELL.

  • You will typically have datasets or worksheet in periodic format, i.e. Last months dataset and Current Months dataset, and you would like to find out variances first at Row level and then at CELL level.

  • Compare data workbook finds out added data in a current month, removed data in a current month (from last month) and for datasets/rows/records which are existing in both places (Last month and current month), it highlights variances at each cell level.

This is something awesome, and it is difficult to achieve this with traditional Excel formulas and function or even with Modern excel tools as Power Query Joins. The only way out is smart VBA coding in Excel.

In this course, I will walk you through on how to use this fantastic compare data workbook and teach you:

  • How to set up the workbook for best results.

  • How to change important VBA Code settings to suit your need.

  • How to change the formatting of added data, removed data and variance data.

  • How to set up this workbook in an ideal way so that you can create truly dynamic and reusable reports for any complex data analysis.

In the end, we will go through two practical examples:

  • We will analyse the Mutual fund portfolio to find out critical variances between the two dates (position).

  • We will go through a comprehensive Payroll analysis exercise where I will create fantastic Payroll variance dashboard to find out variances for various pay types (Basic, HRA, Overtime, deduction etc.) in complex payroll data.

  • With these examples, you will also learn how to set up this workbook to maximise your productivity.

Enrol Now and Let your data analysis and data comparison problems go away!

Meet Your Teacher

Teacher Profile Image

Prashant Panchal

Excel Expert and Data Transformation

Teacher

Hey, I am Prashant Panchal. I like helping fellow Accountants, Auditors and Analysts to become PRO at using Microsoft Excel.

 

I have been using Excel for mainly Accounting and Analytics for more than 13 years and I quite enthusiastic about sharing my knowledge with fellow Accountants, Analysts, or any Excel user

 

I have come across many challenging reporting and analytics challenges during my career, and I was able to resolve the same using good old Microsoft Excel Spreadsheet application.

 

With me, you will find Basic to Advanced Excel formulas and Functions, Tips and Tricks, Excel VBA ready to use codes.

 

Th... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Compare data Workbook Full Introduction: Hello and welcome to the scores. So what is competent? A workbook comfortable book is Excel Workbook, designed to compare to similarly structured or tabular data or toe worksheet by each Rouen and by East Cell to find out matches and differences between the two tacit or worksheets. If you ever worked in office and woman, I'm pretty sure you must have come across a situation. But you had to compare to the test sets or, let's say, to retire sheets to find out. Francis. This could be any kind of data. Most commonly employee database. Employeessalaries stock car invented a set sales commission or sales item. State assets accounting. It does set when you don't notice. It's so what's so special about actually be able to look? You can say we can always do this with traditional way off, using many Excel formula based technique. Like we look up in next match, we can try more. An excerpt was like power query joints. While you're right about that, you're not entirely correct. When I said that we compare it to actually be a workbook, you can compare it. I set so sheets by each row and then by itself I meant to say that this word books Axel will be a court compares each row, great asserts and worksheets, and then compares each cell village to find out where answers. This is something quite amazing. I can say that is like a silver bullet solution toe every complex that of Iran said that a comparision challenge, which is often freeze backgrounders, auditors, analyst and sells for fresh instructor does at the professional and any other profession are entrepreneur in the routing would flow. Let me show you how this castle will be. A workbook is different in comparing to real estate stand, traditional formulas and function solution you're always approaching. First of all, this workbook compares through similarly sexual later, the letter said, sort worksheets must have a key column that is the first column. As you can see in this to worksheets or table, there is employed court column and Queen Embark Along, which has key column in the board data set. This column must be the first column in, but it assets or worksheets you're trying to compare works history. It is that you're trying to compare, must have the same number of columns that order of the columns must be identical, sort of. The column must be same as you can see on the screen board it was that must have the same order off parliament. Also, same number of columns completed. A bright eyes done it. It's a level in border sets its finds out, and it draws in. New data Rose dilated in new Greater Comfort. Told it, most importantly, finds all rosy test changes, its cell level in both data sets or worksheets you're trying to compare. Isn't that amazing? Believe me, I've been using Excel for a really long time, and I'm quite good at it. But finding where I said it's cell level between the two leaders. That is not easy with formulas and functions that the particular current of using computer workbook is to prepare it aside. 18 same structured former. Also, you'll have to insure the board it has that you're trying to compare, have same data types and data formats. To achieve this flawlessly will be using incredible power query tool that is getting transform in excel in discos. I will explain to each every expects on how to use this fantastic Comparator workbook will go through every component of the World Book. I will show you how to modify the baby accords and how to change the same to suit a requirement it last time and show you a few practical application of this company. We will go through a simple example of comparing a mutual fund portfolio between the two dates and find out critical and answers. The second example I will walk you through a very competent superior ordered a competitive exercise with the dashboard. In this case, we will compare two months parallel, which is various pale imaged to find out where answers like new employees, the leader, employees and employers with changes in their each pay element in lettuce month payroll, comparative previous months, payroll all the example where books and files will be able for download, along with a comparable book in the resources section of this course. At last, I want to tell you that this workbook is my top selling diesel product on the government website. I've already made around $1000 still counting, and you will get this work for free since you and all into this course. Hi, I'm pressure on potential, and I'm an excellent coziest and data transformation. Professional. I'm also a chartered accountant, which means I have come across complex. They attacked quite often in finance and accounting throughout my career and excellence always my goto tool to solve all the complex problems. I have a large amount of experience, a traditional formulas and functions except maybe, but currently and focusing on harnessing my skill set on Microsoft power tools mainly Power query tax power people in power bi I. But my favorite tool among all of this is Power Query. You will see my course on power query very soon. 2. Compared Data Workbook Introduction: hello and welcome with this course competent of it, Axl, where we're going to compare to similarly structured arrested by row and then by cell using Pro Excel baby. A template. Why we need to compare it. Have you had a situation in the workplace? Our job where you had to compare to read ascents? I know many of you, Inspector of yours Professional job profile must have a need to compare to similar structure that assets to find out exit differences over the answers. Where are the most common objective behind comparing data sets? The first, let's find out variances and the difference is fine. Addition off a new data in the tour. It does that you're trying to compare, and the last is deleted or removed it up between one period to another in the student us that you're trying to compare What are the most common scenarios or types of greater that we want to compare? To find out where dances and differences stock market or financial market data to know the fluctuation in stock presses and overall market sales and marketing director usually or appear after, I want to compare the achievements off sales off sales personnel and also as a sales director off the hair off assist apartment. You want to know what exactly happened in the two periods by comparing the same state of financial accounting data account Reason. Orators often come across need to compare historic later, like financial reports, which includes profit and loss, statement and balance sheet or appear off time. This enables them to find out the variances and what went wrong in the two periods. Payroll and employee later. Any HR department in any organization always need to compare employees mustard later or payroll there to find out where alliances or appear off time struggle. Inventory data anyway. Rose Manager, restaurant manager. Always have a need to compare to Peter State and compare exact movements. This enables them in many ways to find out variances and trace issues. What are the most common tools to compare it? Well, you can use. Ask your credits to compare later. Lescure terms is referred to as joints, so there are left out of joints right after Joyce left and he joined right antigens, etcetera. But you will need some experience in writing excuse queries to get the desired result in comparing the time with a school, you can use our programming tools, which is an open source application to compare process, analyze large number of Rita said. It is predominantly used in statistical data analysis. With our programming, you can compare it us. That's easily and effectively, even if the data volume suddenly huge. But here also, you lead some form off experience in various our programming conception libraries. Fighting is the most popular Indian mont programming language to in the market. It disquiet was settle and relatively easy to learn and use, he said. Lee, by a 10 is also gaining momentum in the journalists and data science. You will need here some former programming experience in Python Zero to compare it later and get meaning your offered and what his last was not the least option. Well, I'm sure your guested it is Gold Express replication. Microsoft Excel. It is the easiest to among four of these jewels mentioned here, and it is also the most accessible tools available to anybody who works in office. There is no programming background needed to use. Excel Application is also quite flexible to use with least amount of rose to interact in their structure. We will see how effectively you can use Excel in comparing to similar such a the press it and get the desired result. Thank you so much for watching and I'll see you in the next lecture. 3. Ideal data types for Comparision: brother idea Late hour for comparison so far, strove for your data needs to be organized. The most important thing about competitive ties that data must be organized and the best way to organize data set and shape later in a table of former. So if you advertise in a table of former, it is easier to compare it or do any further analysis. Affiliate ties are recognized that in tabular shape, you're done with 50% off your job off comparing data accurately, most before comparing data. The most important factor before you compare it is that data set and each column for data should have, ah, same later type. Now what I mean by same data type. It means that each column offer tabular data said should have a specific and uniform tolerate a time. The most common types of better are next. Type each other tax. Later hole number, integer data types. This similar type the simulator type Dave data type diameter type currency later type. There are many other kinds of their type as well, but these are the most common data types you'll come across and you to make sure that you're right outside, has this kind of greater already defined and structured for the most common told cleaner organizer later SQL, as mentioned before SQL queries, will help you to pull out later from database in a tabular form it. But ask your does not do a great job of cleaning your later but as it the same time. Ask your later has a specificity types already defined when you get them out as output with ex girl credit. So you don't you worry about escalate at times because in SQL the rate I was itself as a specific type already. Fine. So once you pull out any report or any data from SQL Database, it has that a type already fined for each columns, you can use our programming language you can clean and tidy orator with few commands in our programming language. With fighter, you can do Loffreda cleaning and tired your it as well. Well, all these three option works perfectly you relieve some former programming language experience in either of them. But if we have the best top level, which is quite well known, and almost everyone who works with the data is, well, worse with is Microsoft excel with Microsoft Excel, There are many ways to clean, tidy and transform your later. You can use excess various tax functions to clean and trim your later. You can always use formulas to organise a later, but I was such as best tool available, aspire up Microsoft Power tools, which is also free. It's called Power Grady with power, where you can easily import and a pen and combine various later sets, even from their different sources, and clean them, transform them and get them into a tabular format as an output in an excel table. We will be covering steps on using power query next cell to transform and clean your letter in later section of discourse. Thank you so much for watching as you in my next lecture. 4. Excel as our Chosen Tool for data Comparision: are chosen tool. Microsoft Excel excellence everywhere Italy any personal is work in office knows about the world. Microsoft Excel. It's a go to special application, which requires little training to get used to it. What other radios matters and raise with which you can compare their time. Axl. So there are three main ways you can compare their tiene axel. One is formulas and functions. There is the traditional way. Every Excel user experience on numbers will try to use formulas and functions. To compare the two sets Park Ready and getting transformed is a relatively new concept in excellent, even though it is around for a while, where this particular matter is amazing, I will show you the basics or park ready in cleaning. The writer. We're not comparing it. And the last but not least is rigid music for Application for Excellence, a programming language for Microsoft products. And it is applicable to all the office. Poor expert. We tax Ireland with VB A. You can do amazing things and the sexual spreadsheet and this causes based on Excel Baby, a competitor template. So be going through that in later section of this course. So now let's understand how formulas and function you can use to compare your data. So there are basically two kind of function you can use, which both our look or function. So first is the biblical function, and that is also index and match com. Or you can do to compare your data well about the functions, get the job done while comparing that rule wise between the two litter structures, especially the sad data structure, this matters will not show you sell level, Reliance's and differences. So there is a key here where you will not be able to find data. It's cell level. What are the differences at the cell level? Also, you cannot compare entire data sets. That is, if you won't be able to compare each columns off later. At the same time, we'll have to write complex functions and be a kid with how you write dysfunctions. So this is one of the limitations, or you can say this particular matter off. Using Formula Two competitors doesn't work fully as you would like to be next. The spark ready and getting transform part of getting get a transformer is unknown pulled too many Excel users. Even the most experienced users are packs are not aware about power query. It is the best transformation available freely if you own already. Macros office Barack. So it is a little freely, even if your Microsoft Office 2010 you can download it is accelerating and use it with park , where you can come by many other sources in tow one and shape it into an Excel table. This can be further used for the journalists, and for us it would be the best, compared structurally tacit. So we'll be using power equity to shape our their time making in tabular format, which is all I knew the time. Try to compare it. White Power Goody itself can be used to compare your to rate assets. So once you're transformer, they're trying to travel. A former you can use video scandal joins, and those are left out of joint. My daughter joining in a row, tojoin full after joint left anti join and write 20 joints. You can also get entire sets of greater against wonderful set or table with all the columns . But here also one key and most important factor immense and checks honorary fried. It is cell level. France's so with pork, Very also, there is no way to compare cell level later between your existing data, such in any kind of comparison. If you can able to figure out its cell level later of France's between the two letters that over a period you can get more inside than simply comparing and tyros or Britta VBS transform usually reserved for application. It's a programming language that I loved by Microsoft. With DB, you can maneuver it off his application objects. BB language is used throughout Emma's office applications, mostly from Microsoft Word, Microsoft, PowerPoint and Microsoft Excel. We're going to use a professional created Excel, which I call it competent about. This book compares two similar structured datasets and find out where and sit at pro level and at each cell level. Arcaded this workbook after long trial and Ehlers what I finally was able locate something which every journalist wants to have in Excel with this awesome, absolutely a workbook you can compare to similar researcher and former editor assets or worksheet to find out number of rows added in new work shit or later said no more of bro's deleted from all that I said to new data set in our case, it will be walk, ship and most importantly, any changes that have taken place between the two datasets at each cell level. You can also copy added data to a completely new work shirt with a single button. You can also cooperate, removed it out of completely new work ship. You also copy changed. It s set to a completely new auction. In all, this will be done with just fewer clicks and some minor adjustments. I'm very much sure that you loved this course and competitive world book. It will completely change the rate analysis for you and it will save you many hours of your precious time. I, being a finance professional, has been using this workbook in many routine analysis. It has say we turns up time and our first in comparing various financial reports like payroll, invented a listing and pray at our bases analysis to find out changes between the two monthly cycles and much, much more pro discos. I will teach you how to use this amazing workbook step by step. I will also show you what other limitations of this world book. But you should keep in mind toward any major issues or problems which are the Dionysus. You can dollar this workbook from the resources section and it has some sample later for you to play around. Thank you so much for watching in this lecture will go through the basics and structure of this company. That workbook and from then onwards will be deep diving into the computer workbook. Use it. And how you can use this word book to do the best kind of the journalists and find out the variances that draw level and celebrity. Thank you. 5. Overview of the Compare data workbook: Let's start with the old be off competitive a book. When she opened the workbook, you'll see total seven. She taps. So 1234567 The first sheet name instruction is essential instruction, which you must read before you start using the word book. But since your already enrolled into this course, you will need to read it. But just keep it handy, just in case you're forget something or you first some problem or issue. The second sheet name is ordered our which means that the base data against which you would like to compare the set off new dinner. So this is your best data, and I want to compare something or similarly structured a top, which is a new former it all day. Some some changes in the other later. So that would be the very next sheet. The third shoot name is knew better. So this is the data, which is a new letter or in really simples for Mark support is unemployed abilities. Here you can see there's a name employee number. A little birdie told fire so that our total 14 columns. So as I mentioned before, it has to be have same structure. So he has are 14 columns and the new better same employees that averages 14 columns. So all the time, for example, this is a last month's data or this is the data for the month of January. This is the month of February data. This is a situation off February and all day tires a situational January. Now you want to compare what has changed between January and February. So what has changed all that rabbit is equal to January and what has changed or what is the situation in February compared Toa January the four chief name? Very. As data is sheet read, the magic happens. You will see the violence and differences in form off added data. So as you can see, I have done already verification or reliance comparision using this magical world book. So here we have added data. This is the new titles are of course, in the she'd call knew better in comparison to the second sheet or let us this other new record which have come in this particular later said compared to this data, sent the old data set right all the new rose or a court which are not in the old data, has been added here. So as I mentioned before the murder trial, which is highlighted in the red cell background and with the white forms So this is the data rows or records which are not in the new better compared to the old data so that we explain this. So, for example, there were a few employees here suppose 1st 50 employees, which is not there in our new little set. For some reason, we did ties wrong or they have left or any of the reason they're not there. So those are the rules which are showing years, for example, that we zoom in a bit. Me, a brown William, Tyrone. All these three guys, for example, are there in there or let us a residency here the 1st 3 college. So they are there. And if you come to new data, the 1st 3 column wasn't show them or in anywhere, entire sheet. When this macro transit searches for these guys thes rose. But they're not dead. So those are that accord which are called removed it They were there in this old it'll start, which is the January is that as a regional example, but they are not there in the new data set. Now, the third type off Rosa analysis you can find with the sheet is the change data type. This other Rosa record, which are available in board data, said, that is all Street and Lucio. So they're available in border does that. There's no they're not gone or removed from all that. I don't need it. Also from last winter, this one, they're still existing. But there are some changes in this debt process. For example, the salad ease off people have changed compared to last oneto this one. So this brown highlighted sell value represents the changes is all these employees doesn't have any changes. They're not removal related, but they are there in Water said. But they have somewhere table which have changed. For example, this employee column we has a changing is all this loose. For some reason, maybe there's a correction in their charitable is. So that's the reason there is a change in this particular cell values, so this particular employee is there in both here and there. But after this new letter said was operated, this particular said operated actually parliament as notice. This particular changes this particular values, which is employees name. So let me just freeze this. So employing name exchange for is always of spelling errors or not. Complete employee name is jean days and spelling error and later Bart exchange. Of course, there was some other with anti records for this particular employee. So this showing now for these employees for these rows, for example, all of them have a change in their militants. Traitors. So they were married or there was rewards. Now they're single. For some reason, there was some change in the rate us. It's been highlighted here. Similarly, the salaries off the simply have changed. So, for example, for Casey, the salary has changed from last one to this one. So let's go and find out this and play with this court in both older Tanya to prove that this is working. So let's copy this and played court. So this is the employee. You see, a salary was 66 5 34 So please remember this figure and let's go toe the new details on ahead of Xi's, so a salad, etc. To 74 toe to toe this is the value showing in our very own strata is the change value Similarly, for this tramp lily, Trina Linda, salary has changed and for this tram ploy, Francisco and scarred their zip question. So they moved our place off leaving or something happened. Or maybe there that I was not correct. It's been corrected similarly, for this employee wall. Escort me. There's a changing the department. So it wasn't different apart when I was in I t department. Similarly, for this employee, Sean, the department has changes Well, so this is the way you can see little analysis or references for your data between old and new. The fifth shoot is called arid data. This should will populate all the retired, which are new compared to the old leadership which is their second ship in their nutrition with the other ships in this wear and sheet, whatever the guitar knew, for example, that day temperature at this is the tag which is showing us is that our new it just added into this knew that I had compared toa light. So all this data, which is has stagnant off ad will be corporate here. You can see all this letter separately. No different. Watch it all together. So let's do ah count. So I just like this and select the number of rows. So we have total 98 new rose or new employees that has been added in the new. Did you get a sheet comfort all later and let's Goto added it and see just account. So 98 for its correct right. So 98 employees which are new as put his variance analysis that we be analysis. They have been added here similarly for the removed later, all the employees Ricciardi moved, for example, after the count here as well. So total 48 please be Charlie Mood and let's go this removed later. And this leg count yes, there 48 empty. Similarly for the whereas that other 23 rows which have changed compared to all the new Jerusalem goto the wear and stop so decide them later. Our employees which have ah changed it asserts so there are total 23 employees. So let's goto the whereas the time check. So let's count. This can quickly is it's 20. So this is all this workbook basically works. I'll explain you each and every button here which you can see here on the top. I'll explain in what they do. And also what are these tags and also be explaining you how to change particular factors. Like you don't want to see this Remove where you can put something else. I'll show you how to do the same in the back and off the VB according the baby. So thank you so much for watching this was the overview and give you the destruct this particular competitor workbook. And from the next lecture on world, we'll have a deep dive into this workbook. Thank you so much for watching. 6. Most Important thing to remember while comparing data: is that the most important factor you should keep in mind while using competitive a book, Buddha said, must have a unique key column as a first color or call it, um, that it has that must be unique. Order of the columns must be same in both datasets. Besides, number of columns should be same. The order off columns must be seeming boarded a set continuing with our example in the previous lecture. This is the completed, I said. We just shorter 14 columns. As I explained before, As you can see, employee number is a first column, which is a key column, and also it's a unique column. The order of the columns must be same. Besides total number, which is 14 here. The order of the column must be same in Bogota, said That is last month, this month or early new. This is the old letter or Lastman data, and if you can see the order of the column here, employee number employee named, you'll be NATO fired a top ammunition. Farid Until the department, all this columns order must be saying in both old and new, readers said. As you can see, this is the new debt. Our despondent are the order of the columnist same and, ah, employee number for ski unique columnist there. So this is important for Sunni. Key column must be there. It has to be there in, Buiter said. Otherwise, this analysis will fail and you will not get the accurate answers. And second thing is that employee number employees name date of birth date off our this order off color must be synthesized number of columns that are 14 here. So in case they employ, name is a painting on the fourth stage or in column your F or anywhere else other than a second column, which is We column that it analyst is with Competitors will fail. The macro will not give you the correct answer, and also the number off caller must be unique. You cannot have employees name column twice appearing in the data set or it'll but column. A petting present editor said, If there is the case, then you need to adjust your columns and make sure that all the columns in a data set that is all they knew, which you're trying to compare and get the varieties are off. It needs to be unique. Otherwise, thes analysis will not work and there will be a Corbyn order in properly. You might get any. Do we be others later on time, hours or something else. So once again, let's free trade. Boudette, I said, must have a unique key column is the first column all columns that I said must be unique so they cannot be similar. Took along for the similar hit all the time inside. Otherwise this analysis will not work. And the order of the colors must be same in both that I said that is all they knew. So employee number them play name. The order itself must be seen in the borders that you're trying to compare. If you need further clarification of any question, please let me know where you intersection and I'll be happy to answer them. Thank you so much for watching. And I'll see you in the next lecture. 7. First Column of your dataset must be a Unique key column: one of the key requirements of using competitor workbook is to have ah force column with the unique value in this particular scenario, I'm using employees that abyss for comparing. So as you can see, the first column is idea off employees and all the values are unique. This is very important. Your older time, new data, Any data which you're trying to compare must have ah, unique first column. It's called a key column, and the values in this data like here it has to be unique. Recently, I got credit is from Lord of Users off this workbook that they're not able to get accurate answer. And even I looked into the data. The reason was that they were called me. They dark incorrectly. They're keeping the values which are repeating here on the first column. Ozone Unique Fully thing I did was I went to the data and checked the column, which had a unique values. And I put that columnist first column and then that that Alice is what? Working just fine. So as you can see, the data here is correct. The first column has unique values could go to new data. The four scholar has unique rallies. Let me share with you a quick bonus. Steep how you can ensure that your date eyes not duplicating here. And the first column has unique values. You can select a rate are like this. So I suppose you have. Ah, 100 or 200 plus Rose like this. Select that particular column. Goto conditional formatting in home conditional formatting and go to the first highlight cell rules and select duplicate valleys. Okay, Andi, it will ask you to choose your color. I'm going to keep it. Whatever the Ford is being you in here by Axel and click. OK, now, for example, if I copied this value again here, it will be highlighted like this. This is a rat flag on. You must not do this. As I mentioned, this scholar must have a unique values. It's a key column here in order 10 also knew that so similarly you can copy the formatting so you can just go down like this former painter and pasted here now again, for example, by mistake for our corporate is their target. You see, it's flying. It has a duplicate value. So never do this. This is not a committed, The court will not work and your analysis will be incorrect. So let me give an example what happens when you have a duplicate values or your data set has been arranging a such a way. That unique column is lying somewhere else other than the first column. So let me quickly show an example. So this is the example that I said old and new, as you can see here on the first column is called Sector On. This has repeating values here. So this is a red flag, and you're right. I shouldn't be like this. So let's try to copy this data to our component of a book and see what happens. We should A trick are surely before for copying the header. So I'll goto this old data in the source data which we want to compare in our competitive a book and copy the header like this Cantero Control. See, come toward a company that workbook and based it like this. Now the formatting is also being copied, but you can always change it. I want to bring some color like this. I'll copy the head of straws sheet and it's done. Now Let's clear the data. Similarly, Goto knew the time clear. She data Goto, whereas the North, there's no data here, so it's fine. And now what was so state? And copy the old data like this entire later basted as a value. Go to your new Rhetta and select and Target asset except the header because we have copied it already. Now it has been copied. So now we have our older time new data on let's compare it. And as I mentioned before, you can quickly check whether your first column is unique or not. Helen of the requirement is that your data has to have a unique for scholars, So this is not unique. How do we check that? So just stay like then column like this column coral conditional former T highlight cell rules toe Blake at values on this other duplicate values. And similarly, let me just copy the former King. Did you hear here also, the values are duplicate, so this is a no go. I mean, this is like a scenario where you shouldn't use component of a book in this scenario, because it won't give you the correct answer. Try and compare the greater So I click this button compassion in the weather and stop as you can see their day tires being analyzed. But it is completely false Onley thing. We can see your exchange data while if you see scan through the later Since the data set is small, we have 62 rows in new return and in the order Thai. We have 65 euros, so number of rows in new ties reduced so we can see clearly. And if the retirees is was correct using competitor workbook mythology that sure bean rows which have been removed, right or new door, which has been added so we don't get that. That means there's something wrong with the way we have analyzed the writer. And the reason is because the first column the sector is incorrectly ordered in the data set, so it shouldn't be the first color. It has to be unique now how to fix this quickly. So rather than doing anything here, I'll go back to the source state up, and I'll go to the old better here on die quickly check which column is unique column and in the entire data set in all the scholars, your total 12 columns. Which column represents the uniqueness off his entire Greta said. So this looks like a portfolio date. Our completed aside for a valuation. So the company name and ticker should be unique. One, right? So let's quickly check that. So I select this data like this only the column B company and goto our conditional formatting sal values and check if there's a duplicate value there. No, there isn't. And I think this sticker also is unique. So quickly are selected on copy, though formats on yes. Similarly, this also looks unique. The new better. This two columns are unique. First thing is, we can bring address. These two column is the first column in old and new. I would rather bring both columns as forced to columns. All select like this. I'm in the new data. Cut it on Select column and inside Cox l. Similarly, we need to do this for all the tires. Well, select BNC and bring them as A and B. So now the structure about it, I said, is exactly same as before. As you can see here and now, we can copy now. This time will be selecting the head as well because the header has changed. Order off the header column exchange on. I'll just based it like this. Go to new later. I'll goto new data here, select and tired. It has set and go to the new better and this special. Now let's clear the analysis, which we did, which is completely incorrect. Now let's compare it. And now we got the correct analysis. Let's copy the Verein State our toes different shit at it removed and were ants. As you can see, the retires been corrected. Analyze. So you're to be really careful. This Mr can happen already mentioning my your enemy course about this ensure that you go through all the lectures and insure that your data has a unique economist the for school and it has to be unique in order to have the correct analysis. I'll be updating instruction as well in this workbook and you can download it from the resources section. Thank you so much for watching. So in case you have any questions, please let me know in the Q and a section of the course, and I'll be happy to answer them 8. Deep Dive into Mechanisms of Compare data Workbook: So let's deep diving toe competitor workbook mechanism. I will explain knew each and every component and read labels off this like works mechanism . Before we start understanding this workbook structure dysfunctionalities in detail first and most important thing you should keep in mind just whatever they tell you're trying to compare must have the same data structure. What I mean by that it means daily later said all the new must have the same number of columns. If it is not the case that this work will not give you correct whereas Datanalisis So in the case the data for our employees here have same number of columns of 14 columns. As you can see it account for the old later and if you count the number of columns when you realize also 14. So let's start with all the Takeshita here. You need to ensure that you don't delete any rows or columns. So if you want to remove that are going through the use this particular workbook for another analysis you to ensure that whatever existing it are you used or you did in this workbook previously you shouldn't deleted so you shouldn't go and right click delete like this, or even tried really columns, right? You're to use. Instead, Car clear contended up. So for easy clearing off this existing it us that I located this red macro button, which is his name. Clear sheet data. So once you press this button re last question, whether you really want toe cleared it up, sometimes you might accidently press this. So there's just a safe mechanism where it ensure that that you're not excellently leading the rate. Also, if you press it and it has gone, you cannot recover it back. Because with Phoebe, a court execution is very difficult to undo those action. So let's say no for now. So currently, this particular micro cleans the sheets that are from rose three from here till little 10 4000. So But if you want to add just this number of rows like, for example, you don't want to clear this much about all the time because it will increase the processing time and Orel speed and performance off the sheet. So if you're gonna competitive up to a certain number of rows, for example, you always gonna compare it up to 10,000 rows or 5000 rose and 1000 rows said this macro accordingly. I will teach you how to change this particular number. So every time you press this button, it will only clear the maximum number of your range off later. So it will improve up off ones of this year. Your data set must have the same hard as you can see in row number two. So but example each the details that has saved number of columns with headers also are saying, As I mentioned just moment before, you must not delete any columns in the sheets, so you will need to adjust the number of columns depending on your data set. So the best approach would be to select Roto with any only columns where you want to wish to keep and then you write clean up the mouse and clear contents over example. You don't want to keep this particular department. You can rightly can do clear content. And you can just copy the any blank former tail like this. And you can also, through a clear country like this correctly clear contained. So this is for individual data formatting and or find tyros, right? So suppose you Data set has a 13 columns only not 14. So let's do that one more time. Now let's go back to old data sheet. Now you must turn. Notice the second gray color button right beside the red. Clear. Shit. Better button. This is copy headers to all ships. Is what exactly does one So corporate your later said with correct headers. In this particular sheet, for example, you need to press this button and this others in this sheet will be copied to all of the sheikhs. So all these are the sheets will ever same Heather, Let's see this in action quickly. So, for example, I'll go here and are just remove it like this. You contend I'll go here and you say my bed could be clear content. So now all the column headers are different than our actual coordinated right? So we want to keep this particular thing consistent. And for that, I created this particular button. So just Prestes, As you can see, all the others are saying now it has corporate everywhere, including this added remove and were instead which I changes word before, as I showed you. So this is what this particular bartenders. It copies the headers from this particular sheet to all the other sheets. Now let's move on to the next sheet tap, which is a new better. In this year you will see a similar red button, which clears the sheet data. So this will also clear from row to row 10 4000 which you can have just as I mentioned. And it completely removes and all the continual. But we must be sure that you are taking the right step and you really sure that you don't need this data anymore. So this particular buttoned clear the attack and it doesn't delete anything. He just clears the content of the century. The formatting will remain same. There's no data structure of writers said that is number off columns with the border that it has to be same again telling this because so many people have come across when we're have used this book, our complaint that it's not working because one of the reason was that they didn't put their column structure properly, so the next sheet type is called. We ran straight up to the meat and protect off this an entire competitive workbook So first thing you notice is that there are two additional. But as besides that clear sheet to tablet in the clear, suitable 20 years, the data from row 3210 4000 sent tyros selected like this. It goes down toe 1 0/4 and it clears it. Similar thing happens with older time. Do tell whenever you press this clear. Sh she deter Britain. So this is how it actually works. The purple button, called Campania, ties the magic Putin, as I mentioned before. So once you click this button, you will see complete that. I said being populated showing added that I would gain self background, removed it out with that cell background and changed it. Arid brown sell back on colors is also an additional first column called Factor Change. As you can see here, this is the factor. Change. This is more off. A key column is displaced. Their data compression output types. We have currently three kind off. Let us output type, which is change, remove and act. So as I mentioned in the earlier video, this other removed it as it staggers. Remove this other change their time with the cell container change and is either added data . As I mentioned before, you contain the wordings off this particular cell itself, and I'll show you how you can do that in the back end off this BB a cord, and you can change it to whatever you want. Beside this first column, let's drop. The columns are identical toe flavors to sheet, so it must be the same. So, for example, in these two sheets, we had total 14 columns, Remember? But now, in this way, Ryan shed real 15 columns, so it is 14 plus one, which is this factor column. So this structure also, you have to keep in mind while using competitive workbook. So this factor change column must be there. And also after that, your original column number of columns. So, in our case, is the 14 colors that you can see here as a total would be 15. So it's always one plus number of your columns off your data set. Now the light, your local butter name copy with as that our it cop is the date of the next three. She taps all the roads are according the Vera's Atash. It will be corporate categorically that is Rose with attacks in the first column will be corporate to the at it she data. So, as I mentioned before, the's Rose with the added tag will be corporate here. Similarly, remove Ritter tag will be cooperative, removed it a shit and real estate are tax. For example. It's not realized in this case it is called change. So this change data types, the protect categories will be corporate here. But you can change this particular tigers well to where I'm strong match with your ship name. This is possible because we are any of you be accord to do this as well. But keep in mind that wants to press this button. It actually removes everything here already existing all these three sheets and copies the letters that according to our criteria, sired, remove and change So that year to keep in mind. So in next lecture will be going through the we be eat vigil music editor. Let me just quickly show you a sneak peek and this is our main course. So we'll be going through this court. How given more Disick to comment as much as possible, and I will show you how you can make changes according to your wish, or there are total poor models and every morning as its purpose. So ensure that you don't mess this around or try to delete it at the right this world, we will not work. So in the next lecture will be going through this particular BB that is visual basic editor , and we'll we'll see how you can change the particular factors this names obtained type and any other thing you want to change. Thank you so much for watching. I'll see you in my next lecture. 9. Importnat - Changing Dataset headers for new kind of Data: hello and were controlled this video. In this video, I'll be answering a few questions and created, which I received in the course, especially regarding changing the first hetero for your data sets of, for example, this is ah employed that asset as an example. And suppose you want to change this header and you want to bring in more columns or you want to take this head was completely because maybe you want to use this worksheet for analyzing some other database, for example, since this is employed by bass, or even to compare now that it is it like sales status said, or a stock market that has said a payroll data said, which is different. And then predators said when either it has said, which is a structured format as a shit and what boo requires. You want to change the same right, and you want to bring in more columns or remove a column says Well, so how do you do that? So one of the rules, as I mentioned in the course that you cannot delete and any columns here you cannot just do like this. You select and right click and delete. Don't do that reveal that will mess up the court completely, and this sheet will break completely. What is the best? We now have created a new button here. This work will be able for your download. Have already uploaded it, and I already send a message. So if you are already so, stop with the course. If I know into the course, you'll get this new world with this button which is removed. Had a row. Now, as I mentioned, you need toe. Ensure that you do not delete anything. You have to just click this button. It will remove the head a row. So what if you want to bring in new later set and the head is country different than this. For that, you need to either copy the headers from your source, their time based it here like this, or you can manually type the same informatics. That's hard to do that. For example, I don't want this had anyone in the details that anymore. So first I will clear the she data like this Now that it has gone, I want to remove this head as well. So you're to use this button. Remove Had a row just a warning in case you accidentally press it. Yes, I want to remove it. Want to bring more data like this? Date? Name? Stock, Country T. Bartman. Their house. Well, you unit portal. Well, you all right? Suppose we have Twitter's seven column headaches like this. Seven columns in there does it. And I want to format it and choose the color I want. Like this. Now you can add as many columns you want just by typing like this, right? So like this and you can keep impairing. And now, since the requirement off this workbook is to have a structure that I said everywhere, especially all the new you need to copy this head everywhere. One show. Arrange your labels like this. Your columns like this or you need to do is press this button copy Had a stroll. Shoot. And now if you check your new data set, the head has has been copy against here. I didn't clear their data's Let's clear the data first here and also in added that I removed a tie in various strata. The header has been copied. Now, as you can see, the header has been corporate everywhere and now you can paste your date are old and new the way you want on do your analysis. Now suppose you want to change this headdress completely. And bring in your head is from honor that it has a completely So the years were to work around. This is go to your existing that I said that it does it from very want to bring in your data and the headers Copy and tyro like this. Select. I'm selecting row one in control. See and go to our component of a book can select Go to here in the old leadership on control E Now the entire row has been corporate, I suppose the header you brought in formula tests. It was informative like this. You can always do that. But just going here make it'd bore and give a background color. Teen the phone right? So we can do that. And then just click this parent corporate head a stall sheet and then it will copy it everywhere. So this other ways you can change your column headers and only place You're to changes old data here and then hit this button Cooperators tall She and it will be coping everywhere. And remember anytime you want to add more columns like this to your all the time. Trav HR York later you're trying to compare are basically here only because that's the only place from which all other sheets are being given data headers and which then copy, basically or Togo told a trash it and change your headless accordingly. So, for example, you want to add department here, right? Copy the formatting like this. And if you want to remove, ideally, you should just go to your source data from very want to compare that asset and just copied . And there for like this that would be the easier than other than deleting or changing menu can always change it manually as well. Like you can do like this, related Onda or unclear, contain whatever you like. You can do clear contents and just copy the blank former things like this. And then again, copy had a stroll past. Despondent will be copied everywhere with all the ships, so this other some of the rays and tips you should use while dealing with changing the headers for your it assets 10. How to Modify VBE Code for CDW-1: Now, in this lecture, we're gonna access B B E. That is visual basic editor. Now, since this is the micro nable workbook, you need to know basics off macro. So let me just quickly go through the basics. So this workbook has to be savored. Are there as excel asem or XLs B. And then you're to enable your ah macros setting. So you were go to options like this and go toe trust center, trust center settings, micro settings and you're doing able this particular option disable all micro's with notification. So this is the safest approach and you should do that before start using this workbook now to excess visual basic editor, you need toe enable this data for tap and to enable this dollar per tab, you goto file options on go to customize ribbon and ensure that this particular type is enabled an open So by the fall, if you never used visual basic in your life before or if you're installed, a new copy off your office and you haven't enable developers tap it will be on ticked but your toe take it here like this and click OK after other deliver time will appear now. The access usual Basic. You can go to hell. Overtime here and click this button. Unusual. Busy. All you can use the shortcut key has mentioned here, or Tafel ever like this. No, this is the back end. No far were competent a workbook. So this is the workbook and this other models, as you can see. 1234 So first model is for ad removes she So this particular moral is for this. Three sheets ended it. I removed it and we're estate up. So this model performs. The copying off for their top is during the attack in the weather and step and second model is the clear contained model. So the one we're clearing the contain with this button here in the or later sheet. This part in scored is here in this particular model, clear content court. And this is the main court compare sheet. So whenever you press this button in the air and sheet this court Ron, send us the magic off comparing our data Roy's and then sell ways. Let's go quickly to the headers also. So this is the headers, macro. So this particular button in old it I shed the macro behind. This is here in this particular model. Copy. It is so doors sheet. Right. As I promised before you can change the names off the sheet or let generator. So, for example, I want to keep this as a last month as a new name on here. I want to keep this. This one You can keep any name you want. Espadas. It is allowed us for the V b A according metrology. Andi. Now, if you try to compare their tie with this name, that should be our popping up. See, it couldn't find Doreen now, since they're changes named that other came and to resolve later to change his name to this name. Last one. So if you remember before the name of this sheet was or later remember, I just seen moments before it was or later. So I have to change this truth, this particular name. So I'll just copy and paste and same way the sheet name Waas. New data before, As you can see here and now, I'll change it toe this month. So that's going to change that as well. And where I was. Let's keep it. Where and says it is because it's more for descriptive sheet name and analysis itself is of Aaronson office, so let's keep your dark. 11. How to Modify VBE Code for CDW-2: If you come to this sheet on, let's compare it. Now let's see what it's working. As you can see, it worked. So it will be careful when you change this particular too. Sheesh name after instruction There for us to sheets, which is last month this month are called it all that are new data for the clear understanding about this world books a little structure, since I promised you also that we can change this tags as well. So let's try to do that. So currently there are three tax. The more tag is for the deleted data from all to new. On change tag is for the data where the cell value has changed. The change that are are there in the bush each. But there is a change at the cell level, which is the most powerful feature of this component of a book that is called change Stark and ad tag is as excess. It's a red data from all to new. So all these three tags we can change it to something more meaningful if you like. So let's go and do that. So we are back to this computer model, and here is the section where you can change his name. I already provided a detailed comment here, See, and for others as well have put common so you can follow along. So let's make it reliance like this new data at it. They're tired of mold. All right, so let's clear the sheet. Always clear your sheet when you try to compare it are fresh. As you can see, the name has change that I remove for Ah, the motor parents has come the world we're insisted off change. And instead of aid ability, Ad really knew that. Added Right. So this are you change this tax now or if you want to change the formatting and the looks off these three colors that is red, green and brown, so ensure of that you can do any in formatting you want. So I prefer to do like this where cell background is red and ah, the font is white, so it's clearly resemble that this that has been removed. Similarly, 14th attire cap itself back groaners dark brown and for color white and similarly for every date. I want a green color as a self background and white phone color. But if you don't like this, you can change that as well. So to do that, where to go here. So this is for the removed it as you can see here. So for removed it or deleted data, you can make changes here. This color Corey can find it online for your exact color. So interior color stands for the cell background. So to show you as example, let's try to see this color on any website Where this algae court can we recognise? I opened This are a number of sites. So the court to 55 00 If you put here to 55 00 you'll get a dark red color like this. So this is the color court for there. So if you want another color like for example, you wanted this kind of a different kind off Iraq. Syria to put 2 54 51 51 So, for example, let's keep this kind of color. They're being key shred. So it is 2 5500 or 210 It was alleged to that 55 102 All right. And I want to keep the phone says white. Oh, you can change it to black as we're sure to find they're 11 core for ah foreign. So VB white stands for white, but you can change. Other is well. I will put our friends to this VB courts in the resources section. You can find their since I want form Toby board, I said, True here. In case you don't want the phone Toby board, you can put false now for the changed it are. We had a brownish color, right the background. So let's try to keep it something else. Let's keep this kind off light town color. So the court is 2 55 1 70 years, 10 do. And for the added guitar, we had a dark green color. So it is 01 28 0 So let's test there. So it is kind of color knowledge. Choose a little bit lighter green. Something like this. So it is 10 to 2040 So let's do that. Zero. Apparently, there's some problem with the color black here and try to change, so I'll change it to blue in ah, change data and in arid data. So instead of it being black, I made, it will be blue and rest of the things are saying the color off the cell is also the same I mentioned. 12. How to Modify VBE Code for CDW-3: So let's go back to excellent. Try to turn our computer court. And as you can see, the color we wanted has come and, uh, cells have been highlighted properly and also the backbone off change. It has changed as well. It's a different tan color, and we kept a lighter green than before. And also the forint is blue now. So both for where Esther Time knew better. The front is blue and for the deleted, that other front is white. So this where contender properties off this competitive workbook analysis and you can incorporate your own corporate color or the color you like the most. So this other few changes I wanted to highlight with the baby court arrest of the things you shouldn't touch change should be extremely careful by changing the court here and there . So she face any problem You're more than welcome to contact me in the KUNA section. I'll try to help you out, but before you seek for help, please applaud or send your competitor workbook. Sample data as attachment to Google Driver, one driver, whatever where you want. I will not be able to help you effectively unless I see the problem with an example especially this workbook attachment now is explained before for last minute this month you need to goto the core module and change the names accordingly. For example, last month this month and we're ends here. But for these three time, which is knew that are the little that I changed it up. You want to change the name to something else? You can do that. So what if I want to make it as ah NIU later Rose instead of the previous name on diluted rules like this and changed it are was so this one are fact that court performance But one thing is more important is that you to go to this particular first model and you retain the back name, remember, we change the back. So if you go back to our spreadsheet the United States, we change the street agri rounds instead of change. We made Ryan's Instead of ad, we put new data, add it and instead of removed, we have changed the world data removed. So we're toe being this particular attacks into this model so and set up ad, we will report this particular word. So let's do that returns and stirred up change hopes. Red lines. And the last one is the very moved. So instead of removed here were to put this name no. Now, if I copied this, it should work just fine. Factor change What has changed to a new target instead, Off a double the guard for the Littered rose. Same. And for changed the titles also. But you don't have to go in Tin Yun. If it in the name of this sheet, it doesn't matter the court earlier and only thing you do take care is that this factor changes. You change here originally in the main model of 13 this wordings off tax, whereas that are literally mode and you retired it you to ensure that you make same changes in the first model which for these three sheets or to go here, priority comment here since stirred up. Remove your military move. So instead of alliance were put reliance because where I suppose the same world I think it was changed before our meeting Reliance and and for the ad editorial toe make change accordingly, knew that I did so these words and this world this tree should be saying So that's about it in this particular lecture. And I tried to explain you the mythology off this company about books. We be editor, and you can do this changes as spot average. If you face any problem in making those changes, please do. Let me know in the KUNA section and I'll be happy to help you. Thank you so much for watching. And our next lecture will be going through cleaning off later with help off power query. So it's gonna be really exciting. Lecture, power Query. Ease my favorite subject and you'll see how powerful it is. And before you compare this that are you must clean your ETA properly in property to form a razor. Mention interlace lecture. Oh, thank you so much for watching and I was seeing the next lecture. 13. How to Modify VBE Code for CDW-4: as a permission, the little actress will be going through the court behind this. Clear. She did a button. So this condition turbine when you press it, it deletes all the rose from road 3010 4000. Now, as our next men before we be a is a little bit slower and the court is going through all the processes. So we're not going to late 104,000 rows. It gonna take some time, and it will increase the overall performance and processing time of this book book in case you're not gonna deal with such a large amount off later on, when they were going to use this worksheet or workbook for a limited number of writers say example, we're gonna deal with only like 1000 rows or 5000 rows. Max can change the number of rows you want to clear every time when you press desperate. So how do we do that? So for to do that, we're access the visual basic window office and added the court and you're ready Just one piece off line in the court and that's about it. So let's try to do that so we'll go to usual basic, and you go to the second model, which is clear content cold. And this is the core which does this job. And the first court, as you can see here, is for order, which is the first sheet, which is for a this month, that are, or later it's trying to delete from row for 210 4000. So instead of 10 4000 you can make it like, say, for example, it's clearly 1000 rows right, And after that you're going through this particular second Courreges for New Better. And they're also you can change it 2000 rules, and similarly, for the changes sheet, you need to make it 1000 rows. So every time you push this button this part of liver and clear shit that I don't delete only from Row Four or a three till 4000 So let's try to access that quickly. So to really Pedro 1000 which is this this road. So let's keep hear something just, for example, just to prove that this court is working and it teach deleting all the content. Still, Row 1000 so would come up and let's clear Let's go down. As you can see, it has been deleted. It's really for this one data sheet. Also the macro clear only from rotator 1000. And for whereas that as well. So this will ensure that order performance off your workbook increases because depending upon the latest, as you want to compare and you want to use this workbook for you to keep the order process in check and make sure that your book perform edit best so but off the ways to improve that is to ensure that whatever number of what are you going to use this workbook for you at just this care shit that top button accordingly. And to do that as I show you a good visual basic and we're gonna second model here and change this particular a number here. So rose for 2 1000 and rose for 1000. So all these three shooter to make change accordingly. Now, don't anything else, because it will break the solution itself. So, leaching this any ferry problem changing this particular number please reach out to me like an exceptional we had to help you. Thank you so much for watching. And I'll be seeing you in the next lecture. Thank you. 14. How to clean and shape data into propert structure with Power Query: Now let's start to clean the top little pop. Our query. So this is our old data, which is Ah, old at our previous wonder time. This is the new data. This is the same retires we have been using in the previous lectures To make you understand other concepts up competent a workbook. This debt ties in a row. Former. The data we have used already is in a very good shape. It has been cleaned properly without proper park ready. Now, in this lecture abusing power equity to reshape the data completely on, bring it in a proper comparable former. So you won't get into silly errors where your data compression is showing wrong, incompetent, overbooked. This record the data types and it is not well formatted. So what? I mean, while x over example. In visit as you can see, the date surrounding a Viet former and there are many ways to clean this. As I mentioned in the direction lectures, we can use formulas and functions and do ah, law Francis Stuffit formulas functions, but the park it is free, adding and it is ah, repeatable exercise. So it once you have set up your cleaning process and you're gonna import again. Same rate, our new return, the same structure you just had to click. Refresh, and it cleans the red. Also. I'll show you what I mean in a moment, but for here you can see the dates are wrong. Maybe the tax has some spacing at the back, so that doesn't work. The number former It may not be number form, it can be intact. So there's all these things can be easily clean by power query with a few clicks and it also, as I said, it is a repeatable process to start cleaning this data. We need to first click anywhere in this range. And since it's ah continuous range for rows and columns, park really, really convert this state are your daytime to our table formers. Since I'm in office 6 65 or Geno for Microsoft Excel, the menu and the structure itself may be different than your wasn off, except keep in mind that you need to a Minimum Excel 2010 to work with power quickly so inaccessible of and parent Exeter and 13 you need to download power Quit is adding, I relieve their linking it in a complete article on my website. How to install power query for any kind of what Europe excel. Of course, it doesn't include anyone later than Excel 2010. So check out that article. It has some videos and amazing step by step guide since I'm in office 65 which is almost equal to excel to those in 19 the latest one. And for that you'll go toe data time here and go toe here in this particular section called Cat and Transform. The power create is the old name the new name. Microsoft has changed it to Kate and transform. So since very a table and range were to select this particular option from table rates. So this is our source. So once you click there, Excel will ultimately ask you to cover in the table. Were to say OK on this particular it I will be important into a power query window like this. As you can see on the screen, this is our data in the power query window. Now, since this isn't a power very course, I wouldn't be able to explain you each and everything. But don't worry, I will leave a helpful pdf document and a reference to a very fantastic book becoming of with the very concise goes on Getting started with using power Query. As you can see, these are columns and we start changing. So first off, all the employee numbers already team. So when so important time. The letters for Geno Power query it automatically a place that seems data time, which can What should it automatically it? Power query just is the writer based on the data types. But here also, there are some later types which hasn't been changed properly is over example this state off higher. Let's try to change its to date and now it has changed correctly. As you can see, you can keep employee number that the tax here because we're not gonna do any calculation with the employee number. Selects. Replace change type employees. Name is properly former as a tax states accord is also correct and data, but also to change it to date ages. Fine. The gender is okay on medical status. This fine citizenship is okay again. The date up domination We can seem to date department. Okay, position. Okay. Paid it. Fine. You can change it to currency and similarly will change the basic salary to currency next. Except, uh, replace the changes. And now our data is almost ready now. I also want to remove few columns and also changed. Daughter of Column. You can do that as well in Parker, Ariz. So that's quickly CR completed data for matter in our company of a book in the example we saw before. Now this is the completed a tough format which were used in a competent A workbook. So we want and play number Employee named it Open data. Fire it open ammunition period base salary, state, zippy sex, marital status, citizenship and department So they not only 14 columns were looking for, and currently we're having more columns than we want. So let's go one by one. So employee number is correctly ordered, which is the first color. Let's spring employee name here. You want NATO birth after employees name? So let's do that. NATO fire is fine. Let's bring in debt off termination after date, off higher, and then Barrett like springing better it on base salary after the period. Then state Zip, age, sex, Meritor status, citizenship and department. We don't want position. So let's remove that data set is ready all the details properly for murdered. And let's export this traitor toe a new table. A new sheet will be in the same workbook, but in a different sheet. Click close and lower, and it will create a new shit like that Ship three and we'll give a name or data clean. So this is the kind of great I we should import into competitor workbook. This will ensure that there are no silly data formatting errors. I'll show you what I mean by that will manually change the write off all marks. And once we click the competitor but only will see that it will show us a change and it is not the case. 15. How to clean and shape data into propert structure with Power Query -2: Similarly, let's try to change the formatting and clean that off for new ties. Well, so similarly, will Goto Data and Republic from table slash, range and power career open up in his window. And let's name this Kredi new data and your toe fall like that Same steps. Where I can do is that we can copy the entire corn. So this bird at once, But basically I'm gonna follow same steps as we did with the old attenuate, and so I don't want to redo that. So another thing you can do is copy the court itself. So this is the or later sheet scored. So I'm going to copy from here teens type like this and cancel. I'll go here knew better and once editor and we're not based here like this and clicked on . And, as you can see, exact same step they're applied, were clean the rate Aurea Che in the former off the date for you re ordered the columns and there as window as I mentioned the initial lectures that the structure of the writer should be same so the number of columns should be same in the column order should be seen so power query and shows that it is the same. Otherwise there would be a lot of issues. So here we have same number. Off column. Let's Lord, this data closing Lord, it will lower into the new sheet as like you can see here, I limit toe knew that are clean on. Bring it here. So this is the shape of far or later and this is the new data. Now let's see the number off columns, which are a toe and retired 14 columns and here also ate to end 14 columns and the column headings and order of the column. It also seem now we can bring in this data to the our computer workbook and do the analysis . So let's do that. So this is our component of a book, and we're gonna be cleaning the old better. Ex Clean knew it as well. So this is our clean data. So all that I will goto last month, and that's corporate this and come here and based similarly I'll go here Toe knew better Exco operatives and based it here. And let's to our magic with competitors. Sheet metal. Now your pasted are older. Turn knew that after cleaning them on, let's click the button off magic. This is compared sheet, so the the Thais screen already in the barren sheets will click this purple compare sheet. But and here you go, the removed a ties being highlighted in this kind off light red color sell back around, and the dates seem to be our because the day tight celebrating cause we have pasted data as , ah as a number, so we can quickly change that on Let's do That once again. So let's clean the right off first and compare it again with the corrected former. Nowadays, it seems to be okay on the changes as we can see these other data which has been removed. This other data, which has been added here there are changes in salaries. There's a change in medical structures. There are changes in the citizenship as well. Now let's see what happens if the former is not correct. So, for example, for this part of your employees, Thomas Marais, we're gonna add few spaces like this. As you can see here, press enter. So you see there are some spaces, and also let's change the state to an American former. So let's keep it October 11 2014 like that. So that also has change. It's a new date on. Also lets saying this this number format off salary to attacks on similarly put some spaces , some spaces or some spaces. Yeah, let's see in this to an American former so July 6 kids or digitize this kind off informal date our data, which apparently seems okay, but it is not. There will be problems. So let's do the comparison again. We're going to clean the data again and competitive as you can see. So this employee, which is Ah, Liza, we change the date toe on American former from the British former fit has shown us change the species, we add. It has also appeared here in the change. Similarly, the Mary Thomas name has changed. Actually, it is no change. But since there are no spaces here, he went up there. Cosac, for example. Here there's extra trailing species. But if I goto the new letter that is this month's traitor, there are no spaces. So this former disconnect, but not this one. Similarly, for other employees, also, we saw there are spaces it will show so here. Also, the date is a different former because it's an American date instead of the petition. So this. What happens if you advertise nor properly formatted so to ensure that you used to like power credit or, if you're not familiar, park career don't want to use part. Okay, for some reason, you can use formulas. Information and short, the curator said, is absolutely correct. Your formatting over ties correct. And in that case, only you'll get accurate. It have Iran's analysis with help off competitive. So thank you very much for watching you for any questions or queries regarding the cleaning off. Get up is let me know. As I mentioned, I'll be coming over the park query basic to intimidate course very soon. So look out for that and I'll see in the next election. Thank you. 16. Practical Application of Compare Data workbook : Now let's understand the practical application off this company. I work So this is the world has been seen before, and here I attend the names off the or later new data to 8 August 10 22nd February. So this is Ah, somewhere is washing off a portfolio investment between two days. So this is a target with an eight industry 2nd 19 So if you go to the leper type and go to their main court, which is the third court model, I'll change the dates accordingly. As against see the four sheet name is a target stores any 18 so eight off 18 and the numerator sheet name is 22nd firm 19 so accordingly, if you see here, the names has been changed. Realized are kept the same when other things are kept the same. Now this is a summarized version report full. It has, ah, various column. So it is a unique spot for the mutual form. So this is a mutual for investment portfolio on a different date, the position you need sparked an average protest, plays some off purchase value. Current value is on market and see e g r. It so complaining and your growth rate, some off gains so far, accumulated gain, average holding period and average holding year. So this is the summarize that are same kind of structure of retires as alternate Second. Now I want to see the various between the student and search with the help off our component of. So I'll Goto Alliance and let me clear the existing reliance. Let's compare the sheets as you can see the rest of slow this other new schemes off Mitchell furniture has been added in the portfolio as under the second from 19 compared to 8 August 18 and these other changes off course, the it's a market link investment, so there would be changes and is again see many changes. Almost all of the changes here because stock market changes almost every these two schemes have been deleted from the portfolio. We'll copy the data accordingly here, as you can see, new data are also these rules have come here. As I explained before, he later draws are only two. As you can see, they're only truth skin, which have been removed as off 22nd 5 19 compared to a togas 18 and change this other schemer. There are changes at cell level. So this is the one off the key strength off this company. That workbook, as explained you before, in the early elections that you can use our programming. You can use formulas you can use. We look up. You can use Parton Eskil, but you cannot get this kind off cell level. Iran's analysis. So this is the strength of this component of a book using Ruby record. This had only change. This salad has been corporate here, so that doesn't give any kind off meaningful analysis. So for that work hard and I copied this particular format and link this change. The tire was here. As you can see, this Rose, I've been linked to this change, Kitaro. She does against here and now I'm competiting relies each center. So for that I'm competiting What happened? Actually, in this particular this particular scheme support fully our existing in the both the strike zone a togas this game. So they're all sort on the second floor of this scheme. So then I want to see exert changes which has been generated by this particular macro. And this particular rules have the changes now to see what exactly are this scene? So to do that you take this particular scheme as, ah, look up column. So I just apply every local function is against a year some looking up for this particular scheme name and I'm going toe the old sheet, selecting and tired it assert they'll probably look up as a table, ari. And against that, I'm comparing this particular Rita for the same scheme. So if you come back to analysis Iraqbodycount long term equity form, I'm comparing its ah situation as off 18 August tenderness come February with help off this particular look off value. So this yesterday front between these two. So let's understand what it is. One for someone one means. So if you go toe 8 August, let's find Barack Brick, the total units where 6 24.87 we go to 22nd February for the same scheme as you can see here the units are 2195 Now I'm trying to find a difference, and that could be the increase in the unit so to 1 95 minus 6 24.87 will give us 5171 units . Similarly, same formula has been apply it with the help of Lucca for all these rules and records, and all the cell level analysis has been done is overall increasing number of units board between these two date is 4 to 47 average price have changed. So far it has increased. So we're all average increases to point to wait. Part is valley off, causing clear. So there is a participant 80,000 units in between these two days residency and the current value has gone down for the scheme, as you can see. So we need to look into this portfolio and tries making some changes in the scheme. So only there is ah, hi reduction in the C E g R, which is a negative sign for this portfolio, and something needs to looked into anti in the schemes accordingly. Similarly, some off gain has also gone down between these two dates. Average holding peered has increased up, obviously, because this is a periodic data and similarly this has increased. So there's a very brief example what you can do with competent a workbook, so it's cell level where Frances is a key in any kind of analysis. So this is a strength off this workbook, as you can see the available to do this kind of powerful analysis quickly. And now we can go back to the portfolio manager and ask what is really going on with our investment. And if there's requires any changes, we should do it immediately. Now, in the next video, I'll be going through a very complaints example off preparing, ah, dashboard for the payroll. So we'll be going through a comprehensive payroll data between two months and try to investigate the variances at very detailed level with help of competent of a book and also in that particular workbook, I'll show you how you can enhance this particular sheets toe work in a dynamic manner. So whenever there is a new data I read, it ultimately works. So I won't be teaching you each and every component and formula. How to read the formula because there's not an Accel formula course, But I will try to give you ah modeling approach, which you should follow to make this particular company workbook dynamic solution for comparing your rate are so we'll see that in the next lecture. So thank you animals for watch and I'll see in the next lecture. Thank you 17. Build Insightful Payroll Analysis Report with CDW-1: hello and welcome. This lecture in this lecture will be going through a company into payroll. There. Talentless is like this. As you can see, this is the dashboard portion, and it has been possible to help off competitors in this video. I'm not gonna prepare this dashboard step by step, but I will give you this file as a template and also ill go to reach and every function and formula used in creating the stash. For now, when you download this competitive a book as a template, you'll have only seven sheets as expending. The only electors will have instruction last month. This month that is older. Turn your eight hour Iran's added eight arrows delete It arose and changed the titles. But since you know we have genders names in our previous like just in the dame off access will be accorded eating now to prepare this kind of solution. And I'm assuming you're going to use this workbook for this kind of practical purpose. Like in this scenario I'm comparing two months patients is December and January. December rating was a January 19 and I want to see each and every element that is employed count crossed salary Basic on miscellaneous on other allows his own site allowances. I want to see the grass is between the two months that I want to re consolidate at that employees, the more employment and simply similarly, I'm trying to compare December since January, the average payroll so average Paranal is, is very helpful in analyzing payment you're doing to your employees, especially in ah, industry, where there's a manpower in war. So here, in this particular reverence analysis, I'm trying to compare all the variances more than 5%. So wherever there is a plus or minus more than 5% that is Ah, Brad signals. This formatting is done with conditional former T in Excel. This basically compares on basic pay in the previous month. Let his last month, and they were by number of employees. So we'll go through that step buster. But let me explain you the entire structure of this workbook and how you should ideally use this company. A workbook for analysis Purpose as I mentioned, you will have this seven sheets already have a level when you download that blank template , and I added, in this particular solution extra sheets, they can see here so that I want 23456 And the dashboard sheet. This other additional shoot. I read it to this template. So what is this last one sheet? So this is nothing but this months later. So this is the or data are last month, as you can call it and how hysteria there ordered a year. And also this one the new date. Our pastor here now why we need to create this kind off table is exactly same better there . Isn't that when you gonna reuse this sheet when you gonna delete the date are clear that I will delete all the roads. So even if you put some formula, even if you use name ranges here and try to use this date off analysis, it will fail because they want the Rosa related or the formula or even a name Raines created for this particular data said it will break immediately. So for a safeguard, you better clear this kind off official Acceptable. As you can see here, this is official excellent table and simply are put a formula like if there is no value here, do not put anything. So let me show you function. So if there is no value here, there is difficult to empty, put empty, otherwise support their value. It felt I assume that I'm gonna analyzing fixed number of employees. So number off Maxim employees in this analysis will not increase around seven index. So how kept the rose up till 719? As you can see here and currently we have only rose up to 5 68 So there are total. Let me just quickly countered 5 66 employees. So in case this number of employees increases this cup particular table, we link it automatically, and the formula is calculating accordingly. And now are the reason I'm putting this kind off empty spaces and putting the formula instead. I could have just statically linked it because we want to see exact count off numbers, which will be useful in our dashboard, which I'll explain in a moment and similarly, starting from days were column I'm putting zero, and that is also a reason why importing and start off can see here. I'm putting empty space if there's nothing in that particular sheet last month, sheet on putting empty spaces from this column onwards from day's work. All that our financial letters. I'm putting zero similarly, this month. Table, this is additional table again. Comparing this man data is linking this man data exact same way. And a very unstable is linked with this wear and sheet. As you can see here, this is our main were and shoot. I'm bringing this data here once again. The reason we are doing this because this particular but unclear shit, but deletes the rose food it Any particular cell, a road, the formula, any kind off analysis link to it, for example Name ranges. Oh, anything. It brakes automatically just to safeguard how created these stables. And also, if you are working re tables, it's a structure approach to work with their time. Also, you can create formulas better way with the structure formats in the tables. So I'll show that in a moment 18. Build Insightful Payroll Analysis Report with CDW-2: now a new employees is a table linked to this particular she new titles which is created by this button. Remember when we pressed this button, It brings all the data for added that are donated their time. Whereas data in this three sheets now here also a new first is spot on every time it village the rose here and bring in the new analysis that is the Rosa related here. And if you're gonna use this particular shoot directly in any kind of analysis, your solution will break for short. So again, for safeguard purpose, I have linked their data accordingly Here, As you can see, it's exact same structure just in a table for meddling to the ordinance. Six years new employees is a quarto. New de Toro's deleted and places called who deleted the barrels and variance. This is the tricky part here. This is the most important. Expect off this analysis here we are trying to find out what actually has changed. Well, don't this employees who have changed in their data? Sir, you're trying to compare what actually happened. What is that change? So let's second example So I link this data from here So this particular cell, me three, is linked here, right? B three. And if there is nothing that what we want empty space Accordingly, the date off join category on department is linked. Now the most important expect off this computer workbook is to find out where I am CET cell level. If we go to this particular sheet here, these are the most important roles in this perilous is why? Because that changes at its cell level and those rose and those employees courts we want to analyze between older turned red. I have explained before in earlier lectures this particular employees are existing in both last one in this month. But there are changes in their attributes like this. Work numb, normal hours, holiday hours, public holiday hours. So this information, we want everyone to see how it happened, why it happened and then do any kind of further analysis in this shit. Variants employees. Since we know that these are the employees which are coming from this sheet, right, they have changes in them. So these are the rules in which there are changes. And now what are those changes and how to find that? So for that we're gonna be using simple. We look or function. And if you're not familiar with a look of function, you can refer to the guide I have linked in the resources on my website. So this guy explained that we look or function in very basic and unusual manner. And I'm sure you will understand the basics fundamentals off the local function following that guy. There is also a video tutorial there, so I'm sure you will understand what we look up actually does. So now we're trying to find out what happened between this one and last one for this employee for 312 and we're gonna start with Day's work. And for once we write, this formula can just drag it because it's formalize dynamic till the end. So from day's work till net on, you want to see what actually happened? What changes has happened for these employees for 312 If you see the function of return here, let me zoom Obert So it's we look up and play court. So this is our table structure. This kind of formula will return if you're using in table and this is the one of the best feature off this Excel table, so you don't have to even worry about it. There's in heightens control on others, so there would be no other as longer. This column is there. The formula will be similar now that we look up function as, ah four arguments, usually. So we look up. So look off. Value is 41 2 And what is the table, Ari Now there's also one of the benefit Top Excel table is that you can keep the table name like this entire table in which is the name of this table. If I go here this month and if I go here and show you this is the table name, so entire range will be selected automatically. So there is our table area. What is the index column, but is, ah, column index number, which is number five? All this structure is same, so that will be always same number of columns. So if I go here, I have given this kind of manual imports, and I just graded out. So it's not very important for the analysis, but it's important. Follow formula. So this is column number five, starting from employee code so American link this particular sheet here in the sheet or you can link here as well. It works both with and then false because we want exact match. So if he highlight this and see what is the value of this? So to do that here to select like this and press f nine and you get number 31 which is my number of base worked 31 in this month. So let's go and find out and related. That's all. Copy this and play court Goto This month Table and find is simply here he is. So there is 31 days in days work in this month, that is, in January 2000 19 years worked for 31 days. Now let's go to last month table and find same employees. And as you can see, he has worked for only two days. So let's go back to our formula. So, second formulas Exactly. Same only difference is that this employees data we're looking in the last month table, which is this stable, right? Exactly, same formula. So what is the value off it? Let's press F night. That is to, as I showed you before, 31 minus True, that is 29 days. So yes, work 29 days compared to December in January, close of 19 right and similarly normal. Our ideas worked has increased, so positive means increase. So 31 minus two is 29. Similarly, if we just quickly highlight this normal hours so he has worked for 1 74 hours in this one . It is January 19 and years were put only 16 hours, which is obvious because only work for two days. So that different between these two, it's 1 50 so similarly for entire that I said, we have done this kind off parents analysis. So is that on as increase computer last one? Obviously, because there is a correlation between the moral day's work and their overall pay package. So there are minuses also here. As you can see, this particle employees 40 05 years work less, so his payroll always will be less right. And for the entire it all said, we are doing this kind of calculation now. This will really helpful in ah identifying any kind of alliances between this toe payroll sheet 19. Build Insightful Payroll Analysis Report with CDW-3: Now let's go back to the dashboard. So this is the way you should ideally design your analysis using competitive workbook. Now let's go toe dash for and see the real power of this analysis, As you can see here, the first section where I put employ count cross salad, e busy car, miscellaneous, others seitan at any other allowances or TR Holiday overtime night over time, public holiday or time in total over time. So time is nothing but total of this four columns His column. I toe l so total over time and there are Deduction section. Here's a warning direction at once Saturday direction and then also later as production, and they sort of reductions or subtitle of this and that own. So now all these things are coming from this to table. So this two tables, as you can see here. So let's go back to dashboard. So I'm getting this number off count. So I'm simply referencing their total off the table right this month. Last one table. So December is a last month table here. This this particular table, which is coming from last month Data analysis later, last month's actual Later. As for our last month. Main data. As you can see here it is linked to their and number off counties. 5 66 But since I put this counter function like this up totally count a only counting the numbers it's counting for sisters complete. So in case this employee increases accordingly, it will count employees or decreases. It will show the reduction is well, So I just leaned that employees court totals in last month table here and similarly for this 1 January. This data said the number of employees are by 61 as you can see here now, reduction or fire employees were all from December to January. As you can see here, this minus start now similarly, our friends gross aladi So this is gross salary total drop. So if you go toe last month gross salary here in this table this is the valley 1 34 1 48 and similarly for January it is total off causalities. So I just drag this formula like this when it is driving the value here. So all these cells are the water rose up this tables last month and this month this is the net only now there isn't had a check, which is simple, like a total salary like this or total salary Basic aren't a lortie and then plus over Time's up torta minus the reductions. It should be one, probably seven for 20 years, so it should be same as net on, right? So this is a check there. Any other evidence you know you should investigate. That must be some problem with. The formulas are linkage on. There's some problem with their time itself. The alliance is nothing but whereas between two, whenever there is ah increasing payroll or all payroll, it is highlighted as a rat form. If there's any reduction in later is a green form, so we always want our cost to go down in a gentle manner. In. In most of the cases we want that. So it's a green color like this green text, and now if you come down here from 09 onwards, you are comparing at employed him. So this particular Veron's has been explained by this particular number. So there are two identify sixties employees in December and 5 61 employees in January, and there is a total reduction off I in place but how exactly that happened, and what is the reason behind it? So you can quickly see through from this particular analysis here, So at employees, so simply, I'm referencing the employee table from here. You can see just coming from new data Roshi, our main competitors sheep segment. So how I'm driving it so I'll go down here and go toe totals And how may totally any sheet you can cater total as long as it's a tax value, you just want toe derive the number of rows, which has data. So 21 employees that return, um, place. So we go back to a dashboard for 21. Similarly, for the literal employees, there are 26 place I go to related employees sheet, which is a table which is daring from our I mean competent outside when she deleted rose. So if you go down and check how many employees are 26 employees and there are 5 22 in place , which is a very science in them, so the titles, which is where Ryan's outfight 22 between somebody in January. So if I goto Iran's sheet here, which is coming from this main sheets, like went off, accompanied of a book. And if you go down here in the table and C 5 2010 place now, how do we reconcile this now? Added 11 which is there and simply then added. Be 21 to it. And then from there ar minus B 21 again and with the simply. So if I make a total of 5 22 plus air employees, it's 5 43 in place if I make daughter Lo Fi Trentadue plus this 26 which is 5 40 So what are different? Should be five between this and this is minus five here, as you can see and they should be Samos this or are total should match. So that would only work. Employees. We just changing their attributes like gross salary basics, ality or any changes between December to January are 5 22 A number of employees. It's a new in January, competitive number 21 then we're off. Employees were left who are not there for any reason, like they went on a vacation or they have been resigned or that they've been fired or whatever reason, they're not. There are 26 employees. So if I add number of employees, kind came into January 5 43 Right? So total 5 43 employees, ideally, should be there. Miners stamp lazy child left 26. Right. So if I take total of this 25 48 or always rise fees coming minus five, as you can see here and that is the exactly same variance we have between or all. Data count, like 56 employees are there in December and fastest templates are there in January 2. Both are reconciling. That means on unless is this accurate? Simply, there is an increasing order. Gross salary, right. Even though the number of employees count has reduced from December to January, there is increasing gross salary for whatever reason. So that is the reason you're to investigate further. So $41 has bean or all increased and how this 41 is increased. It is explained by this particular analysis so similarly here I'm comparing new employee table this table and I'm taking the gross salary. This particular column gross salary 5136 and I minus ing the removed. So added means new employees which are brought in and there is a positive impact increase in salary and I minus things airport minus year. And I'm taking this particular grossly roll from the left in place here. If I goto gross salary here, 5140 has been deleted. So what is the difference between these 24? And now what is the order of parents off employees which are already there between December and January, which is $45. So for goto parents employees sheet here and I go to gross salary e There is a $45 difference between salaries off this simply what existing in December and January. So if you go back to rush for and if he sum all this up we'll have a 41 BD which is exactly matching here. So this way or analysis is accurate saying that you can compare each and every attribute off the spaceship like this and that should be zero here. So this true number should always match is a compressor where you can analyze your patient and you won't go for that analysts and investigate the reasons this is happening to see your profitability impact from your payroll itself. So if you're in a service industry where your employees are your assets and those are the one you are earning money from. And this is analysis you should do always each month. 20. Build Insightful Payroll Analysis Report with CDW-4: now it is next section of dashboard were trying to compare every century. So our salary for each division and also for each segment off salary like crossed salary own Baia Charlie And here also get return a very smart formula where, for example, this particular amount 1 55.39 is taking the Charlie earn for Allah for division for only those employees which has been paid. So the count formalized minute on very smartly. Very goto Last month table toe this a charge on column. As you can see it actually own column here is gonna count only the values which are not zero. So if you remember, we lowered this formula Look at zero if there is no value So these are access, Rose. If you put normal counterfeit always count this particular zeros as well. But we don't want to count that We were only want to count Achara paid to employees so he won't only count this particular values a number of rows. So these are employers to whom the actual has been paid out that also division wise Soto do their year to write this kind off count dysfunction. Where gonna count only the valley region. Article 20 So you can do like this, So let me zoom a bit. So count this entire road the criteria range and there count only which values which are not equal to zero. So non zero values to be countered. So here, this particular values 17. But this is where entire data set. But here in the dashboard, we have put one more condition. So second clarity raise their divisions were taking department range and counting only for the all four division. So if I take the value for this particular formula, were pressing f night. They're only fire employees. So part employees. Acharya is 1 55.39 exact. Same way we have done for all the divisions for each payroll element. So we're only averaging them place to whom this particular hrs paid in the genital region. In the camera division, we are only averaging employees to whom the basic on pay has been paid similarly for ah, this month. That is for the last one. This Monday, after we're done. Same. But in this particular scenario, we are taking this one table as a cart area. So this is this month is a call to January 19 and last one is a quarto December 2018. Accordingly, the average pay for department wise and also element wise we are comparing here, so total really were different, very attacking toward totals. So in this total, we are counting entire cost salario on pay, whatever the element is and the word by number off employees to whom this on president paid . But here all in place will come not department wise. The county function will ever one less argument. Only one car Taylor injuries and tar basic salary and the employees to home. It is fate. So we get average pay for entire month for each element for all the employees. Respect your department in this total column and for the rest as explained for East Division Ways. Seeing the father January this month and now we're comparing the analysis for the any reliance is about plus or minus five person. So in this scale, other allowance for Gina de Region has reduced from December to January and were to know why. So if it was 54 it has reduced by 10% so we need almost 11% I can say that and we need to invest to get one in Dagestan region. Also, the average on pay has reduced so we can investigate. And here in this scenario, is the rice is not that material. So it's not that significant. Anything less than O plus 5% is not significant here. That's their conditional formatting have given here. If you go here and check the rules and I'm selecting these values in the cell here, let me show you that this true values the most important factor here is, or tanks in any men power industry, where the payroll is their main cost. And basically these industries are service industries, or time is a main factor. And all industry wants to reduce their or time because, as usual over time has always paid higher than the normal rate power. So this analysis done accordingly. So here we are comparing all four divisions or normal over time, which is 1.25 in this scenario and number off employers to whom it has been paid for that divisions, which is 26 point zero and in the this month it's 30. So that is 18 person increase in average parent play over time, which is alarming thing. And we need toe investigate why it happened. And I would also this if ever functions in case there is a zero value, there would be a division error. As you know, Excel shows division other when you're trying to divide anything by zero, so are put a feller function here in this scenario. So that is division adventuring zero. There is no value here now. 400 overtime for gamma division, it has reduced by 11%. So we need to know why production is always good. But then again, we need to check why it is happening. The only reason can be that holidays were more in December, then January so that cities and holiday over time is lesser. In January compared to December same. We're not salaries also being, ah you and formula and we're calculating the alliance's accordingly can do a very companies even detail payroll analysis each element wise spot employees for ah, various divisions as well. So I'm gonna be providing you this as a template so you can go through the formulas and learn Apply the same concept for your own payroll analysis now in the last segment here, I'm trying to compare salaries. Or also, this is a very simple version here. I'm just taking the entire A direct strafing direct star friend Total. So entire Daleks stuff for all four division. I'm taking salary for the December. That is last month. So 10 $7000 for all 40 vision. What was the attic stuff? Net pay. So I'm only comparing that way here. And this was in direct start parents. And really, for this one same scenario and the references I can investigate anything. Obor less than 5%. So here it looks alarming Where indirect stop salary has gone down on This is Ah, very typical case where some employees has gone for a vacation and we didn't charge facility toe the normal payroll. It was paid release, elated provision. So their account any for dealing with payroll. You would understand this particular scenario where the it looks very high presented but the amount itself is not material or not significant. So this is how you do a comprehensive analysis. This is the one off the example and it's very simple. Form off for Pedro analysis. There are many other analysis you can do, We can create people tables and you can do visualization as well. But I want trying to show you the actual power off competitor workbook, and this kindof analysis can we don't very easily. 21. Build Insightful Payroll Analysis Report with CDW-5: this solution is completely the enemy cannot show you with an example. Now, as I mentioned, we are compared December 18 was a January 19 payroll. Now we want to continue this analysis each month. So now I want to compare from January to February. So how you can do that? So it's simple. I goto this man sheet. The main sheet for struggle are goto this man sheet and this day ties belongs to January 19 and I want toe bring this debt out with the old shit right, because we're comparing month by month. So January data now becomes last one data since this Monday that would be February did all right. So simple ways to just to copy like this and based trip. Or you can do one thing. You can clear their data before you do that. So let's do that. So I'll clear the tie here and now let's bring in the January later like this, you can taste it and just ensure that there are no data rose beyond this by 63 rows. So there's a completed as that for January. Now will be this one. There is all data, so let's change it toe January to the other 19 in this sandy one like this and this one. But I would be February. There's a formula given here in Self e one, which takes the formula for end of the month and takes the cell value off even and adds one month week. So we get automatically February and I'll goto this month. And now again here you can clear their data. Let's do that first. Now, in this month. Genital being in pro Tory patient did up. So I have captured ready here on Let's they can target assert that there are total 29 columns. As I told you, we must have the same column structure. Let's copied on Goto this month, which is probably very data on base special. Now the former thing has changed cause they're deleted there. Oh, but that's fine, because we're gonna be using the table, which is linked to the state of this month. This one table will be using. So let's go down and change the formatting off this state. Go to wear and shit, and it's clear that she did up to make sure that they're Dionysus is correct. And let's click the compare shape, as you can see. And if you go to new Rose, did Rose, we have to copy. Still, the state of So the knowledge is is complete. The new data has been pasted in this three sheets at a drawer so knew their employees came in in February compared to January to simply have left for the corn for vacation. Mystical it is and place were there in January, but not in February. And this other change. So the cell level changes for the simply So this. Employees are existing in both January and February, but cannot changes in their payment in all these different elements. Withdrawn this varun sheet and let's check our table nor table a generated some errors, which is fine because the data were deleted. You just had to come here and based Formula down like this, and it's gone and you to ensure that everything is okay. So 5 16 employees remember we had only 5 61 employees in our period analysis. Similarly, come here, and the first true is there. There's have Arabs because those throws were deleted. That's finally just have toe corporate this formula down like this since is a table acceptable copping formula down Israelis in it is more consistent and accurate. Seen before the Iran unstable so number of employees knew. Or 43? Yeah. As you can see, deluded employees are checked the number 39 Reliance employees. Also, we can check quickly. So for 22 employees again for the alliance, let's go dashboard. And in ideal scenario, the dashboard should give us analysis without any adults, Any validation. And now let's go to dashboard and see Far February that I was a generator journalist is okay. In all, the renovations are also okay and analytics are fine. We'll add a check. Okay. Its texture some off all these zeros. And if zero r some value like $5 which is Ah, not significant Very on. So I'm keeping a tolerance off five year. So there is fine. And because the total Alanis his old sort of change, we have broad jan and frappe and start off December and German. So this is linked with this particular sheet. So once you changes value changes everywhere. So if you change this toe whatever month and you want, it also changes here with the formula and off the month, and that has been linked here as well. Similarly, all the analysis have been updated, and now you can pass comment to investigate more. What really happened, especially this Ah, France's aboard tolerance level off plus minus 5% that you can investigate and we could report. So this is the way you can use company of a book effectively. Only problem we had face was that when we copied the data, the new turn deleted the rose. This formula was 1/2 hour because the drawers were militated for meeting that four stories . They were being deleted. And you press this button clear sheet. The first Roy's. They were really, really just clearing the contained. So it always there. So that helps in taking your formula down like this, which it takes only a few clicks and also a few seconds, depending upon how larger atrocities. So just drag it down entire formula. So we just select the role like this and just drag it down, and it will work just fine. So once you do that in this re sheet everywhere else, it will just create the formulas accordingly. So only caution is that you need to attract the formula down from euro First in this last one table this month table and we're instrument. Apart from that, everything is company dynamic. Your dashboard will be uprooted just to ensure that these validation errors are under your tolerance. Whatever you said, the front zeros again. 40 How kept it five because five is okay. It won't change the order perspective off this analysis. So this is how you use computer workbook effectively. This is the one example off payroll and you can compare any kind off like stock market is were seen in the previous example where I compared a brief portfolio. That approach was quite simple. I just took the same sheet as variants ended analysis. But this kind off structure approaches far better when you're gonna do this kind off analysis were ants analysis of payroll every month. So thank you very much for watching if there any questions or clarification, please leave them in the kuna section. And also, as I promise, I will leave this particular that I said including the February later said the separate asset in the resources section and you can play around this particular analysis apply the same to your own solution or your own announces. And also you can see the formula, this everything here. 22. Important Facts about using Compare Data Workbook: Let's see some important facts about competent a look book. We're the best feature of comparative workbook exes ability to compare cell level, whereas is between two day triceps and sheets. It's quite easy to set up and use, and also the mortification in company that workbook Us are also relatively simple. The most important feature of this Excel Baby Comparator workbook it visibly to find out cell level references while comparing similar structure data as explaining totally Lecture . Excel has other features, like formulas and functions. Namely, we Look Up Index and match functions are photos like Power Kredi. We just have to compare toe data set. But to find out sell alliances between the common Rosa records is only possible with VB, according and that treaties were book shines the most. This workbook is easy to set up, which is not that difficult to set up and use. If your music to Individual Excel user, you can easily set up your only Newt are following the instruction and perform Iran's that Alice is with a few clicks. Importance. I applauded complaints. Example off payroll that Alice is between the two months imperious lectures. If you follow similar approaching setting up your data set, you should be able to achieve a truly dynamic solution, which can be easily used each month or vertically. If you're any kind of experience with, except maybe even with the most basic level, you can easily follow the instruction prodded within the core models to make required changes in the court properties. This is important when you like to change all the time machine names to the something else and many other changes, like the tags off numerator. You can also change that with easy to follow guidance, as applauded in this entire course, and also in the instruction off this workbook. Let's see some limitations up Discomfort of a book Dis company of will not be the most ideal solution for the journalists. If you're dealing with large intersex, for example, if you're inter surgeons into millions of pros and many columns, this world will will be super slow in comparing this loud. That asset so ideal approach would be to use some other tools or some other soft Frederick competitors. Sex in case earlier times lily large. You should be familiar with Microsoft Excel See ecosystem, that is. You should be able to narrate, Excel, workbook and know the basics off formulas and functions. My leg said, Maybe a knowledge is not complicity for the course and using off this workbook, but if you're familiar with Baby according and Excel at the basic level, it will help you in modifying the world book according to your requirements. 23. Thank you and Class conclusion: This is the end of the journey. I hope that you enjoyed this course and got familiar with amazing analysis capability off this excellent. Excellent. Be a competitor. Work. It has taken lot off our forks in eloping. This workbook and the baby accord itself was not easy to crack for that is our result. But I kept on working on the same and was able to do all of the solution. I hope this workbook will save you time and Air Force in performing tedious Datanalisis for any kind of medicine I'm gonna developing. Other excellent power query based courses were seen the grim So Microsoft Power tool called Parker in the scores. And this tool has been my favorite for their transformation and cleansing as promise. Are we coming up with a full power criticos in near future? Is there any question or difficult in using this competitive Abu, please reach out to me through your intersection. Thank you once again. And good luck. We thought it on on icis