---
title: 'AI-Powered Business Analytics Full Course 2027 | Learn Excel, Data Analysis & AI Tools | Simplilearn'
source: 'https://youtube.com/watch?v=wjj1B9BaRBg'
video_id: 'wjj1B9BaRBg'
date: 2026-11-14
duration_sec: 21491
channel: 'Simplilearn'
---

# AI-Powered Business Analytics Full Course 2027 | Learn Excel, Data Analysis & AI Tools | Simplilearn

> Source: [AI-Powered Business Analytics Full Course 2027 | Learn Excel, Data Analysis & AI Tools | Simplilearn](https://youtube.com/watch?v=wjj1B9BaRBg)

## Summary

This video is a comprehensive tutorial on AI-powered business analytics using Microsoft Excel, presented by Simplilearn. It covers the entire analytical workflow, from foundational spreadsheet concepts to advanced data cleaning, formatting, and visualization techniques, emphasizing how Excel's AI capabilities enhance business decision-making.

### Key Points

- **The Power of Combining Excel and AI** [00:22] — The video opens by highlighting the challenge of analyzing thousands of rows of sales data and the potential of combining Excel with AI to speed up analysis, identify patterns, and turn spreadsheets into meaningful business insights.
- **Interactive Quiz: Summarizing Data** [03:41] — The instructor poses a quiz question: 'Which feature in Microsoft Excel is primarily used to summarize and analyze large data sets by grouping and aggregating information?' The options are conditional formatting, pivot tables, data validation, and find. The answer is pivot tables.
- **Business Analytics Definition and Applications** [09:03] — Business analytics is defined as the skills, technologies, and practices used to analyze historical data to investigate performance, consumers, and competitors. Applications include sports analytics (ticket pricing, team selection), finance, government demographic analysis, and customer behavior analysis.
- **Real-World Example: Tire Defect Analysis** [11:53] — A detailed example is provided where an operations head must analyze defects in tire production. The process involves four steps: 1) Collect data from all plants, 2) Create reports (frequent defects, plant-wise, process-wise), 3) Find root causes (why), and 4) Propose and test solutions (cost vs. effect).
- **The Four Types of Business Analytics** [20:47] — The four types are: Descriptive (what happened), Diagnostic (why it happened), Predictive (what will happen), and Prescriptive (what should be done). These form a cyclical process, with predictive analytics involving machine learning and prescriptive involving test runs and real-time tracking.
- **Detailed Look at Each Analytics Type** [23:32] — Descriptive analytics answers questions like 'what are customers interested in?' Diagnostic analytics uses correlations and hypothesis testing to find causes. Predictive analytics uses statistics and machine learning to forecast outcomes (e.g., Starbucks footfall). Prescriptive analytics simulates solutions to optimize decision-making.
- **Risk Analytics: Quantitative vs. Qualitative** [31:38] — Risk analytics deals with uncertainty. Quantitative risks (e.g., loan default) can be numerically assessed and mitigated. Qualitative risks (e.g., public behavior, patient health outcomes) cannot be quantified and are harder to mitigate, as illustrated by examples like Tata Nano and local protests against plants.
- **Excel Interface Basics: Quick Access Toolbar** [37:56] — The instructor demonstrates how to open Excel by right-clicking in a folder to ensure proper file naming and location. The Quick Access Toolbar is introduced, and users are shown how to customize it by adding frequently used tools like 'Fill Color' and 'Format Painter' via right-click.
- **Understanding the Ribbon and Tabs** [43:17] — The ribbon is explained as the main toolbar with tabs (Home, Insert, etc.). The instructor shows how to collapse and expand the ribbon by double-clicking on a tab, which is useful for maximizing workspace.
- **Name Box, Formula Bar, and Cell Basics** [50:47] — The Name Box shows the cell address (e.g., J3). The Formula Bar displays the true cell value (e.g., full decimal number), while the cell shows the formatted result. The instructor emphasizes that formatting does not change the underlying value.
- **Excel's Data Limits and Performance** [52:11] — A single Excel sheet contains over 1 million rows and 16,384 columns. Selecting entire columns for operations like VLOOKUP can cause file lag and large file sizes. The instructor advises against applying formats to entire columns to avoid performance issues.
- **Data Entry Best Practices** [58:12] — Use Ctrl+Enter to stay in the same cell after entering data. Numbers and dates are right-aligned by default, while text is left-aligned. Aligning everything to the center is discouraged as it hides the distinction between text and numbers, which can lead to calculation errors.
- **Fill Series and AutoFill Options** [01:02:15] — The instructor demonstrates how to use the fill handle to create series. The 'Fill Series' dialog box allows specifying start value, step value, and stop value for linear or growth progressions. This is useful for creating sequences like odd/even numbers.
- **Working with Dates: Today Function and Shortcuts** [01:13:50] — The TODAY() function returns a dynamic date that updates, while Ctrl+; inserts a static date stamp. The instructor explains the date series concept: Excel counts days since January 1, 1900, and this number is what Excel uses for calculations.
- **Data Cleaning: Text to Columns for Dates** [01:39:39] — When dates are not recognized (left-aligned, text filters), the Text to Columns feature can be used to convert them. The process involves selecting the data, choosing 'Fixed Width' (to avoid splitting), and then specifying the date format (e.g., YMD) in the final step.
- **Custom Number Formatting for Dates** [01:55:54] — Custom date formats can be created using codes like 'd' for day, 'm' for month, 'y' for year. For example, 'dddd, mmmm dd, yyyy' displays the full weekday and month name. This is a facade; the underlying date series remains unchanged.
- **Custom Number Formatting for Thousands (K)** [02:01:31] — To display numbers in thousands (e.g., 1.5K), use a custom format like '#,##0.00,'K'. The comma acts as a thousand separator, and the 'K' is a literal character. This makes large numbers easier to read in reports.
- **Sorting and Multi-Level Sorting** [02:14:17] — Sorting is a fundamental first step. Multi-level sorting allows sorting by multiple columns (e.g., Category, then Product Name). The instructor emphasizes selecting a single cell within the data range to sort the entire table correctly, avoiding data corruption.
- **Filtering and Advanced Filter Options** [02:22:35] — Filters can be applied using Ctrl+Shift+L or the Alt key series (Alt, A, T). The instructor demonstrates number filters (above average), date filters (February), and text filters (contains 'laptop'). The status bar shows the count of filtered records.
- **Grouping and Outlining for Data Management** [02:32:12] — The Group feature (Data tab > Outline) allows collapsing/expanding rows or columns to create different report views. This is a better alternative to hiding columns because it provides clear indicators (1, 2, 3) of what is hidden.
- **Excel Table vs. Range Table** [02:52:45] — An Excel Table (Ctrl+T) offers automatic formatting, filters, and structured references, but it has limitations (e.g., VLOOKUP doesn't work). The instructor recommends using it for formatting and then converting back to a range to avoid these limitations.
- **Subtotal Feature for Aggregated Reports** [03:02:44] — The Subtotal feature (Data tab > Outline) automatically inserts subtotal rows based on a sorted column. For example, it can sum revenue by product category. The prerequisite is that the data must be sorted by the grouping column.
- **Copying Only Visible Cells** [03:21:21] — When copying a subtotal report, use Alt+; (semicolon) to select only visible cells, then copy and paste. This avoids copying hidden rows. The 'Go To Special' feature (Ctrl+G) also allows selecting visible cells.
- **Conditional Formatting Basics** [03:28:21] — Conditional formatting allows formatting cells based on rules. Examples include highlighting cells below average, greater than a value, or duplicate values. The formatting updates dynamically when data changes.
- **Data Cleaning Tools: Text to Columns, Remove Duplicates, Data Validation** [03:41:12] — Text to Columns splits data based on delimiters (comma, tab) or fixed width. Remove Duplicates deletes duplicate rows based on selected columns. Data Validation sets rules for cell entries, including creating dropdown lists.
- **Creating Dropdown Lists with Data Validation** [04:46:52] — Dropdown lists are created using Data Validation > List. The source can be a range of cells or manually typed values. This is essential for making functions dynamic and ensuring data consistency.
- **Conditional Formatting: Data Bars, Color Scales, Icon Sets** [05:07:15] — Data bars visualize values within cells. Color scales create heat maps (e.g., red for low, green for high). Icon sets can show arrows or circles based on thresholds. These are useful for quick pattern recognition in large datasets.
- **Highlighting Entire Rows with Formulas** [05:21:44] — To highlight an entire row based on a condition (e.g., salesperson name), use a formula in Conditional Formatting. The key is to use absolute referencing (e.g., $C$19) so the condition applies to the entire row.
- **Cell Referencing: Relative vs. Absolute** [05:29:07] — Relative referencing (e.g., C31) changes when copied. Absolute referencing (e.g., $C$31) fixes the cell. This is crucial for creating robust formulas and conditional formatting rules.
- **Text Functions for Data Cleaning** [05:44:05] — TRIM removes extra spaces, LEN counts characters, UPPER/LOWER/PROPER change case, and CLEAN removes non-printable characters. These are often used as nested functions to clean data before analysis.
- **Introduction to Logical Functions** [05:54:39] — Logical functions like IF are used to return specific outputs based on conditions. For example, IF(profit > 3000, 5%, 3%) can be used to calculate bonuses. This replaces the need to manually interpret TRUE/FALSE results.

## Transcript

working with thousands of rows of sales data in Excel. Your manager asks which products are generating the highest revenue, why sales dropped last quarter, and which customers are contributing to most business growth. You have the data,
writing formulas along with creating reports can take hours. Now, what if you could combine the power of Microsoft Excel with artificial intelligence to analyze data faster, identify patterns,
spreadsheets into meaningful business insights? That's exactly where AI-powered business analytics with Excel comes into picture. Hey everyone, welcome to this AI-powered business analytics with Excel by Simply Learn. In
environment, organizations need more than just spreadsheets filled with numbers. They need professionals who can interpret information, recognize trends, and support smarter business decisions. Microsoft Excel remains widely used for
business analysis and AI-powered capabilities which makes everyday analytical workflows more efficient. In this course, we will explore how Excel can be used to organize, clean, analyze, and visualize business data. You will
understand essential spreadsheet concepts including formulas, functions, sorting, filtering, conditional formatting, and data summarization. We'll also have a look at tools such as pivot tables, charts, and dashboards to
reports that are easier to understand get interesting. We'll explore how generative AI can assist with creating Excel formulas, explaining complex calculations, suggesting analytical
approaches, and helping users investigate business questions using this course, we will connect these concepts practical scenarios, such as tracking sales performance, analyzing customer behavior, comparing business
expenses, and identifying operational trends. Let's also discuss why checking calculations are essential for accurate business reporting. So whether you're an aspiring business analyst, an Excel user looking to improve productivity, or a
professional interested in combining AI with everyday data analysis, this course will help you build practical analytical skills. So get ready to discover how business analysis more efficient and insightful. Having said that, if these
like to watch, then hit that subscribe button along with the bell icon to get notified whenever we upload a new video. Before we get started, if you're looking to build a career in data analytics, here's something worth checking out. The
with Microsoft is designed to help you go beyond simply working with data and business insights. Through live expert-led sessions, hands-on projects, and a capstone project, you will build practical skills by solving real-world
business problems rather than just learning concepts in isolation. The program also includes Microsoft aligned learning with dedicated Microsoft certificates that add value to your learning journey. You will also work on
practical use cases involving dashboards, customer behavior, sales analysis, and more, helping you understand how data analytics is actually applied across industries. So if you want to build job-ready
experience working with real business scenarios, check out the link in the comment. Which feature in Microsoft Excel is primarily used to summarize and analyze large data sets by grouping and aggregating information? Is it A,
conditional formatting, B, pivot tables, C, data validation, or is it D, find and comment section below. Let's get started with this. &gt;&gt; So first thing is that by starting your journey here with business analytics and
Excel? As we all know, we all are working professionals, most of us are, right? So Excel is a powerful spreadsheet software. There are more softwares out there. We have same as
Microsoft Office. We have got Libre Office. We have open office tools. We do So there are multiple softwares out there that are basically office tools. So all are here office tools. Within
these office tools we have a spreadsheet softwares which is Excel with Microsoft. Now here Microsoft Office is widely used across the organizations and that's why we are learning Excel here. It is extensively used for data report
management. We also use it for making multiple reports. It is also for educational purposes and when we say we are trying to create reporting out of that we are basically
analyzing it. So Excel is used for business analysis, reporting, data storage and management and for educational purposes. Thus Excel is the basic and the foundation of every data table that we
are going to see. Afterwards if you decide that you would like to go ahead and maybe learn Looker, maybe you want to learn Salesforce, Tableau or Power BI, Click View, Click Sense, there are multiple softwares out there.
If you want to learn any of these softwares, the foundation is Excel. If management works, you know how to clean the data, how to prep the data in Excel, you can do that everywhere. Even when you move forward you learn Python or you
learn R or you learn any other language out there, you will have a very good foundation if you learn how to do data analysis, reporting, creating the charts, visualization within Excel. Excel has a very flat learning curve. It
is easy to learn. Excel also has a statistical analytical capabilities. It provides multiple tools for that too and this is a tool of the choice whenever we are starting your journey new
are going to go ahead and explore Excel. Now within Excel what we are going to go with the help of the Excel we will be to go ahead and learn how to go create, how to use functions to create reports because we're learning it for business
analysis. You will do the analysis only when you are collecting the data set. So have to prepare the data set, you should know how a table is created. Now you prepare the data set, clean the data set, then for analyzing it you are
can be created with simple sort and filter. Reports can be created by using multiple functions. Reports can also be created with pivot. And once you create the report, we will explore all the methods and once we have
the report, we'll go ahead and create charts out of that. After creating the and read the chart, We will also learn how do we get data from other sources within Excel, what
are the possibilities there, how much data Excel can handle. We'll also see few automation tips and tricks within Excel that is done by VBA and macros. So this all will make make exploring the data quite easy.
Okay. This is what we are going to learn. and preparation. Okay, that means we are talking about sort and filter here. Sort, filter, group by and removing duplicates, text to columns and lot
we have understood about data cleaning and preparation techniques and features available in Excel, we'll move forward with conditional formatting meaning creating reports out of functions here.
Within these functions, we are going to learn about date functions, aggregate functions, text functions and lookup and reference functions. how do we start reporting with pivot tables.
learn about charts too and we'll see how to create an interactive dashboard by using Excel slicer and combo boxes. Then we will go ahead and learn about In analytics with Excel, we will learn about statistical analysis and
present in Excel. After looking at that, we will go through the automation here that is macros and we will also go ahead and see how we can go ahead and import data from every
from other data sources by using Power Query. So this is the journey that we are going to have in next eight sessions. ahead and learn about the course and project and how you're supposed to
So this is the whole journey we are going to go with everyone within the Excel course. Now how we are going to go ahead with Now how we are going to go ahead with this journey and what are we
and the practice. Today's agenda, first we are going to go ahead and discuss about business analytics with Excel. So first we discuss business analytics, first we discuss business analytics, what is it and where it can be applied.
Then we will go ahead with the basics of Excel interface. here, we will learn about multiple versions and fundamentals here like fill series. After that, we will move on for sort, filter and formatting. So this is
move forward. This is what business intelligence is and just one of the environment is business analytics. So when we talk about analytics here, okay, it's a pure scientific research methodology. It's a scientific process,
okay. It's a statistical process here that will help us to examine the data and of course so that we can go ahead and draw out meaningful conclusions for in which areas we can apply that analytics? In the sports analytics.
Finance, we already do. We understand how our business is performing. Even the government uses the demographic and geographical analysis or the sentiment analysis of the crowd.
area of the sports here, a sports analytics, what we are analyzing there? We're analyzing that if we go ahead and come up with a tournament in particular city in a stadium, how many people are we expecting to buy that ticket and
based on that we will go ahead and do the analysis and figure out the price of the ticket that this should be the ticket price and how much it should go and where we should price what how much are we
expecting there and within the team okay who is going to be there in the team that is also decided by analytics not only their own decided by analytics not only their own measurements of our matrix as per their
performances okay even the matrix of their public image and their brand image their public image and their brand image that is also that gets calculated and on basis of that they can go ahead decide the ticket prices and more.
So this is the sports analytics okay and in multiple areas in these business analytics, data analytics can be applied. And it often involves okay something has happened that's that's only when the data can be collected.
we have taken a certain decision in the past how it has turned out and it will help us to evaluate the performance of the businesses. So this is the proper definition here
So business analytics is it refers to the skills, technologies, applications, practices. So all the tools, technology and statistical practices where we get to analyze historical data
and to investigate performance, consumers, competitors and more. This is business analytics everyone. So let's say that you are the operations head of a multinational tire company and
you have been asked to submit a detailed analysis of defects, detailed analysis of defect that occurred during tire production that occurred during tire production at various manufacturing plants worldwide.
And whenever the occurring okay they go ahead and they save the defect in the codes, for example DF1, DF4 and likewise. So they are saving with a pre-defined defect code, every defect.
the defect log includes variables such as operator name, machine, plant name, as operator name, machine, plant name, defect code and more, right? operations head, what is going to be your step one?
What is going to be your step one? So you you have to figure out that in this particular multiple manufacturing plants, multiple production lines are and you have to figure out why the defects are happening.
So what is your step number one? What kind of questions you would like to ask to the data set? Or let's say what kind of reports will to understand and submit the analysis for the defects.
What will be your step number one? Collecting the data from all the most often occurring defect, collecting data from all the centers, machine time, name of the machine, machine time,
gathering the data, types of defects. So the first step that collect data from all the manufacturing plants. After collecting the data here, the step two is creating multiple
reports out from there, right? What kind of reports are we talking about? So first that I'm seeing here is the frequent defect occurring, right? So what which is the defect that occurs frequently? How many years of data you
want? So while collecting the data, the decision has to be made that on the basis how many years of data you want in the analysis and then you collect only that individual plant is having one which of the frequent defects appear in
which particular plant? Real-time defects during manufacturing. the process in the whole process in the whole process, okay, which step or which exact machine part or the process is causing the defect and which
defect occurs? Which defect occurs Which defect occurs during that particular process. multiple reports. Okay, we can see in which plant which
timings since it's a manufacturing You can go ahead and ask for shift timings and you can figure out that which particular shift will have more defects as compared to other shifts.
You can also go ahead and collect more data. Yes, we go for root cause analysis after that. Once we understand what is happening, okay, now we have to go ahead in. So to understand why here you might go
ahead and take the shift timings. And you get the shift timings data, you figure that out. You go ahead and collect the machine maintenance data been maintained.
So you get their their maintenance data. You go to finance department and ask you may go to the operations for each plant and ask their maintenance data. When was the last maintenance was done for the machines
or when they have been updated. Because you're trying to figure out why, you're trying to figure out which defect occurs at which particular machine part or inside which process. You can also go ahead and ask for the
ask for the quality team that are they going ahead and checking is coming in. Have they found any discrepancy in there so far? Okay, you can go ahead and check the logistics teams there that while transfer of
okay, are we having some defects while packaging and while getting the raw materials there? So there are multiple there are multiple places where we can go ahead and look for the why. Once you will go look for the why it
means the cause here, what is your next step? Now we have figured out cause. figured out the cause? Let's say the cause here is incompetent workforce. They have been hired new most of the places because of the high employee
So we have figured out why. Let's say and when the why we have figured out that in few plants okay employee attrition was higher. You do some policies and now they are totally new people are there but they
are not trained properly due to the high demand. defects. So we you figured out with that for some Now what will you do once you got once you have the cause?
What's your step number three? How to fix it? We solve it. That means solutions. Right? We find solutions and see the Meaning we have to go ahead and find multiple solutions.
We have to go ahead and find multiple solutions for the same set of problems. and the causes we found multiple solutions. After finding multiple solutions we have to go ahead and see cause and effect.
Meaning what is what do I mean by cause and effect here that if I go ahead and implement this solution how much it is going to cost? So it is exactly not cause and effect it is cost and effect.
effect. So if there is a solution for example we go ahead and we apply uh give them training there okay give them all training.
cost? And after they are trained after they revenue? [clears throat] What is the effect on reducing the number of defect and how does that increases my revenue? Or how does that increases the sales
value? Okay so you have to go ahead and make sure that everything can be quantified in numbers or in the monetary Right? So you will go ahead find multiple solutions you will also see
what would what will be the cost to implement that solution and after implementation of the solution what will be the probable effect? What will be the probable effect? When I say probable effect of course we have to go ahead and
happen? Yeah we are only why we are here to suggest a solution, right? We are not going to take the decision. So once you have multiple solutions here, you go ahead and you present this
multiple solutions, okay, to BPMs. They will go ahead and they decide on few. They might go ahead and decide on few or one of the solutions to be presented. However, they will not go ahead and
implement it to the all the manufacturing plants, absolutely not. They will pick up a few people to train, all right, from few plants only. So they are going to do a beta test, that is test run.
with that particular solution. After doing the test run, if it actually goes ahead and proves that yes, this is good solution, right? How does it going to prove? So they do a test run and they follow the whole cycle of analysis
analysis again. They do the exact same analysis again. And then they will compare that without training what how many number of defects were there, with the training now what is the quality now.
They will compare both the analysis and right pilot test run. right pilot test run. If that is successful, then they go ahead and they apply to the whole all the plants there.
there, again the data is collected, again the reports are created, again or not. So it's a whole cyclical process. This is how it go. But it is basically divided in these four steps.
solution. That's why I said for a few of that the employees are not doing well. Maybe for few they figured out the For other maybe they figured out that quality was not working well.
it is not going there is not not never going to be a blanket solution for all. solution for a specific problem. Instead, they have to go ahead and create clusters of the plants. And to
of the people there within the departments, okay, they will go ahead Because it is costly for them to go ahead, go to each and every problem And it all depends on organization to organization too. So these are the four
steps, right? So based on these four steps that we have simply figured out while talking to each other, right? So that makes the here that makes the types of business
That makes the four types of business analytics. First we figure out happening. Exploratory data analysis, describing After describing what has happened, we go ahead and we find out why is it
happening. This is what we we did in the second step here. Once we figured out, okay, why is it happening, we went ahead and we tried to happening, we went ahead and we tried to depict that what will happen if I will
go ahead and apply some solutions. So here we were applying the solutions, we that what would happen if we apply the solution, how much it is going to cost us, how much cost are we going to save. And then after predictive here, we go
ahead with prescriptive. Prescriptive is our test run. What is prescriptive here? Prescriptive here is your test run. If a ahead implement to everyone wherever it is applicable and the cycle
continues. There are four distinct types of analytics, descriptive, diagnostic, predictive and prescriptive. predictive analytics includes your machine learning, it includes your
predictive modeling. Prescriptive here, it includes the same versions, right? But it goes in more into the live streaming and tracking your data in real time. So that's
prescriptive, you're prescribing something to the solution. You're implementing that solution. And while implementing the solution, we are taking let's see what how the things are going on.
and please remember that, okay this is all a cycle. You understand your business, you understand your data set first. After they understand you understand
your issue, problem. You understand your data, how what data you need when. technically with your team of data analytics and then you go ahead and explore the data, you model your data, you follow all the four
steps of analysis. Once the solution is validated, right? everything else is done, then you go a dashboard or you live stream it on your server so
you simply go ahead present it in a seminar that this is the project we implemented and this is the cost we have saved and a lot So this is all about analytics everyone
and yes this is how we move forward and we optimize the performance of a business. Okay so here let's go ahead and learn can be applied. When you talk about descriptive here,
analytics. It simply create the small reports and we explore that what is happening within the business right now. It can answer questions like what the customers are most interested in, what which is the segment of a buyers that we
are currently dealing within such and in a city, which are the high value customers and which characteristics make them different? Or maybe we want to go ahead and analyze them demographically or geographically.
So these are the questions that can be answered within descriptive analytics. If you go into diagnosis, we learn about why things happening, right? It means we between some scenarios. So for example, if I talking about tire
manufacturing right now, so there is a defect one. So defect one is getting caused by which one of the processes? So if you are the process goes, you will have an answer to that
done properly, was not maintained at this particular step, or the pressure that has been there has been it should be within this particular range, but the range is crossing in a certain certain step. So
you have to go ahead and create multiple correlations. You also have to go ahead and prove the cause and effect using your hypothesis testing and the models. So this is the diagnostic analysis here.
It can answer us that why defect is happening, which defect is happening at which step, which a step, why the customers are not subscribing here, why they are not coming back for subscription, what are the specific
variables that are basically impacting our output. So if some particular product is not popular, let's figure out why it's not popular, why people are not back. So this is diagnostic here. Then we have
predictive. Predictive here is pure pure statistics, right? This is where the data modeling, machine learning techniques are going to be applied. So here the statistics start with EDA right? We we go ahead and figure out
outliers, anomalies, we see the null values, how to treat the null values, we clean the data set accordingly. We will move forward and to predictive. I have a solution here, right? I want to go ahead and analyze
how my solution impacts the output. It means I want to change some variables inside that and want to see how my output is going to react. In that case, yes, we need machine learning models. It starts very simple
It move on to multilinear regression, then it moves on to the more. So this is the whole track that you are going to see as we move forward. usually it was used in clinical data
business too. And we try try to predict that how example, if I want to open a Starbucks in a tier one city in a certain outskirts of tier one city, how much customer footfall it is going to gather
and what quality of crowd is going to there in that a Starbucks. based on the geographical and the demographics there. Maybe I want to go ahead and change the look and color or the shape of a shampoo
And we want to go ahead and figure out that how is that going to work? to that? And what are the potential values for each customer and the prospect that they are bringing in? So like this multiple
creating a model out of it. That's predictive analytics. So we can go prescriptive here is and improves the decision making. It is like the simulation or the test run
solution to it. So if you have multiple solutions, you pick few, you apply some into the test run which works better. You go ahead and then you apply it to You go ahead and then you apply it to the whole cluster.
analytics, more descriptive and analytics, more descriptive and predictive. something, right? We are always we are always in the probability mode.
am not I cannot be ascertained that okay, if I go ahead and I will give them a training that costs $50,000. Okay, I give them very expensive training for $50,000 and I'm certain that it will go ahead and increase the
revenue by $100,000. I cannot pinpoint that one single number. Now I will give number to this number it we might have the probability is there then we will go ahead and decrease that
number of defect and that will increase by this much percentage probability. We will increase our revenue. You never give one single number. that too with the probability. So for example, vaccine people come out and
they say our vaccine is 84% effective. Right? It's a probability. It's probability that there is a chances, okay, just pure chances that it works for the 84% of the times. If your organization has the data you're
There may be if you're doing a competitive research, you will go ahead to the companies that do the market research for you and gather the data for you. You simply buy the data from them.
So nowadays market research is done by the companies and you simply buy the And every application out there is collecting your data set. They have your data. Khushi we are going to learn Excel in
So whatever is in Excel, okay? So Excel can work from hypothesis testing to only one simple regression analysis Excel. Beyond that, that's the maximum Excel
can go in predictive analytics only till simple linear regression. we have already talked about it, okay? Finance, performance, it can be employee performance, business performance. Customer analysis is the big one right
And of course the risk analytics is there too. And there are more examples as one. There is market segmentation, right? And consumer behavior analysis, market
Like these there are multiple areas where it can be implied, whether it is intentional marketing or social media marketing and a lot more. All of them deals majorly with determining or predicting how the
customers are going to behave. Now in the similar process we are trying to determine here whether the customer is going to subscribe or not, whether we are going to we are we will be able to retain the customer or not,
new customer or not. So the whole customer analysis works in that particular part on customer acquisition, retention and might go ahead and comply with.
Same goes with the financial analytics. In financial analytics we go ahead and we analyze other companies financial statements real financial position by looking at their debts, investors, stakeholders,
how much public holding they have, and a lot more. In this detailed overview will give us an idea where to to make the investment or how to adjust our own investment portfolio or business portfolio.
personnels, it can be for the business performance too. operations we try to optimize the cost, optimizes the budget and which figure out how we can go ahead and uh areas for improvement too.
This is true for individuals also, right? Individual per employees there. Their performance analytics is also a thing whether you are doing 360 degree analysis or it is simply one to one based on the size of the organization
improve the performance employees, performance of the employees too. Other areas are risk analytics. Now risk analytics is very interesting domain here. This belongs to you know what is risk here?
In a business a risk is if you have a project, right? Right? It's uncertainty of the outcome. So you have no idea if you're investing in a project how much that project is actually going to go ahead and give you
either a success or a failure. We cannot foresee a future because any unforeseen event. If you know about the risk beforehand we will be able to mitigate the risk. However, however,
that risk has to be quantified. If you cannot quantify the risk, if you okay, we cannot mitigate that risk at all. For example, I know that if I invest in this project, okay, success or failure
totally uncertain. We have no data for a backup because we are in entering into a totally new field. So we are going to be cautious, we are going to have some insurance at the back end that even if this doesn't work we will not lose much.
information and we will go ahead and try to mitigate the risk accordingly. So there are two type of risk when it comes to risk analytics. One of the risk is quantitative. So I know the investment one we can
quantify it. We can go ahead and evaluate the possibility of adverse events numerically. Okay. It can we can predict how much loss our company can make in
possibility of an uncertainty that or unforeseen outcome. So we can go ahead and quantify the possible results here. That becomes quantitative. That becomes quantitative. For example, a bank is giving a debt to
an organization. Whether they are if they go if they belly up, okay, they go investments they cannot pay us back. We know the risk that this much the amount
and we have lost it. That's quantifiable. We are we can apply So we can go ahead mitigate that risk. We can take an insurance if we are going ahead with that and there are multiple ways we can find out to
be ready to face that particular uncertainty or the risk. That is called So whatever you're facing right now, whatever unforeseen event or uncertainty that we have here in the possible outcome, we go ahead and we try to
quantify it. Quantifiable risks are and possible to mitigate and maybe possible to evade entirely. That's what risk analysis is. We do that in our investments basically.
in the insurance business, that's risk analysis. If you go to the creditor business, that is risk analysis. Accordingly, we look into it. And we know how to go ahead and deal with it.
However, there are qualitative risks which cannot be numerically defined and these qualitative risks cannot be mitigated. qualitative risk. Would you like to brainstorm a little
bit and try to figure out what can be arrest that we cannot quantify Especially from the business perspective. Right again the courses part the time that we will take
to actually on the grounds to complete certain steps. okay let's take some example here if you take the example of let's say banking industry right so we can quantify it credit worthiness of the individual that
person even that person has a very good credit worth till now okay we still have a chance that they might or might not give person is there are more chances that they will
However if we go to the healthcare sector right we have no idea that if we person okay after the treatment whether they
are going to be better or they are going to have absolutely no effect or they become worse. We cannot say that reason if they are not under observation we have absolutely no say even if they are
under observation and we are keeping track of each and everything through the documentation we have no idea what happens to that patient patient and when we cannot control that. That's not quantifiable.
Absolutely not quantifiable here. Okay so this in the healthcare what will be the patient condition there is it is up to pure risk. Another pure risk is how people are going to behave so when we say here how
people are going to behave now that is the behavior comes out to be maybe you go to a certain office and you want some papers or legal things to be done now takes how much that person behaves with you how what do they want from you. Okay
wherever the trust of people confidence and trust of peoples comes in that becomes qualitative. We can we can never know how someone is going to behave so some
behave. One of the best examples here is whenever some plant is going to open up, any manufacturing plant is going to open up in a certain area
and the locals protest against it and they get the plant shut down. You'll find many examples in the history of business like these. So either if the damage is huge or they have to close up the plant or they will
have to close up the plant or they will run the plant on the half capacity. happens like these. So these are the qualitative risks. businesses if we go back and see very very interesting examples.
One of the examples is Tata Nano and another example is Pizza Hut. Another example is McDonald's. Okay, they they have to change the color as per the locality and a lot more. So these are the qualitative risks that
we run. And now we go ahead and we talk about Excel interface. about the Excel interface, everyone, you should go ahead and go to a folder which you are looking for. Go inside that folder and then do a
right click. Then do a right click. Go to new and open Excel here. Why? Why we are asking you that to do this here? The reason is if you will open Excel by this method where you're right
clicking and new and Microsoft Excel worksheet, the first thing that it does here is ask you to save it with a proper name. So here the two advantages. First, you know which location you are at.
know which location you are at. And you get to save your file first with Now whatever work you're doing, it is going to be saved at a proper location going to be saved at a proper location that you know about.
Okay, that you already know about. So that's how you open an Excel sheet. Excel sheet. You will know your file location, you will know your file name. You can go ahead and simply keep saving your work and you can always come back
and open it again here. Once you have saved your file like this, we can double click and open everyone. So go ahead to your desired folder. Yes Ganesh, absolutely right. Yes Anupam, that's right too.
Everyone, please go ahead to your desired folder, right click there and save your Excel here. Double click and open up your Excel workbook. It is going to be a blank workbook here. You can shift in Excel deep in bar too.
Very good Yes, we can do that too. That also opens up a blank workbook. save it, we have to be very careful that in which particular file location we are
Okay, all the office here, Microsoft Office basically. It all started with 97 2003. Then we have 2007 version, we have got 2010. Then we had 2013, we have 2016, 2013 and
years. And after 2016 till here, till here, they only had desktop versions. Till here, they only had desktop versions desktop and then only you can work on that.
But when they introduced 2016 in the in the newer versions, they came up with the application software, meaning you can go ahead and use it online or you can go ahead and use it online or you can have this as an application too.
A simple application, doesn't need a proper installation, simple applications too. And now after 2016, okay, they kept And now after 2016, okay, they kept updating it on the application front. So
either you buy it all in the desktop, right? Or you buy the application version which gets updated every month. Or you simply buy the desktop version. So in the 2019, they came up with the desktop version of 2019. Applications
here on the application version, they kept on updating every month. And every third month basically. After 2019, they came up with 2021. This was also a desktop version that you can buy for the
forever. And this is again a desktop version. After 2021, now they are sticking with 365. And this 365 has desktop version also. They have and this is an application
converted everything into an application. So when you go ahead and get this here, the 365, right? It works and updates every month. So you can go ahead and subscribe 365 or you can buy 365 too.
But it requires the subscription now. So this is these are the multiple versions throughout the years, right? So that is why we will have different However, if you have any of these versions, you're good to go. If you have
any of these versions also, you are good. At least you will get to learn right now. If you have this version here, I would really ask you to update a little bit here because you are going to have entirely different names
altogether. Okay? We all have the tabs right now around here. To check the version here, these are the please. File, home, insert. In this we go to the
first tab that is file. This is file tab. If I click on file tab here, we have account. So you click on account. In the vertical menu here, you'll find account. Click on account and you can
account. Click on account and you can see which version you're working with. anywhere of these applications, you're good. By the interface is different.
Now let's go ahead through the versions every through the interface. Now in the I would like you to pay attention here on the left and upper corner here. Do you see undo redo buttons?
undo redo buttons? Those who are in 2019 above, okay, 2021 above, you might see an auto save button. Everyone else will see only undo, redo, and save maybe. Undo and redo specifically. You will see
these two. Those who have with 10 and 15 years working day in and day out, I'm you're not, it is pretty interesting. Let's see here. So do you see the undo and undo and redo buttons here, right? This whole tab is
called as quick access toolbar. This whole tab is quick access toolbar. Why we are calling it quick access toolbar here? Okay, because we have the tools right here that we can directly access.
So if you will go ahead and try to find, there is a drop down everyone. In Mac you will find three dots epsilon there. And in Windows you will find a simple And see here, when you go to the drop down it says customize quick access
toolbar, right? However, it is not quickly accessible because we have to still go ahead this bar. So if I'm in cell somewhere and working, I want to do something here, save undo or redo, and I still
have to cross everything that is here and then we can reach to the quick access toolbar. So let's go ahead to the drop down everyone and please click on an option called as show below the ribbon.
Please go ahead and click on the option show below the ribbon. Remember that whenever there is a difference between the versions in any of the options around here or the names are going different, I will point that out.
Okay, in each versions. I will point point that out. For now, please go to the drop down and click on show below the ribbon. You click on drop down, you will see all the options that you can add.
On Microsoft recognizes as frequently used tools and the features. So as soon as you click on show below the ribbon, observe that right now it is just above your formula bar. So why is it was written show below the
ribbon here because this whole part that you're looking at this is called as ribbon. This whole part is called as ribbon. Why is flexible. If you click on home tab, you see the
home tab features here. If you click on insert, you see all the commands for the insert. If you go to page layout, all the commands for page layout and so far. So here no matter which tab you click on, every time you will go ahead and see
a new feature coming up. So this is the ribbon here and below this now we have quick access toolbar. Now once we have the quick access toolbar here, right? Now we can quickly access it directly from the cell. Now I
don't have to go ahead cross the whole ribbon, all the tabs and then go out here. Absolutely not. We can go ahead and use directly in here. Now inside the quick access toolbar, how do we add the features here?
have the list of the features. For example, we are using sort ascending and frequently. We can simply go to the drop down and check here and it will get added here right now. Okay. So no matter which tab you are in,
no matter which tab you are in, you will still be able to go ahead and access whatever tools you add here. Now it becomes very interesting and advantages. For example, every time we go ahead and
give borders everyone, right? To the tables. things around here. We go ahead and increase and decrease the font size. Now for doing all that here, we have to
always come back to home tab. So I will give you one example here. every time to the headers. So I can go to the fill color here. In the home tab, we can go to the fill color feature here and do a right click.
When we do a right click here, the first option here is add to quick access toolbar. Add to quick access toolbar. When I have this here, I don't have to go back to home tab every time. There is
one more very good feature here called as format painter. I am pretty sure most before, right? So if you want to go ahead and copy the format, we use the format painter everyone, right? It is again in
the home tab in the quick access tool inside the clipboard group here. So inside the clipboard group, inside the home tab clipboard group, we have format painter. It can copy the format for us.
So we can right click and add format painter here too. So every time you need to format paint something, some function out there, tab multiple times. We can have it right here.
are using frequently, keep adding just simply right click on that feature, add this on the quick access quick access toolbar and make your own shortcut keys. It will help you to be proficient. Right? So this is the quick access
Now in the quick access toolbar, let's talk about the ribbon. looking at some tabs which you do not have system right now. Even within the ribbons, you might not
see many options here with you. Sometimes you might have also faced the issue, okay, where the ribbon only appears when you click on it. Otherwise, it goes away.
automatically too. Sometimes it comes back automatically too. Now how does that happen? This collapse and the expansion of the ribbon happens due to the accidental double click on the tab. So go to any
tab, go to any tab, any name here and do a double click. will collapse. When you will double click again and the ribbon will come This collapse and expand is provided to us that if we need more space to work,
we can collapse them and if we need to work with any of these options here, we can double click and expand them here. So that's it ribbon everyone. Go ahead and try this doubt please.
I'm sure most of the experienced professionals might be aware of this. Remember that we are in Excel to do the exact same to do the exact same function or exact same process. There can be multiple
options out there in Excel. I'm going to share one or two at max. Which are easy to follow. Is double click working for everyone? Okay. So as I have already as I've already said Kunal, okay in
Excel there are multiple ways to do the exact same process. I'm going one or two for each and everything. Okay, there are more options there. I can go to the drop down here and I have
Same options. And then we have the keyboard shortcut shortcut here to make it go away? That is control and So there are multiple ways to do the exact same thing.
Now here below the tabs, what we have? We are already aware about the formula the formula tab. So we know this what we call this? We call this a formula bar. We call this a formula bar. Very good.
What do we call this another box? What do we call this box here? Have you ever used this box? Just throw over your cursor over there,
automatically. Just throw over the cursor over there, it will tell you its name. Now we'll understand why this is called And we are also aware right now
if this is formula bar and that is name box here. Okay, a b c d all the alphabets here. If you read it from top to bottom, okay we call this a column. This becomes a column here.
If we go ahead and read left to right, if we go ahead and read left to right the numbers that you represent here, we call this a row. We call this a row. And of course, where they where they're
meeting at between, where they're meeting at between here, we call this meeting at between here, we call this intersection now, okay, this name box is going to show me
I4 that is the cell address. That is the cell address and the whole sheet here, this is the sheet we are at, right? So currently we have Excel and usually we call this workbook as an Excel file.
In this file consists of multiple sheets around here and each sheet consists of row and columns, row and columns which constitute cells.
Currently we are at sheet one. Here. If you want more sheets, we can click on plus and we can get more sheets. Now here within this sheet, how many rows and
columns are there? Uh so to figure that out how many how we have here, okay, we go to the intersection of A and one here When you left click and hold here, look at your name box. Now name box will give
you a number. So when you go ahead and select all, what do you select? You select more than million and uh nearly million and a half rows and you have 16,000 columns, more than that
in one sheet. So whenever you are selecting all and applying borders, whenever you are selecting all on applying a color, you are actually applying that border and color to more than more than 10 lakh
48,576 rows or and more than 16,384 rows or and more than 16,384 columns and that is actually causing the increase sized of your file and that is the
actual cause that your file is lagging. So, if you're a professional out there and you're applying a VLOOKUP, okay, but while selecting a column like this, okay, it means that you're actually selecting 10 lakh 48 thousand 576
values to be find. And that is why VLOOKUP is lagging your file and your file is huge. It cannot be shared over email. actually cause the lag in your file and the functions.
total number of rows and columns we have there. Tariq, you are going to have lesser number of rows and columns because okay, you might have you will columns, but yes, it is going to look a lot different.
Now let's talk about few foundations here. For example, we have got formula bar. I would like you to just stay with me there. I understand you already know everything about Excel. However, just stay with me, please.
So, we have three things here, formula bar, name box and cell. thing, name box is telling us about the cell address or you can say that it is telling us about the cell position, everyone.
It tells us about the cell position that where my cell currently is. Currently we where my cell currently is. Currently we are at J3. J3 means the column is we are at the J column here and at the third row of the J column.
And that is the cell address or position that is told by the name box. Then what is a formula bar? Formula bar here it shows us the true cell value. It shows us the true cell value. What is meant by true cell value?
now. When I say that formula bar shows us the true cell value, it means that if I'm typing a number here, Correct? If I go ahead and write a number with
multiple decimal points here, okay, in the formula bar. However, in the cell, I'm looking at only four decimal We can go ahead and decrease the decimal numbers from here, from the cell.
bar. So if I go ahead and do further calculations with this particular number, it is going to use and utilize all the decimal points that we see in the formula bar.
I can also go ahead and format this number to any currency or accounts here. I can go ahead and format this. Once I format this here, it doesn't Once I format this here, it doesn't impact the true cell value at all.
then if it is not showing us the true cell value? Cell will show us what the cell value? Cell will show us what the end user wants to say. that what kind of result we want to see here. So for example, if we go ahead and
we do some mathematical calculation or we apply a function here. Formula bar is showing us the true cell value, the function that is at the back end. However, cell is showing us the resultant. We can go ahead and format
resultant. We can go ahead and format the resultant as we want. us the true cell value. Name box is always going to show us the position
there. Cell will always go show shows the end user view or the resultant value No matter how you're formatting it, you go ahead, you fill up a color around here, do anything. It doesn't impact the formula bar.
Now name box is not only about the cell address and position. Name box here you can it is actually used for naming the things around there. So you can go ahead can name a whole column, you can name a whole row. In fact, you can go ahead and
name a whole table. Once you name something there, you can use this box to navigate. So whether okay, you can go ahead and use this box When we say here,
so far, but we can still use this box to navigate. How? For example, let's say that you are working on some data set and this somewhere too far. We are lost somewhere too far.
So if you're lost somewhere here too far, okay, what do we do? make sense, right? So if you are somewhere out in your sheet and you are too far, go ahead to the name box and simply type
A1 and press enter. and press enter. You are back. Okay. So this is how you can simply go ahead and access anything from between
the sheets or within the sheet. This is also used for navigation, This is also used for navigation, everyone. So this is your name box, basics, everyone, right? Now after learning about these three,
let's go ahead and talk about the cell here, okay? In the cell here, how do we do the data entry that I'm going to talk about right now? When I talk about data typing here. Whenever in general when we type
everyone, we type one and we usually press enter, right? Whenever we type one, we usually press enter. And when you press enter, it will enter. And when you press enter, it will take you to the one cell below.
number, I have to go back up again and then do the formatting out here, So instead of that, what we can do, we can simply press one and instead of pressing enter, we can use control enter. So most professionals use control
to whether they're applying any function or simply typing down the anything, they will use control enter. here or I will go ahead and type a date here,
right? Or we were are doing any kind of calculation out here. We're here. We are calculation out here. We're here. We are any kind of calculation out here. We can also go ahead and add a text. We can also go ahead and a text and a
number. We can also go ahead and have a number and a text. So any kind of combination can be here. However, please observe the difference You can observe the difference here that the first three here, first three here
are right aligned inside the cell. All these are right aligned inside the cell. However, when it comes to text or alpha numeric, all of these are left aligned And that tells us that whether our number in a proper number format or not
and if we have a text, whether it is properly a text or not. Basically it is used for numbers. number and date format and they can be used for further calculations, they will
be aligned to right. That is why aligning your values to the middle is It is not a good practice. I know you follow that practice. You select the at the middle. That's an incorrect practice because you
mistake. And your analysis will be whole wrong because there might be some data that is saved as a text and those numbers are saved as a text and those numbers are not included in the calculation at all.
So this is the very very basic of data entry, left and right alignment and entry, left and right alignment and using enter instead of control enter.
I hope most of you were aware about this. this. Hi Ritushna.
If I go ahead and align them all to the center, am I do I know that which is center, am I do I know that which is text or which is number? Absolutely no idea. Right? That is why we never align it.
The importance here is if my number is saved as a text, right? If my number is saved as a text here, it will not be used in any used in any calculation at all.
will get the error saying that you cannot multiply it with a text. That and same goes with the date. If I am in the date, I want to apply a February month, for example. I want to apply a filter here, right? But I will
not be able to do that. Reason my date is not in the correct As your date is in the correct date format, it will be aligned to the right side. Because it's a number.
very new. Sorry, not new. This is something very All right. So talking about the data entry here, I am pretty sure, okay, most of you have seen this before. So if you type one here and you drag it down, you
drag it down, you see the one one one here. for the absolute beginners. When you type one here, when you type one here, you will see a green colored square at
the corner, right? In general, your cursor is always a white plus sign. It's a white plus sign here. However, the moment you keep your cursor here at the corner, your cursor changes
into a black plus sign. The cursor changes into a black plus Now when you grab this here with the left click and drag out here, drag out here, you'll find that it is simply copying and
find that it is simply copying and pasting the values. go ahead and you drag and drop here, you'll find it it is copying and pasting you'll find it it is copying and pasting the values.
the values here, you will have the auto fill options open up here. You'll have the auto fill options open up here. See? When you go to the autofill options, we have multiple options, fill series, fill
formatting and flash fill. We'll talk about the flash fill later. series, we'll have the series of numbers coming up.
the series of numbers coming up. So the as far as you can drag and drop, okay, you'll find your series till there. for fill series. Fill series, everyone. This is your fill series.
Sure that most of you are able to do this fill series. Yes, Avanti, go ahead and experience for yourself, right? Right now you're typing one, Avanti, correct? Go ahead and type one, Avanti. When you type one here,
can see the cursor is blinking right now. If you go ahead and press enter, you will go to the next cell here. Okay. However, if you press one and you can
see the cursor is blinking, at this point, if you press control and enter, you will find that you are still at the same cell where you were typing. So that's one of the references there. Control enter has multiple uses.
For now, it is just for entering the data. Now in the fill series option here, if I am dragging, dropping, going to the drop down and clicking on fill series here, it is filling up the series of numbers
But what will happen if I want to go ahead and let's say I want to go ahead ahead and let's say I want to go ahead and fill the numbers from 1 to 100. Should we simply go ahead, drag and drop and fill the series 200? Does that make
it a proficient way to fill the values 1 to 100? Yes, Sutrishna. There it is there. option, right? So step number one. Where is the fill series option? I will
The step number one to use the fill series option, series. So you have to go ahead and write the start number here. Okay, you have to go ahead and write the start number or value here that from this
value I want to start here. So we want to start from one. So we start from one to start from one. So we start from one everyone. option. Fill series option is in the home tab
towards the right hand side just above the purple eraser, purple or pink based on your version. Just above the purple eraser eraser towards the right hand side of the ribbon, you'll find the fill series
So first you type one, you control enter, you stay in that cell here because that's your start number or start value. Now you go ahead to the drop down and you'll find your series right here.
right here. Go ahead and find this out please. here, a dialogue box will appear everyone. In this dialogue box here, you will go a series in rows or columns. First option is series in rows or
In rows means once you will hit the series, it will bring 1 to 100 like this. Columns means it will fill up the column So we don't want it to fill by rows. We want to fill it by columns here. So we
go ahead and select the radio button columns. We want a linear progression, a simple linear progression here meaning 1 2 3 4. here a step value is one meaning the difference is one. If I want to go from
1 to 100 we want to go like 1 + 1 2 2 + 1 3 3 + 1 4 and likewise. one. In the stop value here we go ahead and we write 100. Click okay. So please go ahead and
Click okay. So please go ahead and select here. stop value please type 100 and click okay and let me know are you able to see okay and let me know are you able to see numbers till 100
mathematics here uh growth progression GPS there are multiple type of GPS out there. It is usually asked in aptitude exams. That's that is the GP
That's that is the GP growth progression. It is not used much in business analysis okay. 5% Ganesh. It's okay we'll see that.
So here we go I will go ahead and upload some example about that Ganesh. So if I click on okay everyone it does goes to 100 but how do I go ahead and not. So we scroll down and scroll down and
scroll down and we find out but that's not a very good way to do that. Right so we can go ahead and find it out by control and the down arrow key. Control and the down arrow key. Right if you want to come up here we use
the up arrow key. So we are like clicking here at T1 and we control and arrow keys here control down we are here control up we are here. So we use the control and the arrow keys for the navigation within data set
navigation okay navigation in data table okay navigation in data table or any kind of range here that we have. navigate control down control up go ahead and see that please.
Wait for your responses right now I do understand this is very very fundamental only few of you are beginners out there but if you are able to follow along please let me know that. And if
if you know something new you know about the shortcuts or something please do in the chat box. Follow along with the control up arrow key and down arrow key have the series with us.
Now this fill series option can be used for multiple can be used for multiple purposes. One of the purpose can be you may want an odd number or an even number okay? So you can go ahead and choose to fill an odd number series or an even
How we can fill up the odd number and the even So what's an odd number series? We want one, three and five something like this. right? So if there is a pattern going on, I can
go ahead and write few of the numbers around here, select the numbers here and drag and drop, drag and drop. And it can actually identify the pattern and fill this up. However, this here
however, this here is not feasible again it based on the drag and drop. it based on the drag and drop. So here we can go ahead and use fill series again. But in the fill series here, observe
that there it inside this dialogue box, there is no option right now where it is asking you to type down from where you want to to type down from where you want to start.
where you want to start. Let say I want to start the even numbers from the two, number two there. But inside this dialogue box, we have absolutely no place to put that where is my start value, okay?
So remember that whenever we are filling the series in, the start values goes into the cell. So for example, if I we want the odd example. We go to the fill series option right
here and the fill series we want to go ahead with the columns. We want to fill numbers in the columns here. And let's say the stop step value, values, what should be it is if I want the odd number to be there after one.
What should be the step value here? Two, very good. So we go ahead and we type two here. And let's say the stop value let me keep example right now. So I'm taking the stop value at 10.
And now we go here and click okay. Now we go okay. And here we have the odd numbers. In the similar manner you can get the even number also.
So if we want to get the even number here, we have to decide the start value here. enter. inside the fill series here, inside the fill series here, we'll do the exact
same entry. Columns two, step number is two and stop value is 10. So the inside the entry is exactly same. What is different here this time is the start numbers that we are writing
Go ahead and try this out everyone and please please are able to write the odd and even numbers by using the procedures. Go ahead and try it out. Only the start
number that we are writing here is changing. Everything else is absolutely same. Go ahead and try this out. Give me the box. Okay, let me try out an example for you.
So again, okay, let's see this is my starting value. I'm just taking some starting value. I'm just taking some other starting value right now. and we want to fill in the column. This is the growth around here.
Okay, and let's say I want to go ahead and accept the value for two and maybe stop the value at I'm taking a chance here, 30 and I stop here and I click okay. Do you see what is happening right now?
It is a growth series here. What was the entry I did here? The step entry was two and the option instead of linear, we choose growth. What is happening here?
What kind of progression we are setting up right now? Multiplication. So this is what a growth progression is everyone. Okay. Right. So if we want to go ahead and
up, multiply by that number, we use the growth progression. That's very good. Now after the growth here, we have one more option, date. After the option here, we have one more
option date, right? Yes, very good Usha, Lalita Prasad. good. Yes Kunal, Saurabh, Ganesh, Yogesh. Yes Kunal, Saurabh, Ganesh, Yogesh. Very good. Yes Sarla.
date. Okay, so how do we enter today's date everyone? How do we enter today's First I want to show you the date progression here. So let's talk about how do we get today's date here. How do we type it? Yes, that's the
question. How do we type it? Yes, it is comprised of date, month and year. So let's talk about date here a little bit, okay? When I say date here, so you are telling
me a format right now. So see, based on the location you are at, based on your system settings, either you are going to have a format written like this or you are going to have a format written like this.
Every format is correct. Every format is correct. So these are the multiple formats you'll find the date. very good. Yes, we have the answer Mala. Very good. We have the shortcut buttons
So here if I say the date is equals to today everyone, okay? We are applying a function, how do we apply a function everyone? We type equals to here. When we type equals to here, we type TO. When I'm typing TO in the drop down, I
can see the name of the functions. I can simply go to the name of the function and double click. Yes, we can use the shortcut methods too. For now, as we're looking at the function for the first time, I am using the I'm using the user
interface here, okay? So I'm typing equals to I'm typing TO here. From the drop down we have today. When I double click on today here, see the syntax argument, right? There is no arguments, no parameters inside it.
We go ahead and close it with shift and zero. We close the parenthesis there. And here you go with control enter. Then you apply this here, we have a date, right? So this is how the date looks like when we go ahead and get it
looks like when we go ahead and get it from the That is a function. There is one more method that we are you are looking at currently and they are telling us that we can go ahead and
and type control and semicolon and that will give us a today's date and yes it You go to a cell, empty cell, you press control semicolon and you will have a What is the difference between the two here? If you're getting the date from
the today, okay, this is going to be dynamic. Meaning if tomorrow you reopen this particular sheet, the particular date is going to
here, now it is going to be static. Meaning it will be like a data stamp. It will be like a data stamp. It will not change. So for example, if you want to go ahead
and tell your tell the person that on this particular date I have I have completed this particular product project, okay, you'll go ahead and stamp it with that particular fixed date. You don't want it to change here.
In today, maybe you are going ahead and you are creating an invoice. In invoice you every want that particular date to update by itself, you can go ahead and use date. And any you can get any kind of format,
it will be based on which particular system you are currently working in. So based on your system default, format will appear. Every format is correct because it will pick up your system default format. Okay?
date. I'm going to go ahead and go ahead and bring date with control and semicolon everything. Okay? I'm going to bring date here with control semicolon because we want to work with it further. We
don't want it to update. So this is date. I'm going to with control semicolon a simple date. Please go ahead and do that. Bring the simple date first. We will learn everything that Krishna
step by step. Once the basics are done, we go to the And that's where we learn about the number formatting. That's where you will learn how we can go ahead and change the format.
format. Right? Once you have got the date here, let's go ahead and drag and drop. Let's go ahead and drag and drop please.
A simple drag and drop please. When you drag and drop here, what do we see? Today's date today's date plus four more days. A simple drag and drop, yes.
Right? We get the whole date series. Very good. When we go to the drop down here, now the autofill options will change. Another is fill days.
screen. Please back to your screen again. changed to nine, 10, 11, 12. So basically days are changing right now. So if I go ahead and click on fill days, the days will change. Pay attention
If I go to autofill options here, I click on fill weekdays. I am clicking on fill weekdays. Observe here that the eight is there, but the nine is gone. Okay? Since we are starting at eight, it
cannot get rid of the eight here. However, however, if I go more around and I go to the drop down I ask you to fill weekdays, you'll find that fill weekdays, you'll find that Saturdays and Sundays will be gone.
here. So if you want the weekdays to be filled So if you want the weekdays to be filled here, that's how you get your weekdays.
Saturday? Because that's where we started. So it cannot get rid of that. weekdays. If I click on months here, it will only change the month. 8 February, 8 the March here. So maybe
fixed. So you're creating a whole table around you're creating a whole report and you want to calculate the total payout went out on that particular date. So if that's my payout date, okay,
the day. So here month is changing. And then we have got year. So if you want eight in February to be fixed but you want multiple changing years, you can drag and drop. You can even find out
can drag and drop. You can even find out the years here. we talk about the date. Now here, most of the time what happens whenever you are filling up the date here, right?
There uh be an issue where you are getting hashtags like these. Why do we get these signs here? Reason is very simple. We do not have enough space to type in.
columns and you double click, okay, or you simply drag and drop and give enough space, okay, you will have it appear. So whenever you see the hashtags or pound signs here, okay, it simply means
currently you do not have enough space to type in whatever is there. And you will see the value. It means space issue everyone. Just will be able to see the value. Okay, Ritushna?
the drag and drop. If I want to go ahead and use the fill series right here, fill series right here, you will find that we have another option date. As usual, we want to fill in the column right now. And it is automatically selecting the
date. It understands where we are starting right now. And now see you have whether you want to change the day, you want to fill only weekday, you want to fill only month or if want to fill only year.
So here I will type control and semicolon we have the eight to here. We have the weekday right now here. We can go to the stop value. In the stop value here, okay you have to type in the full date. So for example I simply want to go
ahead till let's say 20th Okay. I want to go till here and I want to fill only weekdays. And here I'm giving a stop value also
escape the alternate days. Maybe I'm planning a training and in that training planning I want alternate that training planning I want alternate weekdays.
training will run or the number of days will be there. So I will and go ahead and choose these options. So here columns, date, weekday, step value I'm keeping two, stop value I'm keeping as 20th February.
Once I will do these settings here and I click okay, right see it has filled up the weekdays that to alternate days. list that these are the days where we can
that these are the days where we can keep the training. So this is how the date series worked. So someone has asked that how do I go five plus today? Okay. So basically you can use the exact
same options for here also. If you will do five plus today, you can simply go ahead and use another cell and can you can do five plus this particular date. It works exactly similar in further calculations.
Riteshna first write the date. Make sure you're selecting the date. If you're selecting the date, okay, then if you go to the fill series, it will allow you to click on it and open up that.
editing mode. If you are in the editing mode like this, cursor is blinking anything. So make sure Ritu you're not in the editing mode, okay? Here, go to the drop down here, go to
Fill the columns in the step value. You are going to type two here. Here we are selecting weekday and in this drop value, okay, we are writing the 20 February and 2025 here.
Okay? Please go ahead and do that. Do these changes, please. date will be automatically there. Select weekday, type a step value and a stop value properly.
The stop value format should be exactly similar to the format that you see in your sheet, okay? So this is about a simple date series here, right? While we are talking about the date series here, let me
go ahead and show a little bit about data here. So when because Rachit has asked a question here that how do I go ahead and doesn't matter whether you're getting date from today or from control and
control and semicolon. Okay, the backstage value remains exactly similar. Okay, we can do that calculation. We can do that calculation with any of the dates here, okay? We go ahead and do
the calculation with today because we need an updated date. We need an updated date. Okay, and that is for today, for today. However, okay, when we are getting from a static, a static here, it is a data
completed something. Right? So that is the major difference here. Now whenever you have a date here, whenever you you have a date here, okay, how does that date looks like? Have you
ever noticed the one thing? If you go ahead, sometimes your date converts into ahead, sometimes your date converts into a general series of a number. So you have the whole column of a date and sometimes your date will
of numbers. So, the professionals out there, they here. Right? Why observe here that why is it happening? Right now, the date that I have typed
in, right? Whenever you go ahead and observe everyone, okay? So, I'm going to delete this here. I'm using control and semicolon here. The moment I type the date and press control enter, automatically it picks up
here. It is automatically picking up the date format. If I go ahead and convert this date format into general or a number, see the general or a number here, it converts into a
a random series of numbers. Okay? Now, why does that happen here? represents? To understand this number here, okay? I number here, to understand this number here, I'm going to go ahead and do this.
I'm going to type one and one here. I'm typing two and two So, right now this one and two are numbers, everyone, right? I'll go to the number formatting here and I change these numbers. I change these numbers
these numbers. I change these numbers right now into a date. When I change these numbers into a date, you will find that it says 1st January you will find that it says 1st January 1900, 2nd January 1900.
Okay? So, for Excel, the first day is 1st January 1900, second day is 2nd January 1900. So, if this is today's date, if this is today's date and we when we convert it
today's date and we when we convert it into a number, it is 45,60696 into a number, it is 45,60696 days since January 1900. So, it counts the number of days, everyone. And this number of days
technically is called as date series. Technically it is called as date series. And this date series actually fixes your format. This date series helps Excel to
determine which day, month and year and week you are at. This date helps you in the calculation. So whether you're doing today plus five, so in today's date plus control, you get date with control semicolon and you want to do the plus
Right? So it is called as date series and this concept of date series is Whether you go to Power BI, you go to Tableau, you go any everywhere, date series concept is followed everywhere. And that's how your date work in the
So whenever you see these numbers, simply change the format. Whenever you're doing the calculations, do remember that if you deduct two dates from each other, you are going to get the answer in number of days.
I okay. So this is how the date works. Date series tariff is just the number of days since January 1900.
There is a specific reason to take that. Okay. So Microsoft has a documentation on this particular part. There is a particular reason reason at the back end particular reason reason at the back end that why January 1900 was taken. Okay.
Hi Tamanna and Tarif. Okay, don't worry about it if you don't understand what is happening here. Okay. See Excel, let's understand it like that. correct date or not? Okay. How Excel know whether it is the
correct date or not? It counts the number of days since 1st January 1900. It simply counts the days from there. So formatting here. Okay. See Tamanna and Tarif, problem
here is we don't understand currently what number formatting is. Okay. So for example, if I am typing a number here, for example, I'm writing 89. Okay. If I'm writing this here, I'm writing this here, I convert this into a number. It
is converted into a number format. If I convert this into a currency, it is converted into a currency format. Okay. So whenever any number is converted into So whenever any number is converted into a date, it simply go it simply go ahead
and it simply goes ahead and it start counting the 89 days since first January counting the 89 days since first January 1900.
ahead and fill the series after 8 February it should be 9 February right how does it go ahead and understand that to understand that part here it should know ahead that at this particular date actually is 45,696th
day since the 1900. So if I go ahead and drag and drop here it will simply add one one one to that series and give us the answer.
number of days since 1900 to specify and identify a particular date so that it can go ahead and do the further calculations with it. So Tamana and Tarif if you are not able to understand even now that what is
happening here don't worry about it the more we will move forward we will see some functions and calculations you will get the understanding about it. because you I can see here that you are not from the
data or we have not worked here before so no worries it's all right okay. some sessions we are not which configuration laptop will be good for this course in Excel. This is the first session Soujanya right and any laptop
will work if you have if you're working on Windows you can have 10 or 11 you can have an we have already talked about the versions when we started this you can versions when we started this you can have any version from 2013 till 365.
work we have already talked about that when we started with the session. Okay any version will do from here to here. Mac you can have any Mac just have the spreadsheet version of that.
spreadsheet version of that. Okay. bit about date everyone. This is how things work. formatting. So please go ahead and save this workbook.
here. And all of you when you will if you have downloaded the folders very good. Inside the folders here inside the data set in the lesson two data set okay we are going to have a demo one file.
An Excel file okay there is a demo one Excel file there. So I would like you to open up that demo one Excel file please. I'm also sharing this in the chat box here.
here. Okay just I would like all of you to please go ahead and double click and open this up. This is how the demo Excel will look like. I am going to reshare the screen
like. I am going to reshare the screen right now. When you double click and open it this is how it is going to look like. When you have opened up your demo one Excel file please let me know.
That your whenever you are ready. We see the Epsilon three dots here then we see the sheet two here right. Currently we are totally unaware that how many sheets are there. If you want to figure out how many
sheets are there instinctively we will go ahead and start clicking on the arrows or these three dots or the Epsilon here right that's what we clicking here. Or clicking here or we click on those
three dots there that's what we do usually. However if this happens with you if this happens with you and you see these three dots and that sheet there. How you can figure out that how many sheets do you
have in this Excel. To figure out the number of sheets you have in this Excel workbook. Simply go to any of these arrows and Simply go to one of these arrows and right click.
right click here, you will see the list of the sheets here. of sheets we have we have here, simply right click on the arrow, you will see right click on the arrow, you will see the list here.
okay? You you have to right click on that and That's why I have shared it on the chat box too. If you will extract it, you box too. If you will extract it, you will be able to work on it.
the sheets here. Now we have got sheet one and sheet two. here. We have a data here in sheet one. We click on sheet two. We have got another data here in sheet two. If you'll actually look at the sheet one
what is the difference between the data set? Is there any difference between these two data set in sheet one and sheet two?
here is that this is date. Num the data is absolutely similar. Whatever data we have here it is the exact same data that we have here. formatting. Which one looks better everyone? Sheet
one or sheet two? Right the formatting and font. Right the formatting and font. Which one looks better? Yes, wonderful. Why? Because this is properly separated.
going on here. However, reading this is quite painful. Right? So let's go ahead and see how we can properly go ahead and format this particular table here. Right? So let's see how we can go ahead
everyone. Uh, I let's uh first let's select the whole table. Yes, very good finish.
So first thing is how do we go ahead and start changing the format here? First part that we do here is we go ahead and we select the whole table. You with control A. You can also go ahead and select the whole table with another
keyboard shortcuts. We will discuss those keyboard shortcuts tomorrow. Today we will simply go ahead and select this table like this here, okay? this table like this here, okay? Now we can go to the home tab here
Now we can go to the home tab here and in the home tab we will decide one font style here. We'll decide it. After deciding the font style here, we will stick to one font size. We'll stick to one font size here.
So we'll make sure that our font size and font style is exactly similar. After that we can go ahead and give the all borders. We I can also add the all borders to quick access toolbar. So I'm going to the drop down going to
the all borders, right click on the all borders, added to So what will happen? I don't have to go back and click thrice. I don't have to go back and click thrice here. So here we have we have a font
here. We have selected our font size here. Okay, after selecting the font size we are clicking on the all borders here. After giving the borders right now here, look at the category and region. Now we
pay attention here to category and see the columns around here. Okay, all the columns here are have multiple column width. We want to go ahead and give absolutely proper column width to everything,
So how do we give proper column width to everything? It is not feasible at all that I bring I pick up each column and drag and drop or double click here. Okay? So So we can make sure for all the
this. Resize all the columns at once. To resize all the columns at once, we go to the first column here. We select that column. We drag towards right.
columns together. So what we are doing everyone? We are selecting all the columns together. So select the first column here. First you select the column and you drag towards right. All the columns are
properly selected, right? Once all the columns are properly selected, now you have two options. Either you can keep your cursor anywhere between any two columns. Your cursor will change into two headed arrow and
here. When you will double click here, everything will get resized as per the number or the data points inside it. However, this doesn't look good. So you can go ahead and decide the exact
width also. You can go ahead and decide the exact width that you want. the exact width that you want. So you can go ahead, click and extend. And every column will have exact same width.
So here select, drag and drop. Drag and drop to a width and every column will be properly equally
drag and drop everyone. Go ahead and resize your columns. Now we will go ahead and start with number formatting. group by and subtotal for data management and then we will see data
cleaning prep and more examples there. And through this we will be exploring And through this we will be exploring all the data tools inside the data tab. Okay. So now last we have seen that our data is right now
It means that it is not in the proper date format. It is here as a text. So if you want to convert this state into proper date format, the step number one is we select this column or the range of cells here.
Once we select the range of cells here, we simply go to our home tab. In the home tab we have here general. This is a number formatting drop down. So first we are selecting the cells
here, range of cells here. We are going to use the general drop down here and we to use the general drop down here and we can simply go ahead and click on short date. When I click on short date here,
it see here it recognizes this as date but it still doesn't go towards the right. able to understand this is a date or not. If I convert this back into number
it still shows the text. It means that it is still not a date. how to for check properly that whether this is for check properly that whether this is recognized as a date or not.
For a proper recognition here we can go ahead and apply a filter. We can go ahead and apply filter because currently we have no idea because see everything is left aligned. Have you know have you observed that right now?
Everything is left aligned. Even the number here, this is also left Even the number here, this is also left aligned even though it's a number. this is left aligned here. So if I go ahead and select the whole
data set if I select this whole data range for date and I keep it at the right hand side, it is at the right side. face if you will keep aligning your numbers to center or left or right.
here that whether these numbers are correct or not. The way to verify this, the way to verify this is we go ahead and we apply To apply the filter here everyone, where the filter where sort and filter options
Where sort and filter options are available everyone? In the quick access toolbar, we have it. I have it here right now. So this is the sort and filter. In the editing we have sort and filter.
access I have added this filter right now. So within the home tab, we have editing group. Inside this editing group, we Okay? We also have data tab. In data tab, we
have sort and filter group all together. There's a whole group here which is sort So how to apply a filter here? Since it's a small table and it has absolutely no blank cells here, we can simply go ahead and click on filter.
filter. So keep your cursor anywhere inside your So keep your cursor anywhere inside your data set. data set and go to data tab. In the data tab, you'll find filter. Simply switch
on the filter everyone. Remember that we are applying the filter because since the alignments were given manually, now that check is not working, that whether this particular number is number or not, whether this date is
recognized as a date or not. To To verify that, we are applying the filter. verify that, we are applying the filter. So first apply the filter.
data set and then apply the filter. I do understand those are professionals out there, we always select the whole data first, whole table first and then We have to do that because in real time we have lot of blank
columns and blank rows or simply blank cells in between. table. Since we have the intact table right now, we can simply click on filter. I suppose everyone was able to apply the
verify. First, let's verify our sales amount. If we go to the dropdown of the sales amount here, it is getting a number filters, meaning it is recognized as filter number by the
Excel. So here, so Excel can determine this as filter because it is recognizing this as number filters there. Similarly, we go to date drop down here
It is giving us text filters. It is not able to recognize this as a date. So no matter how much you write align or left align, this is not a date system.
working out. Go ahead and check it out, please. went to home tab. We have already went to home tab, right? And we have see in the formatting it is saying it's date.
Excel is not defining this as a date, otherwise we would have date filters Go ahead and check it out. Are you able to see that? This is the best check for the formatting.
Tamanna, what do you mean by no here? I don't get it. Can you elaborate, please? Is it at when you go to the date drop down here, do you see a date filter or a down here, do you see a date filter or a text filter?
So let's go ahead. How do we if date is not working with normal formatting here, how do we convert this into a date? So we will see that here. Number filter often fails to
determine date as a date format. We will see how does it work. about the number formatting a little bit here. In the number formatting in the sales amount here, how many types of
formatting we have available? General, currency, accounting, time, percentage and others. Okay? So numbers can be formatted to either So numbers can be formatted to either currency, accounting or anything else.
That is number that is general number formatting. Okay? We do have custom formatting. We will see the custom formatting too. However, let's first
work with this date. Whenever you face difficulty like this, especially with the date filter or the date formatting and number formatting is not working, we use text to columns for formatting. We
use text to columns for formatting, which is in general is used for a split one column into multiple columns. Split into multiple columns. We use the data tools text to columns
here. Now let's see how to do that. your date here without the header please.
From here we go to data. Inside in data data tools you will find text to columns. The original the text to column has multiple applications. One of the application is when you get a CSV file
it can break it into multiple columns. That's there. Other is to format the date and it's fantastic in doing so. So please go ahead select your date range without the header everyone.
Then go to the text to columns here. Go to text to columns here under the data tab. When you click on text to columns
Read whenever there is a dialogue box read properly. read properly. As you can see here it says here delimited. We don't care because we don't want to go ahead and split this
up. Why delimited? Because there are some characters that are separating each field and we understand that and that's Currently we are not looking out for separation.
that we don't have to separate it at all. So right now also observe right now we are at a step one of three. We are at are at a step one of three. We are at the step number one here everyone.
Okay and we are going to check on or select the radio button fixed width. Once you select the radio button fixed width here, please click on next. So before I move forward, let me have a quick check.
quick check. Are we at the same path? Ritu fixed width because I don't want to split it. When we will move forward, you will see why fixed width was there.
Thank you for following up with me. Now let's go here and click on next When we click on next here after fixed width, see here it says that if you want to have a column break,
then you can create a break line. I don't want to create a break line. So we simply go ahead and we click on next. We are not clicking anywhere. We are going next. Now in the next here, the third step is
very important. In this third step, it says here that select each column and set the data format. It is giving you the instruction format. We already have one column
and this the format here is date. Now in this date format here, the first we have got year, then we have month and then we have date, correct? So we go ahead and we tell Excel that
this particular column column here have first year, then month, then here have first year, then month, then date. So we are recognizes as Y M D.
date. So we are recognizes as Y M D. So recognize the date as Y M D. Yes Ritu you can but remember to uncheck every checkbox there, okay? Now here everyone, this is Y M D. The destination here is A 2.
Right now we are simply checking whether this works or not, correct? So we will not replace the original data set. Instead, we change the destination So how to change the destination here? Simply click inside the white space
Simply click inside the white space here. Currently destination is A2. So we delete this here and we click outside right now. Click on any cell. Click on any cell outside. Currently I am selecting A14.
outside. Currently I am selecting A14. So click inside, delete your A2 and delete the A2 here and select any cell outside so that we don't replace our original data set.
Once that is done, go ahead and click on finish here. Go ahead and click on finish here. Two changes here Y MD. Change the destination here and click on finish. Do we have the answer?
whether my output is correct or not and this is the correct approach also in Okay, otherwise if anything bad happens or you make an error, you have to go through each and every step that you did to import the data back here.
So let's go ahead and click on finish everyone. When we click on finish here, we have the date. Now how do I know it's a date here? So I will go ahead and keep a check here. I'm going to call
keep a check here. I'm going to call this date for now. I will select this whole column. Okay, I'm going to apply the filter here and see now it's a date filter and date filter is quite different.
date. Even if I go to the home tab here, it will be as proper date it will be as proper date plus it will automatically keep the format of your system. That's how also you can recognize it
because it will automatically keep the format of your system. So this is how it works. This is our text to columns. Text to columns is used whenever you are not able to recognize the date format. And
able to recognize the date format. And this date format right now, okay fields that can be applied, all the other fields are no matter in which format you have the date here. In each and every format you can actually go
ahead and apply TTC. So let's understand it like this. Sometimes what happens, okay, whenever we are getting a text file or CSV file or an in exported file from any of the ERP systems, usually dates are written
ERP systems, usually dates are written like this. number 2005 09 01. That's how the dates comes up. here. And then we are not able to apply
formulas on that. We are not able to apply do anything in that. In those cases you can simply go ahead and ask TTC to recognize it here. We'll see the example of TTC more, but right now we needed to format this out.
are correct. Now we can cut and paste everyone. Right? We can cut and we can paste the numbers here. Please do that. will do. Please go ahead and paste it and give
borders. Please go ahead and do that. So again we use it for splitting one column into multiple columns. For example, I have but it is in one column. We want to go ahead and separate the street address,
city name, state name, pin code, area, country in multiple columns. In that split. We'll see that in detail as we will move forward. Hi Riteshna, how would would we do this for large data set?
In large data set you simply go ahead and in you add one more column in one next column, empty column you go ahead and you paste that. Riteshna, inside the TTC what you are telling Excel there that
in the text format first is year, then this is month, then this is date. In the output it will follow your system default custom format.
custom format. That's date series. That's date series. If you select this, you go back here, convert this into date, you will get the date. 449948 it's the date date series.
Now here we go. This is now date is in the correct format. Okay. that we have done. Wonderful. So we have used TDC right now. We have seen date here. Now let's talk about
more formatting about the date. So let's talk about more formatting within date. So right now our date is DD MM YYYY and it is picking up our custom format that is it is picking up the system formatting right now.
How about I would like to go ahead and type a date where I want to simply maybe I simply want to keep month and year. and year. Maybe I want to go ahead and
want to know that what weekday was it and then I want month and year. I want my whole date here. So like this if I we have multiple
custom formatting to give how we can go ahead and format the date here. So we know the normal number formatting. Now let's look into one by one into custom format. Let's start with date since we have
Now to understand about custom formatting of date everyone, again select your date without the header. Select your date without the header Select your date without the header here.
do it. First way to open custom format is we home tab number format and click on more number formats. This also opens up this dialogue box or we can simply go ahead and click on
the expansion arrow here, this. So here's the small expansion arrow here. So we select the range without the headers and here is a small expansion When you click on open, this opens up the same dialog box here.
Or we have a keyboard shortcut and that is control one. Control one here is the glow is a shortcut that open formatting options, okay? That open formatting option. You click on a table, it opens
it opens the chart formatting. You click inside the table, it gives you give you table formatting, okay? So right now, if I'm selecting this here any of the three ways we go to
date format here, but none of them matches exactly what we are looking for. So we go to custom everyone. So in the vertical menu here, we go to custom.
When you click on custom here, observe that it says in the type, this is DDMM YYYY. This is our custom formatting DDMM YYYY.
that this code that is typed here, this is editable. So go inside this type after by here. Once you click after by here, you'll find that the cursor is blinking there. When you find the cursor is blinking,
simply delete the whole thing. The moment you delete the whole thing right now, so without any formatting, you can see the date series will appear you can see the date series will appear here.
simple date here. So I'm typing here D. The moment I type D, since it's 22, I'm getting 22 here. I type D again, remain same. It fine. I will type D again. Now it is sun.
It remains at Sunday. I type D again. I have the full spelling for Sunday. And like that. Okay, good. This is what I was looking for. So I give a comma here and I move forward. I give a space after the comma. And now I will type
here M again. If I type M here, I get one. I get type M again. I get zero and one. I type M again. We are getting the abbreviation here that is Jan here.
January. After January here, I am typing D and D. I type I'm giving hyphen again and now I'm looking for year. So I now here I'm going with year.
You can use a small uppercase, lowercase, any case you can use here. you want. You can make any combination that you So right now I'm giving this combination here. I'll long date type here.
Go ahead and type this out. I'm typing this in chat also. I am also typing this in a little bit larger font here so that you can type it out and click so that you can type it out and click okay.
specifications here, whatever you want to type.
weekends? That is done by the date functions, not formatting. Date functions recognize that. We have filled the weekdays drag and drop fill series yesterday, correct?
If you want them to recognize weekends, either you use the date functions for either you use the date functions for that. It is. And the custom formatting gives us a lot more.
this, right? Have you clicked on okay, everyone? and reaction buttons, please? Great, wonderful. Here I'm going to click okay right now and see.
Observe here right now. I have hashtags. Why? set your column width. You can double click or drag and You can double click or drag and automatically it will be here.
good, Saurav. Ritu Shna, this is not a drag and drop. Okay, the table is not formatted. That's why whatever date one wants, it's written there. It's not a formatted one
or let's say a sorted one here. Okay? formatting. Yes. Now let's go ahead and look at the sales amount right now.
Let's look at the sales amount right now. Currently the sales amount is in simple hundreds, right? The sales amount is in simple hundreds here. We are fine. How about we want to write this in a
proper denomination? Like we want to go ahead and want to write this in ahead and want to write this in thousands. Where is 14 here, we want to write it like 14 K and likewise.
So it should convert it should be in number format. However, it should give us the denominations like K. If it is millions, then we can go ahead If it is millions, then we can go ahead and we can convert this into millions.
Yes, it has to be identified as date only. If you cannot identify as a date, only. If you cannot identify as a date, then DD MM YY doesn't work. date into a proper date format and then we were able to custom format it.
Otherwise it is not date formatting will only be applied when it's a date. So here, how do we go ahead with the custom formatting? We can select the
numbers again everyone and we open the custom format. In this custom format here, we go to custom again. Now in this custom here, in this custom here, do you see there are multiple
formatting that is written here with the hashtags, pound signs and the zeros hashtags, pound signs and the zeros here? Do you see anywhere millions or billions written here?
however, if you go to number here, you do see a thousand separator. If you check on thousand separator, see what happens. 1,500.
If I uncheck this, it's written this as simple 1,500, 1500 continuously. The moment we go ahead and check on here use separator, it becomes 1,500.
So this is a thousand separator. It can determine when the thousand is getting completed. So after every thousand, it will put a comma. If I go to custom now, see automatically
automatically the custom format appears. This custom format is recognizing where the thousand comes up. the thousand comes up. It is made up of hashtags and zeros.
Now what is meant by hashtags and zeros? Try to understand this here please. In the custom formatting, in the custom formatting,
Zero represent fixed values. Meaning if it is writing 0.00 0.00 meaning after decimal there can be only two digits.
There can be only two digits. However, this sign here represents that there can be multiple digits. Multiple digits. So even if the this hashtag or the pound sign is written
only once, it means any number of digits can be written here. the fixed values, then we use zero zero here. If you want to say that there can be multiple before the decimals, that is fine,
we use the hashtag here or the pound sign. So right now what we want? We want to go ahead and specify that to Excel that see, once you see the thousand separator, so
there is a number, you find a thousand separator. Okay? After that, I want you to go ahead and add one decimal and at the end it K. Write K here to determine to simply
denote in Roman that it is thousand here. So we go ahead and we type in here. Look at the sample. If I delete this right now, it says 1500.
If I delete it more, it is still says 1500 because the hashtag here means multiple values. Right? And see here it says two right now. Why? Because it has rounded it off.
Why? Because it has rounded it off. It has rounded it off the 1500 to two. Okay? So this is usually the 1500. So how do we go ahead and write the 1500 here? So we go ahead with the hashtag,
And it says 1500. This is the first separator here. again here. It gives me full 1500. So I would like that okay, can you add some decimals around here?
That giving us after the hashtag, I am asking it to identify the thousand separator. After identifying thousand separator. After identifying the thousand separator, give a decimal.
the thousand separator, give a decimal. And after decimal, I want two digits. get the thousand here. Go ahead and get the thousand there, please. Hi Yogesh.
programmed. That's how it has been programmed so the pound sign also there is a limit of numbers that can come here. So it simply gives us a denotation that go ahead and bring out all the numbers
that you can within it. Understand this here. It is a concept, it's a custom format. It is a pure coding language here. This here is a pure coding at the back end how it is determining the
number format, right? In number formatting here, first mathematics. In mathematics, do you understand tens, hundreds, thousands? We have understood this in maybe in
primary, right? So when I will write here 100,000 like So when I will write here 100,000 like this, if I'm writing 100,000 like this, okay? Or I'm going to write like this.
We can go ahead and determine how many thousand separators are coming in. How These commas are called called as thousand separators because we can recognize when the thousands will come in.
And that's how Excel is trying to determine it. Excel is trying to determine it. So right now what we have is the 1500 So I'm calling this as 1500, but in reality it is 1,000 1500 1,500.
If I ask you manually to convert this into the denomination of K here, what Right. So why are you writing like this right now? Like this here? Because now your thousand separated shifted.
shifted to here. Correct? This is what we are trying to communicate to Excel, but Excel is not a So we have to communicate to Excel in a language that it understands.
So the language it understands is the hashtags and the zeros. For hashtag it means here one one hashtag here means that we can have multiple variables here. Hashtag here means that if I'm writing
hashtag once that doesn't mean one single variable one single digit will be there now. One single hashtag means there can be multiple digits, but they should be denominating one single number.
So there can be multiple digits, but it will denominate anything that comes before the thousand separated. So this is what is meant by the pound After the point this hashtag here I will give a comma.
This comma determines that thousand separator. So what I am saying here is wherever you find the thousand separator before that thousand separator any number of digits that you have you go ahead and you write that in.
After you write that in you start giving me decimal points. Now you start giving me decimal points. I want two decimal points here. And then we go ahead and we put the K. Okay? So then you put the K. So I want
me. I am not looking for the exact number. So round it off to two. That's what we are currently asking it to do. This is what you will
Are we good now? Do we understand this here? Okay? So when you are writing here the pound sign or the hashtag it is simply trying to say that
you do not have any number with any extra zeros. extra zeros. Okay? It is going to be exactly very it will not force any zeros. It will represent only the digits which are
needed. So there are any leading or trailing zeros, okay, that will not be there. So it will give us flexibility, that's it. However, if the number is much bigger, it will still convert that into
thousand. Okay? I need to click okay for right now. So let me click okay here first. So let me click okay here first. Here we go.
All right everyone. So this is how we move forward. This is the custom number huge. And there are multiple options here like And there are multiple options here like that.
own, Saurabh. Okay, but you have to follow along with Okay, but you have to follow along with the thousand separator only.
Alok Nanda. See here I'm selecting the numbers here. Numbers will be shown as custom. Okay. Numbers will be shown as custom. If you'll keep your cursor on sales amount and then you select everything,
shows you the number formatting of the first cell that you are selecting. So do not select the header, please. Only select the numbers and you'll find it custom. It will always be custom.
everyone. This is the custom formatting and this normal formatting here. formatting a little bit more then we'll remove for the sort and filter.
So please go ahead and click on sheet two. Please go ahead and click on sheet two everyone. Saurabh, you can use them properly like a number. If you remember the remember from yesterday,
even now if you click here, it says 1.5k. But at the back end it is 1500 only. Remember that whatever custom formatting okay, whatever custom formatting we are doing, it is all of
see it. It has got nothing to do with the You can see in the formula bar underlying value is still intact.
left alignment I mean 1500 what is happening here? How write in text why will you write in text?
The custom table option will select to get 0.5 k. get 0.5 k. What do you mean by custom table option?
there is nothing like that can convert the numbers into words. There is no custom formatting that convert number into words automatically. There is some VBA there is a VBA coding that is required to convert word
convert numbers into words. Okay. required there is nothing in here that can convert this into words there Swati. Okay how much sales we have I will go ahead and sort it by sales.
everything making the bills I will go ahead and sort it by date. So it is all about the priorities. Absolutely correct Rachit Ibrahim absolutely correct.
let's go ahead and sort everyone how do we sort it we do have sort in A to Z. So date. To sort here we are not going to sort it by selecting the whole table. We will simply keep our cursor on date
We go to data tab here and we click on A to Z. When we click on A to Z here we see a sorted date column. So sorting like this is easy. Wonderful.
Like this we can keep on clicking on any of the number, any of the column here and we can simply go ahead and sort it either in the ascending order or in the descending order. A to Z or Z to A?
a standard. And sort is always the first step. As soon as you get the data, once you will give the proper formatting here and proper number formatting, next is you start sorting it.
So if your data is sorted chronologically, that's fine. It still it by multiple columns just to understand your data properly. applying the filters too. So let's see
few more types of sorting ways to sort here. So right now we have sorted by date everyone, right? But look at this here, we have got product and category, we have got region
and salesperson. So if I right now go ahead, click on the category for example and clicking on the category here for example and I sort the category right now. Now these are electronics here, right?
Inside the electronics, I would like you to pay attention right now to all the product name which we are selling under the category electronics. Inside this category here, electronics is properly sorted. However, if you look
is properly sorted. However, if you look at the product right now, laptop. It is written here but not together. So how about, how about that I want category to be sorted, that's fine.
However, inside the category also I want the related product to be sorted too. first is E is coming here up, then B is coming up. We want this also be to be
coming up. We want this also be to be sorted but inside the home goods here. So this is called as multilevel sorting or multilevel sorting. Okay, so we are sorting it but multiple columns at once.
So let see how do we go ahead and do the multiple level sorting here. inside anywhere. Okay. Keep your cursor anywhere inside
here. Just keep selecting one single cell and click on sort box. Click on sort box. Where is the box here? This is our sort box here.
This is our sort box here. So let's click on sort box everyone. In this the first level is category. In this the first level is category.
data has the headers even though data is not selected it will select by itself. You can see easily that when you are selecting inside you are clicking inside this particular box your data is automatically getting
selected without the headers. In the sort by here if it is empty you can go to the drop down and you can see the list of all the columns here right? Go ahead and select category right now and currently the order is A to Z. We
We can go ahead click on add one more level here. In the second level here I we want product name to be here. Product name is also sorted in ascending order A to Z. We are fine with that too.
here. If you want more levels can be given too. So right now let's give two levels here category and product name and click okay here please. So go to the sort box first is category
then is product name and click okay and look at the output please. If you will select just one column you are your whole data is now compromised.
That's why we simply click on one single cell that is the header there. header and sort it the whole table is sorted by that one column. You don't change just one column. If you change the one column, okay, your whole
data is ruined right now. There is all information is totally incorrect. On this January 10th, this is the transaction that happened. But if you will simply select only the column here and you sort it out, every
none of the rows are getting sorted with that, then you have an absolutely don't do that mistake. That's why I said only select one single cell. You can see right now inside the
electronics here, inside the electronics here, and then S, and then W. So meaning here also, within here, this is also properly
sorted. Within home goods, everything is properly sorted. So this is here multi-level sorting. This is here multi-level sorting.
multi-level sorting for these three columns here one by one? here. First level should be for region. Second level, we want salesperson to be sorted within each region. And sales amount, show the highest amount,
show the highest amount in each region then. Remember that when I say these three columns, the whole table should be sorted properly. Okay? So your cursor can remain anywhere, but I want these
can remain anywhere, but I want these three levels to be there. Pragati, I am going to solve it right now. box here. Now I we don't want these two levels
here. We don't want these two levels here. How do we delete the levels, To delete the level here, we simply select on the level, Pragati. Okay, open the sort box, keep the cursor inside anywhere.
inside anywhere. Click on the sort box. level, select the sort by, delete the level. That's how you can delete the level. And with plus here, add the level.
like region because this is the first level we want A to Z. We go to the drop down and we select salesperson here. We go to the drop down and we select the sales amount here.
Now the sales amount you can see it is smallest to largest. It is smallest to largest here. So we change this into largest to smallest amount first. So we go to the drop down here, we
So we go to the drop down here, we change it to largest to smallest here.
No, Ibrahim doesn't resort accordingly. You have to sort it again. When I click okay here, look at this here. Within Europe, these two people are there. Highest amount is 0.9 here.
Within North America, Alice is there. 1.3 Within Carol, 1.4 and likewise. So now there are three levels again. Okay, there are three levels here. So this is how the multiple sort works.
Now let's go ahead and click on let's go ahead and see the filter everyone. Now we have I am sure everyone of you have already used filter multiple times before, okay? So we will go ahead through examples of
filter here. Let's go ahead and click on filter. Apply the filter everyone. Now you can apply the filter options here. You can go ahead with control shift L.
That's one of the shortcut keys. There is alt key series. Alt key series is alt AT here in the newer versions. Okay, in the 2007 and previous version, alt key series will differ. I think that is alt ee something alt dff
okay so the previous key here is alt dff. So those who are been working for 10 15 years in excel they remember this here alt dff. So this is few of the shortcut keys all
right. Now what is alt here? Whenever we go ahead with control shift control shift and l or any shortcut key that comes with control shift l it is always has to be
pressed and hold together. However what is the alt key series here alt key series has to be typed one after another. It has to be typed one after another we don't hold it here.
So what is the alt key series here to talk about the alt key series here most of you are already aware. However I want to go ahead and show it off here that what's an alt key series because there are multiple shortcut keys
that we find it like this but you can make your own shortcut keys if you understand how does that work here. So this is the keyboard everyone okay my cursor is here inside I'm going to press alt right now.
So I'm keeping my cursor anywhere inside the table I'm pressing alt right now. See press just once and left it just press it and left and immediately observe that in
and immediately observe that in in here I have multiple letters. Look at your quick access toolbar. If I right now press alternate I can
this is the shortcut that I have created for myself here. For the quick access toolbar I have got numbers. If I want to go into the home tab right now I will press h
The moment I press h here now inside the home tab for the whole ribbon now immediately I can see all the letters right here. We can go ahead and access anything that we want here. Just keep going inside it
and keep clicking on it. I want to go back and move to the data pan. I will press on the escape button. See here, I press on escape. I'm one back, one step back. I want to go to data, I
will press A. I'm inside data pan. I want to remove filter, I will press T I want to remove filter, I will press T here. I want to apply filter, I press Alt here, I press A here and I press T here.
Filter is applied. That's your Alt key series. That's your Alt key series. And that's how it works. Most of you were aware about that. Those who are not, this is how it works.
So if you are want to work with the shortcut keys, that's how you do it. All right everyone. So we will move forward. I hope everyone has applied the Now I'm going to ask you some questions here. Please go ahead and answer me.
So it's a very small dataset. Okay. So can you go are we good till here? Yes, it works in all Excel versions, Vivek.
responses everyone. Let's move forward. We go to the dropdown here. Okay. In the dropdown, we have got number filter. In the number filter, we have general filters like equals to, not equal,
between, less than, greater than. We do have top 10. And we also have above average and below average. The beauty of this filter is we Absolutely not. You have to simply go ahead and ask you
It will calculate the average by itself. It will filter it out itself. it. When we click on above average here,
okay, it filters it out. Now how do I know how many rows are there? We don't need to count it because at the left hand bottom corner of your screen left hand bottom corner of your screen here, it will write six of 10 records.
here, it will write six of 10 records. So you don't need to count it. At the left hand bottom corner, it is writing six of 10 records here. One more observation everyone. Okay, you can see here right now the rows here
observe here rows are in green color and they are okay because it simply hide the rows here. Now you have a subset in front of you. from someone else that someone else is working on that Excel and they have
shared it with you, always go ahead and check the row numbers here. If you find the row numbers hidden like this or in green color okay or in green color, it means that your data is filtered out even though
your data is filtered out even though the filter is not applied. It will immediately give you an answer that okay by some column it is filtered that okay by some column it is filtered out.
drop down here, I go to number filter. Okay, see it is calculating above average. There is not it is above average. It is calculated average and accordingly it is giving us the answer.
So if you go ahead and calculate the average or maybe 0.9 is the average. transactions from February? So this is date filter. Okay, we go to
We go to date filter here. Now in the date filter currently it shows we can simply uncheck and check. However, we do have multiple options like today, tomorrow. You can also go to all dates in the period and we can
filter it from here too. We can filter it from here too. along here. We can click on February here. You can
can check and check too. However, the correct way is to go to all here. And yes, there are only three transactions for February, and it will give you three transactions there.
Let's clear the filter. We are in back here. So this is about the sort and filter, everyone. here, we are always applying the filter for one single column, correct?
How about we can also go ahead and apply filter in multiple places or multiple columns here. So if you want to apply filter in multiple columns here, how about we filter out here? Can you tell me in North America?
here? Can you tell me in North America? Can you tell me in North America? laptops we have sold in North America? How many laptops we in North America? How many laptops we have sold?
too. Remember to clear the filter. everything. We check on North America. We click on okay here. And we go to product name.
We go to text filter. And in real time contains laptop because we have no idea how many laptops are there. Okay? So we usually go to text filter contains and we write laptop. Now it can be any laptop.
That's correct. In real time, always go to contains a In real time, always go to contains a laptop. the filter and here. Moving forward,
let's say we have a huge data set right now and we don't want to see all the columns at once. We don't want to see all the columns at once. So we can actually go ahead and hide few columns, and we already know how to do
So we can select few columns like this. For example, I simply want to see the salespersons. I don't want to see category and region right now. So I can select these two columns, right click, and hide them.
When you hide these columns here and you share it with someone else, if they are not paying attention because you have 30 40 columns in one table and if they are not paying attention to the name of each column a b c d e,
they might not know that some particular column is hidden here. are hidden, they will never know that because rows maybe somewhere 100 and 120
simply filtered it out. So they are hidden. And if I want to unhide this here, I have to be very precisely go here, right click, and then I will unhide it. And that too I have to unhide it one by
one, unhide one by one. Right? So this here becomes counter effective. And we might hide it and we forget about it that oh there were some columns like that.
So in that case for management of a huge data set and to choose which column would I like to see right now, there is something called as group by. So where group by is present? Group by is present in data here in data tab
in data tab here towards the right hand side under the outline group. This is group by. This is group by here. So you can group the group it here, we can ungroup it and there is one more
option called as subtotal. So let's understand what the group by is. You can group by rows and columns both, okay? You can group by rows and columns both here. And you can choose which to hide, which
So let me show you a demonstration how does it look like and later on you can go ahead and follow along with me too. It's very easy to apply. So I will go ahead and select category and region again,
category and region again. Instead of hiding my columns here, we go to data, we go to outline here and ask it to group it. I will directly click on group it. I will directly click on group.
observe the changes. Immediately when I click on group all both the columns here you see a single dot has appeared, a minus sign is there, a collapse sign you can say. And only left right hand left hand side here
we have one and two. What is one and two? Two reports are created. If I click on one it will collapse. So this is the report that we have right now. If I click on two, this is the report we have right
So this is one, this is two. This is the reports we have here. In the similar manner we can go ahead here and put rows also. So for example, okay, I
want to go ahead and group I want to go ahead and group Asia first two rows here. So if I'm selecting the first two rows here and we'll go to outline and group here
we will again have two reports here. You can click on one One and two. We have the clear indication that how many number of columns and how many number of rows are currently getting grouped.
So we have an idea that where when the rows are hidden, when the rows are viewed. So the it is simply hiding it from the view here. However, we have a from the view here. However, we have a clear indication too.
Now within group by here there you can have multiple groups also. You can have multiple groups out here. Right? For example, I can go to rows here and I'm picking up last I'm picking up here
electronics for example I'm picking picking up the electronics here. These three rows we can go ahead and group these two and now you can see I have got two parallel groups. It's still two reports. One, everything
collapses. Everything everything expands. Everything collapses. expands. Everything collapses. Everything expands here. However, you can also choose to have group within the group.
Now, we can also have group within the group. For example, right now this is C and D and grouped together, right? Maybe I want to go ahead and want to look at region but not at category.
So, we can now inside the group itself, we can select column C here we can select column C here and ask it to group it again.
The moment you have group inside the group, now we have three reports. First report is where everything is collapsed. only region. Third report is where I am able to see
Third report is where I am able to see each and every group. like this also. You can have multiple reports here. It is easy to access and easy to easy to manage your data. Group by is majorly used for
columns back. Basically for data management. Basically for data management. Go ahead and try this out please.
Okay, we're talking about filter bank then. I will remember that. Okay, please go wait till the break. We are about to take a break okay in 10 minutes. That's where I will ask you to share the
screen and I can help you out both, okay? In first five minutes, I will go ahead and address both of your doubts.
All right everyone, look at and see at the group by please. click on group by. You select any bunch of rows, you click on group by and you have multiple groups and reports here which are hiding and un hiding the
which are hiding and un hiding the columns and rows for you. yourself? Very easy. Select and group by.
expand one, two, three click. Are you able to do that? Once you're able to do that, just let me know please. Very easy here. Let's group by please. Very easy here. Let's group by everyone.
And also this we cannot name the groups. It is just for the user interface here. okay, select at least two columns here for example and after two columns simply
select one single column. If you will select just one, see this is the first line, right? Inside this first line I have category and region. line I have category and region. So I simply select category to group.
Then you will have group inside the group. salesperson. That doesn't mean salesperson is the part of the group. salesperson is the part of the group. No.
part of the group. Absolutely not. It means that it will bring the It means that it will bring the salesperson here if I click on minus. With the region and category are inside the group.
happening. Trying to hide this and thinking that it is not grouping it by. So no, below the minus sign this is something out of the group.
So this is the group by everyone. Okay? Now I would like you to please go ahead move to the sheet two here. It is the same data set that we have It is the same data set that we have here. Okay?
Let's move on to some other data set where we can see the subtotal. Okay? For that, we do have
everyone, okay, we will go ahead and open up another file here. customer purchases. Got it Anu? Okay, wonderful. Thank you. There is another file called as retail purchase here. Okay, we will start
upcoming topics we will learn within this retail customer purchases CSV here. It's a comma separated file everyone. It's a comma separated file everyone. And this is how it looks like.
work in this Excel sheet here. And this is how the retail customer purchases looks like. It's a simple comma separated values. It's a simple text file. However,
you might see that it has an icon of Excel. But I would like you to observe here. Okay? See, this is an Excel file extension is .xlsx. This is an Excel file extension is
.xlsx. However, this is a CSV file. The extension is .csv and see the icon is different. It says that it can open with Excel, but it says a and comma.
Meaning this is a CSV file. It's a comma separated value file. Always observe the icon. Icon is a little bit different. It says a and comma. Okay? So when you will go ahead and
double click on this file right now, download this please and double click. on plus and minus or one, two, three and
because the indicator is there. That one, two, three indicator is there. Group inside the group means I want to create I want to create multiple hidings. I don't want to hide them together and view them together.
Few things I want always be hidden and few things I want to view the sometimes. So based on the objective you decide. That's why we have group by group.
When you see the subtotal, you'll have the group by also very much cleared up. So this is the CSV file everyone. Let's double click on the CSV file. Let's double click on CSV file. It opens up in Excel.
When you double click on CSV file, it opens up in Excel. It opens up in Excel and it does look like this.
a possible data loss. Reason is it is in CSV format. convert this into a proper Excel format. request all of you to please click on save as
and save this as an Excel sheet. Click on save as everyone. When you click on save as, the save as dialog box will come up, right? Now go
ahead and save this file here in Excel format. How do you save in Excel format? the type. In the save as type here, you select the Excel workbook. So make sure you go to the drop down here and you select Excel
the drop down here and you select Excel workbook here. box is opened up, right? You go to the save as type drop down here
and you select Excel workbook and you save it. Now you will find that your CSV here has been changed to Excel file and now we are able to work within it properly.
Done. So again, give it a few moments. So again, give it a few moments. It will appear. save as, just go to file and save as.
just go to file and save as. Just go to file tab and save as. And here we have retail
in the Excel. Once you're in the Excel sheet, okay, right now to Excel here. Okay.
good. You can give me reactions. You can write in the chat box. everyone. Wonderful. Great. Thank you very much for the responses there.
Now let's see here. We have hashtags. So we remember why do resize it. I'm going to select all the columns here and resize everything. Resize here.
Okay, I'm trying to keep the size exactly similar. Okay, this looks quite good. Now what is shape of the data everyone? What is shape of data? What is meant by the term shape of data?
It means how many rows and columns do we have? It simply means how many rows and columns do we have? That is shape of data. columns do I have here inside this data set?
Okay. Control shift and right arrow key. This will tell us on the right hand corner that the count is seven. See here on the right hand corner here. It will tell us that there are seven
columns. If I do control shift and down arrow thousand. So we have nearly thousand rows of data and seven columns here. Okay.
So the shape of data here is thousand and in thousand into seven. and in thousand into seven. Yes, very good. That is the correct way of having a data set.
data set. Not everything is supposed to be aligned to only one side. Okay, that is what we think that a clean formatting is when everything is aligned to one side. Absolutely not. A clean and correct formatting is when your numbers
and dates are on the right hand side and all the text is on the left hand side. that's how you make sure that everything is properly here. I'm going to okay. So this is here we have a data set.
to first let's talk about few things. For example, there are few things that are coming up. For example, between Excel table and range table. Okay, this
because so we'll understand the difference between the Excel Excel table and range table first. After that we will move forward. Okay, subtotal here. Subtotal here.
move forward with conditional formatting, conditional formatting here. And after conditional formatting we will talk about the functions here. Right? Now I would like you to create multiple
So how would you create a copy of this Just let me know. Don't create it. Let me know how will you do it? I want you to create multiple copies of this data set in multiple sheets.
How will you create copy here? Create copy of the whole sheet or you add one more sheet and you will copy and paste the whole data type there. Right? So usually what is done here is
either you are going to select all, copy it and you open up another sheet and you Either you are going to do this, but this is not good because while you paste it every formatting that you have done is absolutely gone. So it
is not a very good thing to do. Correct? Now another is to create a copy of a sheet. So how do we create a copy of a sheet? Oh, absolutely correct. We go and do a right click here.
Right. We go into a right click here and we go to move or copy. So we go to sheet here, we right click here, we go to move or copy. Now in this move or copy here, this is a fantastic option right now because we
get to move or copy this particular sheet, the whole sheet in any of the files that are currently open. We can also choose to copy it or move it in entirely a new Excel workbook. So it is a fantastic option. Currently
purchases, so we will stay here in this workbook. That is fine. And we like to move it to the end. However, we have to remember to check on create a copy.
whole sheet. And we don't want that. So to the book, we get to select any existing open workbook or a entirely new or copy it. And we remember here to check on create
a copy and then we create a copy. Now this is something Now this is something that we have been doing so far. nightmare because every time you are right click it, create a copy, right
right click it, create a copy, right click and create a copy. click, move to end, check on create a copy and click okay. That's how you have been doing so far. Correct. And it will create a copy for you and
will write two. Let's see another method to create a copy that to multiple copies that you want within a second. Within a second. Let me show you how. So do you see the key keyboard right
now? You must be able to see the keyboard here. Okay, okay. Now see what I'm going to do here. Look at the keyboard right now.
My cursor is here. Right? Where is my cursor on the sheet. Now I'm going to go ahead and press here shift. So this is my cursor I am going to left click and hold first.
Now I right now I'm left clicking and holding it right now and here The moment I do that see here the moment I do that control and left click you
will see that at the back of the cursor there is a page and on that page there is a plus sign. When I drag this here when I drag towards the left here towards the right side here and I leave
control and left click. I have got two copies. left click and hold drag and drop.
create multiple sheets control drag and drop. drag and drop.
now. Go ahead and try that. It will take a little bit of practice if you have got a simple mouse here it will the touchpad or something you will feel a little bit difficulty there
because it requires control left click and drag and drop. to create copies of a sheet. It might need a little bit of practice too. We need only few here
few copies here so I will delete few copies right now. So I will select them and delete them here right click and delete everyone we all know how to delete a sheet.
now? Just have one or two copies. That's it. first sheet to a subtotal. I'm renaming the first sheet as subtotal everyone.
How do we rename the sheet here? We can go ahead and right click and rename or we can simply double click and rename. Rename the first sheet sheet as subtotal
and when you're ready, let just let me know. here. You should have renamed the sheet as subtotal and when you're ready, do as subtotal and when you're ready, do let me know.
less than nine, okay, you have to let me know that how we can go ahead from our know that how we can go ahead from our side and make your experience better. So you have to let me know the area of improvement here.
have the exact same experience every day. Wonderful. So we are moving forward here everyone. Let's see what is subtotal here. But before we go ahead and learn about subtotal, there are few things
that I would like to discuss. Whenever I talk about let's format it, most of the experienced professionals are saying, why are we not converting this into a format table or sorting and filtering is a lot more easier if we
convert this to table, right? So let me show you here what is meant by convert to table. Currently this particular type of data set is called as a range table in Excel. Listen to me very carefully. Nothing to
follow along. Okay? So this is a simple range table. If you want to format it, we have to format it manually by following the options inside the font and alignment group and number formatting. Fine here.
However, there is an option called as format as table. So if I keep my cursor inside and there are no blank rows or blank columns or blank cells around here and I go to format as
And I want to format it. All right, I will go ahead and select any of the formatting that I like here. Currently, I'm selecting a simple format like this. Once I select a format here, once I
select a format here, it doesn't only color everything. See here, read again, read again. First is it do Meaning it is able to understand where
your data is based on your active cell. Now it is asking you create a table. Didn't we have a table before? No, that was a range table. We assume that as a table. Excel doesn't
We assume that as a table. Excel doesn't understand that as a table. data of your table? This is my data. It is automatically selected the headers? Yes, we have the header here. So this is
automatically checked. Now observe the differences. Observe the differences the moment I click okay. The moment I click okay here, observe the differences everyone. Yes, you are
absolutely correct. You be this is now properly formatted. The colors and the properly formatted. The colors and the borders are given. please observe one more thing here. We have one extra tab opened up, which is
called as table here. It can call based on your version, it can called as table tools. It can be called only tables Now when it is the table tab here, it means right now this is an Excel table.
means right now this is an Excel table. By now this is an Excel table here. table. Okay, so I'm going to name the table as I'm going to name the table here as retail data. While renaming it, we will
the first should be letter and there should be absolutely no spaces. You can use only underscore here, which I am not using at all. I am writing one single name retail data. Now this name that we have given to the
table, this can be used inside any function. You can simply recall the function. You can simply recall the whole table by giving the name there. That's one of the advantages. Second advantage here is observe your
Second advantage here is observe your data set right now in the sheet here. Filter is automatically applied. Filter is automatically applied, right? borders and colors are given. These borders and colors can be easily
borders and colors can be easily controlled by the check and check here. we have rows border here. If I check on banded columns, we'll have the column wise borders too.
If you don't want the filter button, you can check and check filter button. can check and check filter button. This has been added later. the filter button. What is first column and last column? If
you want to highlight the first column and you want to highlight the last column, you can check on that also. You want to add a total row at the end, will be added. You will uncheck, total
row will not be added. And total row will be added only to the last numerical column. Total row, observe that if I check on total row here, it gets only added to the last numerical column, not for the
Okay? I'm unchecking the total row here. table right now, what are the other advantages? Other advantages are if for
example after purchase frequency here, I want to go ahead and add a calculation here, any calculation here. Let's say I want to go ahead and give a we have given them a discount. If we are in a range table, if I write
discount here, I have to again select everything, give the color, give the However, while we are in the Excel table, I type discount here and press enter, it this automatically becomes a
enter, it this automatically becomes a part of the retail data table. of formula here, okay, things look
absolutely different. So if I type equals to right now, okay, that this is the purchase frequency four. So I want it to go to revenue. I wanted to click on revenue here and let's say
divide this by four. Divide this by four. Look at how my function is looking like.
It is not a simple function. It is not saying that D4 divided D2 divided by J2. No. It is writing altogether there a different referencing here. And the moment I will press enter, the
moment I will press enter here, I don't need to double click drag and drop. It will automatically go ahead and apply the formula for everywhere else.
Excel table looks quite good that oh everything is working fine. I don't have to do anything else here. However, in real time what happens, it has several limitations to the usage. You cannot do V lookup.
V lookup. V lookup will not work. functions that will stop working in Excel table. That is why whenever use Excel table, right?
Whenever you have used Excel table here, okay, we always go back and convert it back to range. If you want to use Excel table simply for the formatting purpose, right? Once you format it, go back and convert it to range table.
So keep your cursor inside, keep your cursor inside here and find that inside the table table tools or here, this tab here, okay, there is an option convert to range. Just keep your cursor inside and then
Keep your cursor inside the table anywhere. And click on convert to range When you will convert to range here, it will ask you, do you want to convert the table to a normal range table?
formatting will remain intact. However, the limitations will go away. Sort and filter everything will work absolutely normal.
is used for formatting purposes. See here, now we are back. There is no table button. If I go ahead and add any new column here, nothing happens. The this is this remains like this. If I add a cell referencing, it's a
normal cell referencing right now. I now have to drag drop and double I now have to drag drop and double click. limitation. This is the difference between an Excel table and a normal
range table. Yes, there is an added filter. Absolutely not Abhishek. Because of incorrect cell referencing.
lookup. So when we learn V lookup, we will understand that why it cannot be When you will learn about cell referencing, you will understand that okay, why the cell referencing was looking very bad when you were working
looking very bad when you were working with the control T table. And yes, the format as table shortcut is control T.
table. Format as table. So let me show you here. Okay, how does format as table works? Just by giving the border it doesn't get converted. the border it doesn't get converted. Okay?
limitations when we will reach to the functions. Until we don't reach to the functions, okay, just iterating limitations here will not work because currently most of you have worked with the functions, but you have
referencing. Without learning the logic of cell iterating the limitations multiple times will not make you understand that. So let me reach to that particular point where I will tell you about the cell
cell referencing point, you will understand that why functions cannot be understand that why functions cannot be used. It's okay Ganesh and Abhishek. That's the reason. Stay there
with me and we will see why some things cannot work within table. So right now we will this is about the control T everyone. I have changed it range here. I will delete the discount here right now.
So this is a formatted table right now. Okay. Make sure that it is properly in the range please. Let's go ahead and apply subtotal. Now what is a subtotal everyone? We'll stay in this sheet here. Make sure
We'll stay in this sheet here. Make sure it is a proper range table here. range. I'm repeating that again. Okay. Now what is a subtotal here? Subtotal feature here, for example right now let's understand the data first here. We
have customer ID, product category, purchase, revenue, their age, region, purchase frequency here. Okay. Great. Great. We are good with that. Now in the subtotal here as the name suggests, okay, we can go
ahead and create we can go ahead and create reports here? Maybe I want to go ahead and calculate product category wise
total revenue, product category wise total revenue. total revenue here. So if you want to go ahead and create a So if you want to go ahead and create a table like that, okay,
How? The prerequisite for subtotal is, prerequisite of subtotal is Okay. Sorted by
columns. So whichever by whichever column you want to calculate aggregate the value by that particular column you have to go ahead and sort that
product wise so we will go ahead and sort the product. If you want region Okay. But before we go ahead and sort, let's because we can see there are too many decimal points here.
it. To format this here you can we can select the whole column like this, right? And we can change the formatting However, I would like all of you to show you some keyboard shortcuts again
for number formatting. Okay. Again, I would like you to pay attention. I will write the shortcut keys for you, but for now I want you to pay attention on the screen. I will give you enough
time. Don't worry about that. But right now what you see here, this is See the formatting part here. This is general, correct? If I go ahead and press control shift and one
It's the number format. Keep on looking here. So you have to see two things right now. You have to see the number here. You have to pay attention on the keyboard also and see the number, see the
Okay. Now if I do control shift one, it's a number. If I do control shift and two it's a time. date. If I do control shift four,
I have applied the default currency. If I go with control shift five, it's a percentage. If I go with control shift and the epsilon here, okay, that is the key just before the one
before the one then I'm back at the general. So control shift one is a number. Now I will write that for you.
number formatting. I'm going to write that for you. Okay. See here, control, shift and one. control, shift and one. Control shift in one gives you number.
Control shift three gives us date. Control shift four give us default currency as per your system. Control shift five will give us percentage. formatting. And yes, the control shift,
this key here that is just before the one, this will get us back to general one, this will get us back to general formatting. And it stop at number it stop at number formatting
please. We want only two decimal points there. know. We will go ahead and apply the sorting function. Sort first here.
Wait wait, you can select people number column also, but it has to be a number.
two and control B? Control B will give you bold. And control shift two is going to give you the time here.
time here because there's the at the rate sign there. And control B or control two, it is going to give you the bold here only. There's no other difference. So I don't see any difference right now.
So I don't see any difference right now. Control two is also giving control B. Yes, control B and control two simply bold whatever you have somewhere.
difference between the two shortcuts okay. highlights the it is here okay. If I go ahead and press control two this also
highlights the same thing. However for number formatting we are doing control Okay. All right everyone number formatting again it takes a little bit of practice but you can do
You don't have to remember anything okay just look at the number on the keyboard. Okay they automatically give you the hashtags the currency sign the dollar So it will help you to remember. Okay.
And don't try to wrote it out. Okay Okay and it will come by practice. with subtotal here. Shall we move forward?
asked to slow down a little bit. And control keys here are the control keys these here. Let's move forward with the subtotal everyone where is the subtotal subtotal
is inside the data tab and just near the in the outline group we here we have the subtotal. But the pre-requirement of the subtotal ahead and the format and create the
report from it should be sorted. So currently we want the product category by product category so go ahead and click only on the product category header. Okay and simply A to Z.
Okay and simply A to Z. Just sort it. You will find that there are thousand rows it will be sorted.
Now sorted the product category now we go go data here go to the data here. Inside the data we go towards the right hand side and we click on subtotal. We group and we click on the subtotal.
When we click on the subtotal here a simple dialogue box opens up. We will read again here. It says that at each change in customer ID.
at each change in customer ID. Now So at each change in product category use a function sum.
Use a function sum. Where we want what we want to sum up Purchase frequency? Now we want to sum up revenue here. We want to sum up revenue here. So we
will uncheck purchase frequency here and we check on revenue. We uncheck this and we check on revenue. So what it is trying to say here? It is saying that whenever there is a change in product category, for example, books
will change to electronics. As soon as books will change to electronics, it is going to add one row there and sum up the revenue till that point.
everyone. Go ahead and do the settings here. Make sure we have Okay. Sum will remain sum and we make sure only revenue is checked.
Yes, you can check multiple columns too. Right now we are checking only one just to see an example here. And yes, you're absolutely correct. We do have multiple aggregate functions and statistical functions here.
For this example, we are looking at sum of revenue by each product category. Do these changes, click okay and tell your observation about your sheet.
to select the whole table. So it's better that you go ahead and use the sort box there. Even for the simple sorting, okay? And
please don't and another reason you're getting that pop-up because you are either you're not selecting just one single cell here. the whole column. So of course it is going to ask you to expand the
selection. Swadhin, please remember never ever compromise your data like that. Please select just the header to sort your sort your column.
Because if you select the whole column, okay, the rest of the data will not move okay, the rest of the data will not move according to the product category.
the header. Because this customer ID has purchased the book on this date and generated this revenue. This whole row has to move with that particular header.
header. Okay? Ganesh and Swadhin. because you're selecting one of the columns there.
If you will simply select one of the cell, subtotal will be active. is how the subtotal looks like. What is your observation for subtotal everyone? okay here. Tariq, Tarif and
Sarika. Give me a moment please. Yes, yes. Give me a moment please. Why you have not converted it into Excel? Have you converted it into Excel sheet?
Have you converted it into Excel sheet? Does your name says .csv or .xls? Does your name says .csv or .xls? Check.
If it is, go to file here and save as Excel. right now what has happened once we click on the subtotal and we click okay. click on the subtotal and we click okay. Please observe here.
We click on one. We have grand total report. We click on two. We have product category wise revenue. When we click on three, we have actually doing here is if you go to the second report, you will find that it has
Okay. So what it is actually doing here is this. As soon as a sports is done and is adding sports total, And as the home decor changes to sports
here, okay, it is adding a home decor total here. And when we ask for a second report or a third report, okay, it will go ahead and give us the filtered report like this.
filtered report like this. So that's how subtotal works. ahead add add the rows. And do you see it is grouping the
rows? That's it. Can you control A? Select the whole Can you control A? Select the whole table first.
subtotal at all here. Your table is still an Excel table. In an Excel table, you cannot apply subtotal. Okay. See the tab there, table tools design. Can you click on that, Tarik? Last tab,
Can you click on that, Tarik? Last tab, design tab, table tools design tab, tab. You are in the ribbon, Tarik. Please go to the tab, data tab, review tab, view tab, design tab, tab, tab, tab, last tab, table tools
tab, tab, tab, last tab, table tools design tab. on that, please? You are still in the control T tab. It will not work for you. And this goes out to everyone who is
writing multiple times that so nothing is working for me. Subtotal is not it to range. Convert it back to range. It is one of the limitations. It is one of the limitations. Tarif, click on convert to range here.
Tell me that. What is the issue? Double click on design tab. Stick to that. Again, you have the tab, right? Can you click on convert to right? Can you click on convert to range, please?
You're absolutely not comfortable using the laptop or the software, absolutely not. It should not work like this. You need a lot of practice. You are not looking at ribbons. Same one
double click on If you are not okay with that, right click and expand and collapse. How does subtotal works? Okay, subtotal works how? It will wherever the books will see we have
sorted by books, right? We have sorted by books here. So as soon as the books will change to clothing here, it is going to add one more row and add the that particular number for books.
How to apply subtotal? Everyone, please follow with me here. outline tab here and we click on subtotal. We click on subtotal. When we click on subtotal here, okay, we
have sorted product category. So at each change in we select product category. sum the revenue. And everyone else this time we will check on purchase frequency also. So there is two numbers here, right? So we
want to sum up revenue also. We want to sum up purchase frequency also. And we can go ahead and click okay. Go ahead and do that everyone. Everyone who was asking to repeat this I have repeated this right now please go
ahead. And do the here here Pragati right? Go ahead and do that again please. It's a simple report creation.
if I have a date column? Date A to date B subtotal of revenue. whenever date will change okay it will change it.
What we can do is you can extract month and by month you can go ahead and and by month you can go ahead and subtotal it.
entries and you simply check on purchase frequency we don't need to remove it. Go and click inside subtotal again everything will be here okay.
here and see here first report is grand total second report is simple total report here when you click on three is the entire report.
So when you click on two here you can see right now we have revenue total also. Great wonderful.
And you ask your manager to click on one two three to view the report. Or will you copy and paste it out?
Swati will you think manager will do that much to look at a simple report? that much to look at a simple report? Find where the report is.
So how do we copy and paste? We should have an extra sheet, okay? We should where we are going to paste the report, where we are going to paste the report. Okay? We will see that too, Rachit, one by
We will see that too, Rachit, one by one. We'll see that too. Uh sure, Ritesh, now I will help you with that. not the com it can do combination of columns, but it cannot do combination of
the aggregations. If it is giving you sum, it will give sum of everything. It is giving you average, it will give you average of It cannot do combinations of calculations.
session, okay? But I need you to share your screen and I don't think that's possible for you, right? I will try my best to help you then.
if I go ahead, okay, and try to copy this report, I go to paste report here and simply paste it, I get the whole data. I don't get the report here. I don't get the report here.
Look at when you select and copy it, look at your green dash line here. This green dash line is actually selecting the entire data, even the also. Right?
So what is our objective right now? Our objective here is to select to select only visible cells, to select only visible cells. Okay?
So whichever cells are visible, only that cells we want to copy here. everyone? There are two ways to do it. There are two ways to do it. First is a keyboard shortcut and another is the interface
Okay. So first is the keyboard shortcut here. What is the keyboard shortcut? Keyboard shortcut is Alt and semicolon. Keyboard shortcut is Alt and semicolon. How does this Alt and semicolon works?
How does this Alt and semicolon works? So first you do is you will select your data to be copied normally. Select data After selecting data normally, we go ahead and we press Alt semicolon
and then we click on Control C and then we go ahead with Control C. Let me show you here. I'm going to share it again
with the keyboard, on a screen keyboard here. keyboard, on a screen keyboard here. See very carefully once more here. aware about that. Okay. Most of you will already know this. So see here, I'm
simple selection done. Now I'm going to the keyboard here. In the keyboard, I'm pressing Alt and then semicolon here.
Okay. Now when I control and see, each and every line is is separately selected. selected. Now if I go to paste report here and do
the control V and paste, only this much will be here. Okay. Only this much will be here, copied and pasted.
actually coming from? Okay. Where does this all come from? So it comes from go to option here. So in the home tab, is a very very interesting option is there which is called as go to option.
It will come in handy when you will be managing huge data set. Okay. It comes in handy whenever we use a huge data set. in the home tab here, when you go to home tab and towards the
right hand side here, there is a magnifying glass or you can now call it a search option here. here, you'll find
here go to and go to special. Okay, the shortcut key is control G, So when you go here and click on go to special, see here this is the selection option and they are wonderful. They work wonderfully here.
looking at, okay, you can go ahead and select only formulas in the whole sheet immediately. You will know where the formulas are applied. You can go ahead and select all the blanks at once and you can make them
that they are blank. You can also go ahead and select the You can select the visible cells that we just did. You can select all the conditionally formatted cells and wherever the data
validation is applied. So whenever someone is sharing the sheet with us, we have absolutely no idea where these things are, how many blanks are there, where the formula has been applied. This go to special here
applied. This go to special here combining with the font styling will simply help us to highlight all these different kind of cells. So select from here and highlight from here. It's a fantastic option. Use that in
your work. Okay, you will find it quite handy. you can go to go to special and select visible cells from there and again copy But yes, Alt and semicolon comes in handy for copy and pasting to for
visible cells only. Okay, so that was just a selection there that was with the subtotal everyone. Okay, so that was subtotal. So let's go ahead with the another copy here. Please move forward with another
copy here. That was all about subtotal. Okay, you can have multiple subtotals. and region. Then you can have region wise, category wise total revenue. So you can go ahead and have that kind of subtotals too.
The problem with sort, filter, group by, subtotal is currently Okay? You want to get back, you want your data to be back. If you want your data back, you have to go to data tab again. You
have to go to outline again and click on subtotal again. So you have to keep a cursor inside first. You have to go to the outline drop down and click on subtotal here. In this subtotal here, you have to
If you click on remove all, it will remove everything else. Okay? And we click okay. Now your data is back.
copy and paste the required report outside and then you get your data back. And all of these will be editing your data. So you have to be very very careful. Now before I move forward,
till here with the shortcut key selection, copy and paste. Yes? Remember that if you're not able to do it right away, because it needs a do it right away, because it needs a little bit of practice.
little bit of practice with the video. Just practice it again. Do not click on anywhere on your screen while you are using the shortcuts. All right, wonderful everyone. So that was about the subtotal here. I'm
going to control Z and apply the subtotal back again. Now in this subtotal here, yes, I'm going to go ahead and apply it
back again. Let's go to the another copy here. going to look into conditional formatting. We will look into formatting. We will look into conditional formatting here.
Conditional formatting. Okay? Since we are going to color as per our data set, we are again not going to convert this into control T. Because I will show you again here. I will show you again here. If I convert
this into control T, that is this table here, okay. See the options that will not work. See the options that will not work. So I am at the home tab. Everything is working. In the insert here, everything
is working. We go to page layout. We go to data tab. Multiple things are not working. There are many things that will not There are many things that will not work.
to apply the functions here, they also don't work. So that is why we avoid control T. If you want to use control T for formatting, you can. Otherwise, please don't do it.
formatting. Where conditional formatting is there? everyone in the home tab? Here is the conditional formatting.
What is meant by that? Conditional formatting means here, meaning and that is I want to format. Format means filling up the colors, bolded or anything else, right? So we want to format this here,
some logics. So if we go ahead and we go down to the conditional formatting, go to home tab, click on the down drop down there and see [clears throat] highlight cell rules.
Meaning if the numbers are greater than something or less than something, we can go ahead and we can format them. There are top and bottom rules. We can highlight top 10 item, top 10 percent items, above average, below average
items. We do have other options like data bars, color scales and icon set. Okay. here, I would like you to go ahead and copy
just a copy here just part of your data set in another sheet. We understand the conditional formatting first, okay, in a smaller data set and
then we will move forward and apply it in a bigger data set for practice. To understand the concept here, we use a smaller data set here. So please copy it, add one more sheet and paste it there.
When you copy and paste, most of the time in real time also, okay, it it always get pasted in a compact form. So when you copy and paste it, we have a small control toolbar here,
control toolbar here. When you click on the control toolbar, here, okay. intact, still you want a proper column width,
simply click on keep source column width. So in the second line, you will find keep source column width. So while pasting, it will maintain every formatting, everything intact
plus the column width too. Now we can select the revenue here and control shift two here. Sorry, control shift and one here. So this is revenue and age here. We're fine with it right now.
formatting here. To understand about the conditional formatting, let's go ahead and start small. So for example, we want to go ahead color all
cells red, red, okay, where revenue is less than average, where revenue is less than average, where revenue is less than average,
right? Now how do we do that? So about conditional formatting, conditional formatting has one basic conditional formatting has one basic rule here and that rule here is that you
select your range. It can be column here, it here without header, without header, without
header always. Select entire column without header Okay. This and this is your this is your step one. This is always going to be your step one
Okay. So this is revenue. revenue. See the demonstration. If you are me. If you are new, see the demonstration first.
Step number one, I'm going to select revenue just like this. We are selecting revenue just like this. We are selecting only the numbers. We go to conditional formatting drop down.
In this conditional formatting drop down, we go to highlight cell rules and highlight cell rules greater than, less than, between. Then we go to top and bottom rules. Here we find the above average, below
average, right? Right now, whatever is below average, we want to color it red. So first select the numbers, go to conditional formatting, top and bottom rules and we click on below average. In below average here, it can find the
and see here right now it is filling up with red right now it is filling up with red color here.
Because it has calculated the average. Now it is checking, is it below average? Is it below average? Yes or no? Yes or no? Wherever that yes is there, it is Yes, you have the option here to change the colors, but we were looking for the
the colors, but we were looking for the red color, right? Go ahead and apply this. Are you able to apply it?
You don't need to go to new format. Most of the options are here We'll see the new format also, but later. Have you copy pasted the bunch of ranges out here?
And are you sure it is not in the table format? Keep your cursor inside. You should not have table design tab at all. you share the screenshot, make sure everything is visible. The whole screen
is visible, okay? In last image, the whole screen was not visible. mode also Take care of that too.
So here we click okay. Okay, we can see it is in the red color. Now what is the best thing about conditional formatting? The best thing about conditional formatting is it will change the color if we update the data
set. It will change the color if we update the data set. Pay attention here. So the last here is 362, right? I will go ahead and increase the number here for 362. I will add a
zero here. I will add a zero here. Average will Average will obviously change. If I press enter right now, now this is If I press enter right now, now this is above average.
is there. If I change 933 right now, if I change this 933 right now to 93
beauty of conditional formatting. It actually updates as per your number or the date here or whatever data point you have inside.
that when we are highlighting this, we can see the colors here. That is simply about the numbers, okay? Let's look at the age right now. Let's look at the age here, okay? In the age here, we want to simply
age here, we want to simply assign their age is greater than 50. Anupam, I'm repeating the same thing here. What we want to do here? In here,
we want to go ahead and highlight everything in green wherever the age is Age is greater than 50. Can you do that everyone? step one, you know where the conditional formatting is, right?
So you can select this here. You can select this here. So if you go to conditional formatting Anupam, where we should go?
So we go to the highlight cell rules and greater than. Very good. Now in the greater than, we have to simply define the number 50.
green. We have the green color here. Now I like you to also see here within highlight here you have text also, you have got date also. You can also go ahead and highlight duplicate values.
duplicate values here. So to highlight duplicate values, let's see here. Let's check, do we have any duplicate values in the purchase date? Currently we are not aware, right? So I'm not sure whether there are any
duplicates or not. So I'm going to create some duplicates right now so that create some duplicates right now so that we have some duplicates. one of the dates here multiple times because I'm not sure if there are any
So please copy paste any one of the dates here. So I have copy pasted this one date twice twice in here in the purchase date.
Select the purchase date here. Go to condition formatting, go to highlight cell rules and click on duplicate values.
with the red color if you have made the duplicates. It also have an option to color the unique values. color the unique values? Maybe I want to know that which one of the product
category has been sold out only once. Then I can go ahead and color it unique values here. Otherwise, we can go ahead and simply go highlight the duplicate values. Of course, we can change the color here
from green to yellow. So these are the duplicated dates. At one date, we have multiple customers buying the product. customers buying the product. Check the duplicate values here.
the Riteshna also. So now I'm going to address that in next now I'm going to address that in next two to three minutes. formatting. In the conditional formatting, you have
more options here like data bars, color scales, and I can set a new rules too. Now here, I would like you to observe one thing. Wherever you are applying the conditional formatting, have you
see here everywhere it is applying it to individual cells and items? Correct? It is never saying that let's apply to the whole table. duplicate, you want to look for in the whole table.
If you want to go ahead and highlight which particular revenue is less than below average, of course, we would like to highlight the whole room so that I below average. Right? We never want to simply highlight
We always want to highlight the whole room is below the average. The whole transaction is below average. Transaction is above average or this customer belong to above
Correct? If you want to go ahead and do that kind of conditional formatting, we need to go to the custom formatting. Because by default, by default it highlights only cells. By default only
cells are highlighted or formatted. So if you want to go ahead and highlight whole row, highlight whole row, we need to go ahead and customize it.
Okay? So here and we have start what these are? These are all the options where we need to clean the data. Why we need to clean the data here? In real time you will not be working
might be getting the data from some other sources. Maybe you're getting data from Oracle, maybe you're getting data in text files, CSV files or maybe you're getting data from APIs or websites, okay? Or internal websites or we are
is coming to you. So when data is coming from various sources here, okay? Then what happens what what happens here is it is not in And when it is not in correct format, we have to go ahead and apply data cleaning
functions like sort and filter. We have to go ahead and figure out how many columns we need, how many columns we don't need as per the objective here that is grouping and ungrouping. And then comes more data tools. Then
comes more data cleaning tools. One of the data cleaning tools is text to What is text to columns here? Text to columns here tells us it can help us to make sure that any CSV file if it is in one single
uh street number, house number, city, state, country in a separate columns. So usually it happens whenever we are getting data in files and the text files
have some kind of symbols here. These symbols are delimiters. They can be spaces, tabs, comma, semicolons, colon or simple hyphens or forward slashes. So in any ways if we are getting the data like this here, we
can make sure that it is converted into the tabular format like this here. So first here data tool is text to columns. Now we have seen the subtotal. After text to columns here, we get to remove the
columns here, we get to remove the duplicates. be there in our data set? No matter from where you are getting it or maybe data is already with you. What do you think why duplicates appear
in a data set? What do you think? data set, I would like to remind you we are going to be together for three hours. When you have a data set, you're trying
to hide a column and again do the column it, removing it. And sometimes what happens a lot of copy paste and when you are working on it by mistake sometimes we can copy paste multiple times here
we can copy paste multiple times here and there and it can cause duplicates. validation is missing. So when you are actually filling up the importing the data set from somewhere, your files are not going to have any
kind of validation on these cells. Cells usually have every kind of data. They save every kind of data in themselves. So one thing is there is no validation in Excel in a blank sheet.
come exactly in the format that it has been saved in. duplicates are there. So we will see how we can go ahead and remove the So we will see the duplicate feature there. We'll see how to go ahead and
delete the duplicates there. Okay, so there is a feature called as that we will go and remove the duplicates. And after the removing of the duplicates, you'll see the text cleaning
already know we have we have a number, we can go ahead and format it. If we have any kind of null values there, okay, we can go ahead, go back and check and fill up the null values or we can simply say
that not available. So with numbers, we know what to do. If it's a currency, we can make it currency with decimal, we can make it decimal. But sometimes here we have categorical values, okay? So
maybe in we have got numbers here got for example, sales, profit or for example, sales, profit or debt, assets. So we have numbers for the debt, assets. So we have numbers for the calculation. However, we do have lot of
categorical variables in a data set. So if you have employees, employee ID, okay, that's a number. However, you will have employee names, employee address, employee salary, employee designation, department and a lot more.
and those details are called as dimensions because it will give the dimensions because it will give the depth and dimension to your analysis. In that case, sometimes in that case, we need data text cleaning
So what are these techniques are? Text cleaning techniques here includes sometimes we have extra spaces. Okay, so we can use trim function. Sometimes our data set is not in a proper case. So sometimes we we have
set. We want to write it properly there for functions. Then we have clean function here. Okay, we'll see what clean function does. And then we have substitute function.
So all these are text functions here that we are going to see today. We will also understand why to have these functions here. capitalization, any kind of standardization we need, we'll go ahead
and see data and validation too. And after data validation, we are going techniques here. Okay, what are the data preparation techniques here and why should we be using them?
Once that is done, we'll go with the essential functions in the Excel. We logical functions and of course statistical functions here. It didn't do there because I had many copies.
here. This is how it looks like. It has three annotation. So while you are going ahead and trying today's agenda everyone. So today we are going to see here data
will tell you why. Other data tools here we are going to see system number formatting or let's say custom number formats. After custom number format, we will look into conditional formatting.
that we will look for, okay, this is going to be a little bit intermediate, okay. Well, I will not say advanced, but it is an formatting. Then we'll go ahead with the concept of
cell referencing because we cannot discuss conditional formatting and not Once cell referencing is done, we'll go with the logical functions first. If we are able to cover logical functions today, right, then we can move
However, we'll cover as much as we can. Now let's talk about in the agenda here, why did I write data tools? Okay, there's a reason for that. So let's go ahead and see. If you remember
from the day one, I have told you here that these are called as tabs, right? These are called as tabs. We call this entire thing a ribbon. This entire block that we see here, we call this ribbon. Now inside this ribbon, inside this
ribbon, each of these is a group. So this is alignment group, this is number group, this is styles group, this is cells group. Because conditional formatting formatting is table cell styles. This
formatting is table cell styles. This overall for set the styling of a table. This is related to the cells you want to insert, delete or format. font. Here everything is related to alignment.
So these are called as groups. That is why when you go to data tab here, when you go to data tab here, you will find that we have sort and filter group here. This is the sort and filter group, and here we have data tools.
group, and here we have data tools. These data tools here are for cleaning your data, okay, and making and reshaping your data set. It is for that. we are going to focus on these tools here. We are going to focus on text to
columns here, okay, removing duplicates and data validation. We are going to learn about these three tools here. Okay. So go to data tab. Here are the three options here. Now first is we will look into the text
here? The text to columns here, you will find side here. That's this is text to columns. As the name suggests, okay, if you have
a data in text here like this, as you can see the in the example here, that we have a whole entire data set. However, this entire data set we have in So we have got name, address and phone number. These are the headings here. And
after the heading, we have two more rows of data. single column. We know these are different columns here. Reason is they are separated by they are here separated by the comma. So
name. This is age, so after this the age is written here. So we are good with that. Now how do we go ahead? Make sure how do we go ahead
and convert this this data into a proper data set like this. ahead and convert this. To convert this here, it's step number one.
Whenever we are applying any kind of data tools, okay, one thing is always going to be there, that is whenever you're selecting your data set, okay, step number one is always going to be and select your entire data set.
here. Select the cell range or your data. This is always going to be the step number one. So let's go ahead and explore TDC to
explore TDC here. Select all the three cells here two, Select all the three cells here two, three and four A two to A four here. After selecting these here we will go to data tab everyone and we click on text
When you click on text to columns here okay a dialogue box will open up this when this dialogue box opens up everyone see it very carefully and read whenever ahead and read. If you'll try if you'll we'll read here
you will see here it says that convert text to column wizard a step one of three currently we are this is a three step process and currently we are at the first step. Now moving forward let's see here it
says that we are at a step one of three here and in this in this step here let's go ahead and read what it is saying. It is saying here the text wizard has It is saying here the text wizard has determined that your data is delimited.
read. If this is correct choose next or choose data. We're like fine they tell me what is So here delimiter means characters such as commas, tabs separate each field
Field means column. So it is saying that if there are characters separating the each field here it is called as delimited if fields are aligned in the columns with spaces between each field
then it is called as fixed width. Currently what we have here is delimited and it is correctly identifying that too. So what is a delimiter everyone delimiter is just a symbol
okay delimiter is a symbol here it can be tab, space, semicolon anything which is separating the columns here. Now yesterday that's a that's comma here right now very good. Now we go ahead and we click on next
delimited data set so we go ahead and we click on next here. When you click on next here, we again read first. You'll read again. And here it is written this is a step
two of three. What's written here? It is step step two of three. And here are the delimiters that we get to select, right? right now. And we don't need tab here. So I'm going
to uncheck tab here and check on comma. What does it say? The screen lets you set the delimiters of your data that data contains. And immediately we can see in the preview right now how the data changes
and how does it goes ahead and identify it here. So let's see here uncheck check uncheck check. once we can see that here we have checked on comma, we go ahead and we
click on next again. We checked on comma, we click on next again. Now when you click again next here, we are at the step three of three. We'll click on a step three of three here.
Now in this step three of three, read again this screen lets you select each very important. This is very very important the screen here because here observe right now this is name everyone, right? This is name here it is currently
And if it is highlighted in black color here, we get to set the column data format here. Okay. For example, now here we have age age.
Age is general. So whenever we have number format, it is always go to Okay. However, name we can go ahead and we can decide which format is it. So we can decide that if it is name, okay, it should be text.
fine. If it is address here, it has to be If it's a phone number, phone number has to be a text because of course we are mathematical You also have the option here, okay, to
the column. If you don't want to import any column you can skip the entire column too. So this column data format. So when you number format fails, that's where you can use this text to column formatting
to format your data set. we can highlight, select, highlight and Right? So after defining that, okay, this is most important part comes out to be destination.
Currently what is the destination? Destination is A2. Where what is written Destination is A2. Where what is written in A2 right now? A2 consists of our raw Remember that whenever you are applying TDC, okay, never ever replace your raw
data. Never do that. Because we have never we have no idea, okay, what is going to how is it going to look like. Okay. So after you have extracted the
place in your sheet, you go ahead, you verify that yes, I have all data correctly extracted, then you go ahead and delete that. So never compromise your raw data. Okay. So what we do here is we change
So in the destination here, I'll click on A2 and delete A2 here and simply select any cell outside. When you select a cell outside, just When you select a cell outside, just make sure that you have enough space,
you have got enough space where it can extract all extra columns here and extra Right? So what we do right now? destination cursor will blink to delete the A2 there.
Go outside and click on the cell here. Any cell. okay, where the extracted data should be, go ahead and click finish.
be, go ahead and click finish. Go ahead and click finish. And let me know that yes, you have the extracted data set there. And now we have a data set. Now in this data set here, of course,
and give the borders to our cells here. So let's check it out here whether it is So here we have the data set. So I will select this here. We can go ahead and And now here we have the phone numbers here.
Now you must there are few things that you might have the doubts on. First is what is this green corner here? The green corner here is the warning sign given by Excel that the number in this cell is formatted as text.
So do you want this number to be stored as text or do you want it to convert this to number or do you you want us to ignore it? number as a number. Or we are not going to do any
want to be saved as a text so that I can see the entire phone number here. You might have observed before you want might have observed here whenever you're writing a phone number in real time, sometimes it get converted into a
scientific notation like this. And then we try to make sure we then we try a lot, okay, that it comes out in a proper number or it is just a phone number. And it gets saved here like this because
number is huge. That is why there's always a good idea to save your number as a text. So while importing itself we are getting as a text and we are good with that. Second here guys, look at age here.
It is properly extracted. We are good with that. Look at the age here. This is 22. This is fine, but this 22 is written here, okay? Maybe by mistake someone has done the
incorrect. So how do we go ahead and correct this? So how do we go ahead and correct this? How do we go ahead and correct this?
And I just want 22 here and not the A here. So please go ahead and add one more column. How do we add the column Okay, no worries. Let's go ahead and add a column everyone. So let's go ahead and
how do we add a column? We can go to the column E. We can click and right click and insert. This much we understand, right? There's one more way to add a column. Okay, we can select a column first. So
Okay, we can select a column first. So select column is spacebar. And when you want to add something, when you want to add selected object, when you want to add selected
object, it is control plus. Control Of course, if you want to delete certain object, if you want to delete certain object, okay, delete selected object, it is control minus.
It is control minus, right? So if I want to go ahead and add, if I want to go and add right now, first I need to select the column here. To select the column here, the shortcut key is control and a space.
So here if I'm giving my cursor here at the E column and I go ahead and press on I will get the on-screen keyboard here. Give me a moment.
here, okay, at the E here and I'm pressing here control and a spacebar. the column is selected. Now I can go with control here. Now we can go here with control, okay,
shift and plus. And here we are. If you want to select a row here, okay, if you want to select a row here, then you can select shift and a spacebar. It selects a row.
everything. Yes, Usha, you can go ahead and select this. You can go to home tab, back to home tab, and then insert it from the home ribbon. Okay, you can also use Alt key series to do that. So I just didn't wanted to go from this
data tab somewhere else. It is easy to do within the sheet itself. Why to change the tabs here? But of course that is also correct. right? So here we move here everyone. So we have one extra column right now. And
to do here? We want to go ahead and split this age here. only this column right now. Please split the just select this column here. You can also go ahead and select just this A 22 here. That will also do. Okay,
both are same right now. So this is A and 22 here. columns. I will go back to text to columns here.
your data is delimited. We know that it is not delimited here. We have to get the A 22 A and 22 separate right now. fixed width because there is no symbol that is separating anything.
So we can go with the fixed width here. It is not a perfect example for that. However, I will show you how fixed width works. fixed width right now and we click on next. We click on next.
delimiter nothing. It says that that screen lets you select the field width line with arrow signifier column break. To create a break line, click at the desired position. To delete a break
line, double click on it. To move a break line, click and drag. So if I want to go ahead and split A and 22 here, if I click here, forms two columns here. First column contains A 22 A. Another column contains
GE 22 here. If I want to delete that, I double click If I want to delete that, I double click here and I delete this here.
the end, it will select one column here and the column here which is going to be empty. Double click, it removes the arrow. Okay, we understand how it works. Right now we don't have the exact break here.
We don't have the any exact break here. I'm simply going to click here where the A and 22 breaks and I'm going to click next. Where A and 22 breaks, I'm going to click next.
So, click on next here and you will find it has created two columns. One column here is connecting with A22A, another column contains D E and 22 here. Destination here is D5. Destination is D5. As usual, we will go ahead and
change the destination here so that we have original data intact. Go to destination and change the destination here, please. added and then click on finish. Then click on finish here.
When you click on finish here, okay? It says that there's already data there. Do you want to replace it? Which data is there? The address is there. The address is there. Why do you think we are getting this
dialogue box right now? Okay, because it has two columns and it doesn't have the space for two columns. And this happens with you a lot. Right? Now what do we do here? If we go ahead
deleted. We don't want to delete the extracted data set here, okay? So you cancel it here, you cancel it here. It doesn't you cancel it here. Now let's understand in the TDC when you want to use it, these are the common
errors that we face. Right? Why? Because we didn't have much had much of space here and we clicked if we click on okay, it will replace the address and we don't want to replace that here. And this is how the fixed width work.
Okay, fixed width goes ahead, create a line and give us the answer like this. And I guess I want to repeat that. It's I want to repeat that. There's a reason we did this. Okay?
columns here, this is a general error that we get here. A very general error. So always remember whenever you are splitting something, you need to have a proper space here. That is why when we first did it, we
made sure that we have enough space to get the data here. Now so here select only A22 for now. Now it will work. Now it will work. So we will select the A22 only here. We go to text to columns right now.
width. Select fixed width here. This time everyone select A22 only. We go and select text to columns here.
if when you have bigger data set Pragati most of the time okay if it is fixed similar. And in the bigger data set text to columns work seamlessly. Reason is whenever you are getting any
CSV files they have their own structure and those structures is fixed. lines it is going to follow exactly there. So if delimiter is comma it is going to be comma. If they have a fixed spaces in between the spaces will be
fixed. Okay and if you get a data like this here in between you filter the data out and you apply text functions there to and you apply text functions there to clean it out.
and we click on next everyone. When you click on next here okay we know between A and 22. A column will be created. there. Now when you click here we have got A22
correct. Now click on next. Okay we just tell me one thing here. Do we need both the columns?
Do we need both the columns? Yes or no? Do we need both the columns here? No. Can we skip the column for importing?
Yes. So what we do is we highlight the first here and we ask you to do not first here and we ask you to do not import. Go ahead and select the first column here and we ask you to do not import.
now we simply replace the same 22. Okay, we don't change the destination here. Destination will remain that only. Because now we want to replace 22 here. Select this do not import column and simply click on finish again.
skipping this column here. We are getting only 22. We click on finish right now. As soon as you click on finish here, we are good. Done.
Hi Tanmoy. Yes, absolutely right. So in that case we use text functions. In these cases, when you have multiple different combinations there, in those cases we use text functions to clean your data set.
same thing. And we are going to see every other way And we are going to see every other way that we can.
we have a fixed width here. We have got the comma thing there, And basically we're splitting one column into multiple columns here. However, in the real time, if if I talk about real time here, the DDC is
instead of DDC, nowadays people use power query. DDC is majorly used when number formatting doesn't work. So look at this data as a text here. Okay, why I am calling this data as a
text right now? Because it is a number. But what kind of number it is? It is saying that 2025 0209. So we have got year right now. What we have here? We have got year. Then we have got month and then we have
got date. So it is date here, but it is written in one single number. It is not a date series everyone. If it has been a date series date series, it has been easy to recognize by
If I try, please don't follow along with me. Just look at the demo right now. If I go ahead, select this and I convert this into date right now, it doesn't give me date. Do you see that? It gives me hashtag date. Why?
For a very simple reason, that it thinks that if this is a number, it is a date series and it's my calendar doesn't match this huge date series right now. So here is some issue with us that okay,
it doesn't it is not able to follow the date formatting here. TTC? TTC will help us in simply formatting this data set here. Let see how.
So everyone, step number one, let's select our date here right now. select our date here right now. Let's select our date here right now. to columns here. The text to columns right now, okay, it
doesn't matter which data it is. It doesn't matter if it is delimited or fixed width. Doesn't matter here. Because we are not we are not going to select anything anyways. So just to escape one of these steps here, I will
click on fixed width. Because I don't want to create multiple columns. I simply want to format it. To make things easier, I am selecting fixed width. Select fixed width and click next. I'll
tell you why fixed width here. Okay, I just want to skip one step. That is why. So go ahead, okay, click on fixed width and click on click on next everyone.
selecting this here. We are selecting this. this. We are going to TTC everyone. width and clicking on next. So when you click on next here, it will
ask you to specify the columns that you want to I don't want to specify because I don't want to split. So we can simply click on next here too. Next.
Now here, I don't want to go ahead and create anything. So we click on next But this is the part which is going to help us. Okay. In this part here, we know that we want to format this here. So we go ahead and we click on date
here. We go, go ahead and click on date here. When I click on date here, okay, look at here, it is saying d y, d m y. available for us. So right now, what is the combination we
need here? 2025 is the year. 2025 is the year. 02 here is a month, 09 here is a So we are looking for y m d. Yes, wonderful. Thank you very much.
So we go to the drop down here, we select y m d here and we click finish. If we click on finish, it replaces the the example here. So please go ahead and
change the destination right now. I'll change the destination to this. finish. In real time, you click on finish, it automatically formats your existing data set. And we have the answer here.
go. It is in the proper data format. If you click here, you check on the home tab, it says date. Now in the similar part, in the similar part, sometimes our data is in proper
date format. So it looks like a proper date format. observe that this date is in the left side here. I will like you to go ahead and check the next option here.
Is your date is left aligned or right aligned? In the second example here, is it left aligned or right aligned?
okay? Because if your system, if your system is set on U S date with the forward slash, it will be right aligned. Otherwise it will be left aligned.
So right now, if I try here, if I select this, Select your date, only date here. And here says that it is a date format.
It is a date format. But if this has been a date format, it that. If it is a date format, it should be uh it should be right aligned.
It is not even though it is written date here. The reason is number formatting is failing right now to format this date as a date. date and then year, right? So we will go ahead and we select these states again.
We go back to data tab here and we click on text to columns again. Select your date again everyone. Select this date again. And we click on text to columns here. We follow the exact same steps, okay?
This time I will show you we can select delimited also also here. format, we can select delimited here. But when you select delimited here everyone, we have to add a step
to make sure it accidentally doesn't separate the columns. So we are selecting this. We are going to text to columns here. In text to columns here, this time we will select delimited
and we click on next. In a step number two here, we make sure that we uncheck comma. Okay? We make sure that we uncheck anything that is selected. Make sure nothing is selected.
Okay? If something is checked out also, just make sure you uncheck that. just make sure you uncheck that. And then you click on next.
Let's click on next here. In this next here, we are in the third step. What we will do in the
Cell address. Yes. So we go to next year. We first we click on next. Okay. First we click on date select here? We select month, date and year. What we
change the destination here. We change the destination here. There we go. And then we click on finish.
formatting here MDY and you click on finish. go. Why I am showing you these examples
even if you check your number format, it says date, never believe that it's a date unless okay, it is in the exact same format as No matter where the data is coming from, your date will automatically reformat
itself as your system system default. system default, it means it is not properly read as a date series. And this can be this can be rectified by simply using the text columns.
Okay, text to column here. Wonderful everyone. Save your changes. data tools here. Now do we have the answer everyone for everything? Shall we move forward? Can I
get a quick reaction, any message? We are able to follow along with this space. Now let's move on to remove duplicates. is fixed width or delimited option? Very simple Tamana.
If you have fields separated, okay, let's take an example right now. Let's So let say this is the data I have here, right? This is the data I have here. right now it is like separated by A. Maybe I have got semicolon separating
here. Maybe I have hyphens separating things here. So everything here, okay, let's say this is the fifth street here, main fifth and likewise. So now now I have multiple symbols
right? So if if there are symbols there, they are separating the columns that you might need further, okay? That is called as a delimited
This becomes a delimited option. Why delimited? Because there are delimiters that are separating the field here. How does a fixed width look like? Fixed width look like this. So maybe you have 822 here, okay?
And after fixed width, this is the department name. After the fixed width, here you have department Here a fixed width. Here you have got city details. Here is a fixed width.
Here you have got a state details. When you have the data here, okay, the this. So when your entire data looks like set. So it is a clear indication what which
is delimited with this which is fixed width. come it comes in .txt. Okay? When you see the delimited one,
they come with .csv. They might come in .tsv. Like that. So the exchange the extensions will also differ. Moving forward everyone to duplicates. When you reach to duplicates, there are
actually no duplicates. All right. So let's create some. So we can see here there are few numbers written here, right? So I'm going to copy here. I'm going to simply copy and paste these numbers again just to
create the duplicate. Okay? So please go ahead and copy and paste few numbers here. Same numbers. Just copy and paste it everyone and create some duplicates like this.
there are no duplicates. We copy and paste and we create duplicates here. Now let's see the steps of removing the duplicates. duplicates are. They are at data tools, okay? In the
data tools just towards the right hand side, this is remove duplicates, okay? Step one of removing duplicates is select your data, select your data, select your data, select your data, preferably, okay? Without your header.
header here. We simply want to focus on our data set, right? You can select the That will also work. Doesn't matter. So let's select the entire data set for can select the entire column, whatever we have copy pasted so far.
we have copy pasted so far. Make sure you first select it, okay? So here we have selected our data here, entire column that we had here. Now we go ahead and go to data tab. In the data tab towards
the right side of the text to column so you'll find the remove duplicates. So this is selected here. Only select the select till where your data is, okay? Control shift down arrow key.
Once you select this here, go to data tab, click on remove duplicates. here, a dialog box will open up. Read the dialog box very carefully. It says that okay that it is asking you here that does your data have headers?
It can identify that. And yes, we have the data here. We have got the headers and it is giving you the name of the column as count. It column name. Now it if it understands this very
easily here, we can go ahead and click on okay that okay fine go ahead and remove the duplicates and that's it. This is data here, we click in here, we remove the duplicates here and we click okay.
When we click okay here, what happens? Please tell me, what do you see? Hi Faizal. Faizal, are you at the duplicates? Have paste? As soon as you click okay here, it says
10 duplicate values found and removed five unique values remain. Note that it the counts mean two empty cells and spaces. So we click on okay right now yes we have successfully remove the duplicates.
So if we have just one column and we want to delete duplicates from just one column it will work. Right? Now what happens if we have the entire table? How it will work in the entire table here?
To work with the entire table here again we don't have any duplicates, right? Yes very good Ishan, Kunal, Ashish, Sarla.
It's fine Faisal and now you can hear me right? Don't worry about that. You can always go back to the go back to the video recording. Ritu now remember conditional formatting
Ritu now remember conditional formatting basic conditional formatting? we have already seen conditional formatting. We are going to we're going to see that again, okay? Right so let's see here this is table
everyone okay? In this table right now let's create some duplicates. Faisal let's create some duplicates. Faisal am I audible to you right now? Alt A M here is the Alt key series. With the Alt key series okay you can we can
do everything. And it is never written like Alt plus A plus M, no. You press Alt then you press A then you press M. It is never have to be pressed simultaneously.
I haven't talk about it yet I will talk about that too that there are two types of shortcut keys in Excel or let's say in the Microsoft Office. One are the control shift keys and one are the Alt key series.
duplicates here. Let's create the duplicates here. Faisal work along with So now this is here, right? I'm going to simply duplicate here. to duplicate the last line here. So we can copy paste the last line here.
Or you can simply select the you can simply select the cells here, okay? And press control D. You can simply go ahead, select these rows here and press control D. Control D
duplicates. Control D duplicates. select control D. It will simply copy whatever is up
there. Next duplicate is now see this these are the name subject in CGPS, right? And these CGPS first are calculated by hand, okay? If they are the physical papers and then these marks are getting
uploaded and from the hard copy to the soft copy and the soft copy is also it goes into multiple different sheets and then how it gets uploaded into the ARP. So it has a lot of copy and paste in
comes to the university level, okay? These marks and your names are getting copied and pasted multiple times across the whole process. So in that case, sometimes it can happen that we are only we have
we have by mistake have got the same name twice. Okay? And we are writing here, but the CGPA here gets updated that is incorrect. So copy paste Albert in and math subject
here. However, by mistake we are going to have incorrect CGPA written here. Okay? So the CGPA is now different. So instead of 4.8 by mistake we have written 3.8.
And this is a very very simple mistake. It can happen to anyone. we determining the duplicate? How you how is it determining the
duplicate here? Okay? So see here in real time also, even if I value, so please let me know how will you find You will first read this name here, Albert date.
Okay, you like fine, okay. You'll go to the second line, it is Allison. Is it Albert date? No. Now you'll go to the third line. This is NS, is it Albert? No. Is it Allison? No. Is it NS? Yes.
Once you will reach to the fourth line here, you read NS again, you will check. Is it Albert date? No. Is it Allison? No. But is it NS here? Yes, it is NS.
duplicate, this is duplicate. We need to remove this. So every if you go ahead now, you see oh Albert date, this is also Albert date. needs to be removed. So whatever is the second occurrences,
that is always seen that as duplicate. So whatever the second occurrences, it will be always seen as duplicate. Because that is what get gets repeated. So right now we have two type of duplicates. One is the entire thing is
duplicated. In this case here, we have just name and subject is getting with the incorrect CGPA. So let's see how the remove duplicates Step number one, select the entire data everyone.
Step number one, select the entire data set. Select the entire data set here. Okay, we are going to remove duplicates right now. In the remove duplicates, it is able to find out the data has the
headers here, right? And duplicates are getting removed. Yes or no?
and determines the second one that is repeating, okay? That is the the repeating, okay? That is the the incorrect one. So when you click okay here, okay, it only it only deletes whatever is
throughout matching. So Tanmay, if two students have exact same names like Albert Dane might be the similar name and their CGPAs are similar name and their CGPAs are different, it will not get deleted.
Nitaprasad, I'm going to answer you that. Yes, there is a way. the duplicate here, it will actually save it in this way that if two people
is saved. Done. However, when I go ahead and that that oh, somehow the same batch here had got copy pasted here and now they have incorrect details added here.
I need to go ahead and make sure that I have got unique names here have got unique names here or maybe the unique combination here. the I want to remove the duplicates again because I know that Albert Dane is
So as a human being, I know it is getting repeated and I want to remove How do we remove that here? So we select this table again. We select this table again everyone and we click on remove duplicates again.
duplicates again, observe, okay? These are the boxes that we can check and check. So here we can determine the combination of which two where which combination we need to check for duplicates.
that names are getting repeated or name and subject both are getting repeated duplicate. However, it is going to remove entire How what we when we check and check that here, we also determine that where are
we looking for duplicates? So click on remove duplicates again. This time uncheck CGPA. This time uncheck CGPA here.
has figured out that it has to go ahead and find duplicates based on first two columns. However, it will remove the entire
work and Lalita you have Lalita Prasad it's a very good question and yes there here is that is there any way to identify duplicates and move them to another sheet?
How we can sort out or list out the duplicates in real scenario files? In real scenario files, usually what we do here Lalita is we select the entire duplicates and it will actually go ahead and if
unless entire row is not matching, it will not delete it. Right? Now once we will go ahead to go ahead and extract the unique values out of some column into another
sheet? That's the question here. So for example here, okay want to simply go ahead and extract the unique values there. How do we do that?
and remove the duplicates. That is very straightforward and easy method filter. Do you see inside the sorted filter we have advanced filter here? So this advanced filter here is actually
used for extracting the unique records out of a column or out of a table. the list or copy to another location. It's a fantastic option to work with.
So when we reach to a data table Lalita Prasad, we will go ahead and I will show you this advanced filter here. I'll remember that. two different sales in a big data set? How do we identify that and remove it?
answer already. Advanced filter. Okay. And
if you want any permutation combination, okay, so remove the from a single Abhishek. You use the advanced filter here. It location and it gives you unique records too.
So if you want to simply find the number of duplicate entries, okay Swadhin, I right now. Because it tells you how many duplicate Have you okay. So if you are removing it, it is telling
telling you one duplicate value was there. Otherwise you apply the conditional formatting and you simply see how many You can simply go ahead and select it and you can see the count.
Great. Hi Akhila, if 3.8 is the correct CGP of solution? You sort it and delete it by hand. That's the only solution here.
because it is just one single person, right? However, I needed to show you what will happen
the person has the same name, if two person have the same name. was the remove duplicates. Okay, now we will move forward to data validation.
Why duplicate appears here? Because I give you one answer there that the validation is missing. What is validation? Entering the valid data and it's to check if the data is correct, examining,
supporting, authenticating, analyzing. Right. So what is validation here? If I simply ask you here that what is the meaning of the word validation here is, it simply means that we have we let's
make it very easy to understand here. Okay, make it very very easy to Don't go too much technical this. Understand it pretty normally here. We buy electronic equipments, right? We buy electronics.
In electronics here, we always have some validity rules set up there. In those validity rules, what do they do? They If you follow these rules, if you follow these rules, what will happen?
If you follow these rules, you will be you will be able to get free services, you will be able to get the new device and likewise. So basically, what we are saying here is whatever rules and regulations are
there, if we follow those validity rules and regulation, okay, we will get to use the free services and the exchange. Right? In the similar manner here, when we talk about data validation, when we talk about data validation, we are
actually not validating the data. We validate the cells. We don't validate the data here. We go ahead and we set the rules for the cells. What we do? We set the rules. We set the rules for the cells. So
you'll see that that this particular cell will contain only numbers. Will contain only numbers. So that particular individual cell, okay, it can be a range of cells, it can be a single column, it can be a single row or just a
bunch of cells together. We go ahead and we set the rule for that that it can text, it can have a value from this range to this range and likewise. So data validation here means is we go ahead, we set the rules for a bunch of
ahead, we set the rules for a bunch of cells together and whenever any person goes into that and try to enter the that data, that rules will validate the entry. That rules will validate the data entry.
So if we are going to an event, unless we don't have an invitation, we cannot Meaning unless we are not dressed properly, unless we don't have the invitation, we cannot get in. Why? Because that place has set the rules for
themselves. Right? And we have to follow these rules if we want to get in. So data validation is we are trying and setting the rules for individual cells. That's data validation. So each one of
your answers is absolutely correct. Okay? Technically this is how it works. So let's see here few examples. So one example is automatically there for you. If you click on the employee ID right now, okay, you can see here
immediately the input box pops up that enter between enter the employee ID between 1 to 100. And until you are inside 1 to 100 here, until you are inside 1 to 100 here, you are good.
However, the moment you go beyond 1 to 100 here, it will pop up with a error message and it says that enter values between 1 to 100 only.
it is letting us know that where we are making the mistake. And it would not let us see enter the data unless we go ahead, retry again and we write the correct entry here.
So this is how data entry works. Let's go ahead and write some text here. between this only, then only you're allowed to enter anything. Now next year is next year is look at the age right now.
In this bunch of ages are written here, okay? Please delete that number. Why I'm want to go ahead, set the validation here that whosoever we are employing, right? They are going to be between the ages of 18 and 70.
That's fine here. However, data validation has one here? If the values are already written here, on that values if I go ahead and apply the rules, all right, these values will
not follow the rule at all. We We apply the data validation rules. We can apply the data validation rules only on, only on blank cells, only on Okay. Only on blank cells we apply the rules.
And afterwards, afterwards if anyone comes in, we will be able to do that. Take it as a very, very simple here. If someone has already entered the premises, okay, by hook and crook they enter the premises. Now getting them out
of the premises based on the rules, that is going to be very tough. They are not going to leave there because they are in the party. rules here, it is very difficult for them to take them out.
That is why if you're here, numbers are already there, okay. rules on them, it is they are not going to So here select those numbers and delete that. We want blank cells to be here.
Now we have the blank cells. Let's figure out, let's see here how do we go ahead and give a proper data validation here. So here we select the cells. important things that you need to know. First here is that always select the
range here and it should be without headers, okay? And always apply on a blank or empty cells. So please go ahead and select all the cells everyone, okay? All the cells here
without any header. After selecting that here, just below the remove duplicates here we have data validation. So first we are selecting everything here, right?
And in the data tab below the remove duplicates here we have Now read. It says that allow any value, meaning to any cells out here, meaning to any cells out here, okay? By default any value is
allowed. However, now we want to restrict it. So when you want to restrict it, see you have tons of options here. It can be a whole number, decimal, list of values. It can be only date, only time or a text
right. So if we want that here, right now what we want? We want a whole number. It's the age here. We want a whole number and this date should be between 18 and 70. So it is between 18 and we can say
That's it. Now we are setting up rule here. Once you have set the rule here, click okay please.
Right? So if we are clicking okay right now here, go to age here and write for example 34. It is working fine. 21 working fine. Type 12.
outside the range, what do you see right now? there is a error message. What does that error message say here? It says that the value doesn't match the data validation restrictions defined for the cell.
Now just imagine, okay? So you have created that. Now now problem here is, okay? You go ahead and create a full template here, okay?
Where you need to gather the employee information. You send your send it out here and this is the error message that they okay? This is the error message they get.
appreciate the error message and they will understand what's going on here here? Do do you think they are going to actually read what message is here? Or they are going to come back and say
that your template doesn't work. You go ahead and recreate it. Your template is erroneous. What do you think will happen if it's sent out your exact this set of rules outside
and they hit something wrong or they enter something wrong and they keep on getting this error message. Do you think they are going to understand this? ticket and tell you that this erroneous created again.
The problem here is, okay, a simple person in the office here, if they are not technical, they are not going to understand this here absolutely no idea. They will come back to you and say that, okay, this doesn't
work. I'm trying to enter the data, but our Excel doesn't let me enter my data. have to write the correct error message here.
And to avoid any of this happening, okay, as soon as see here, there is one more issue with data validation. You have applied the data validation. You However, as soon as you're clicking on anything here, even you're double
anywhere? Any indication that the data validation Any indication that the data validation is applied in these cells? No. This is the biggest problem here. So
what is better? The moment someone clicks on this cell here, the this should go ahead. This should go ahead and highlight this message that see data validation is applied here and according to this rule only you can
according to this rule only you can enter the data in this cell. ahead, select the range again. Go to data validation again. Yes, very And this data validation here, we do have to set the rules right now, but
have to set the rules right now, but just here we have input message two. In the input message title here, okay, I'm going to write here this is data entry rule. This is the data entry rule. I'm giving the title here and the entry
rule I'm saying that that age should be between 18 and 19. title that this is a data entry rule for this particular cell. And data entry
rule here is that the age should be between 18 and 19. Once you give input message here, after the input message here, we also have
have error alert. So that error message that was coming This is the input box will appear as soon as they click on the cell. alert here. So we click on the error alert.
Okay? In here also in the title I am going to say that data entry error, data entry error.
should be whole number whole number between 18 and 20.
What we are doing? We are writing a proper error message so that if they are making a mistake, they will be able to understand what is going wrong and how works. So give a proper message here. Okay? The
entry error and data should be the whole number between 18 and 70. You can go ahead and write a better You can go ahead and write a better error message on your own too.
We have the input message here. It should be 18 and 70. error. Data should be between whole number in here. number in here. Let's click okay everyone.
as soon as you select on any cell here, this appears. make any mistake here, it is quite clear. Now we know how to set up our rule, how to go ahead and
message here. Are we good? Data validation, however, data validation is not used a lot nowadays entering the data in excel here like this. We already getting the data in
big data sets here. Right? data validation here. I can type anything that I want here.
to where we select. Good point is you can copy paste it. If you copy paste this here anywhere, okay? Let's say I copy paste this here. It gets applied. Okay? It gets applied by simple copy
you can go ahead and keep copy pasting it. actually utilized for creating the drop downs like this nowadays.
All right, to make the functions dynamic, usually drop downs are used dynamic, usually drop downs are used like this here. you see a drop down. When you click here, you see a drop down here.
This is done, okay? So that we have the correct in we can go ahead and make our functions dynamic. So most of the time, okay? Now this is why these are used So let's go ahead and see how we can go ahead and create a drop down for the
To create the drop down for the values like this here,
I'm going to create a column here called as region here, separate column here. Okay? So in this columns here, we are going to drop downs. So whenever we want to create the drop
down here, again we will not go ahead and select the header. We select only the bunch of cells here. So I'm selecting the bunch of cells here where we want to give the drop down. I'm going to show you how do we give the
drop how do we create the drop downs inside the cell. inside the cell. Right?
Now we go to data validation. Any data validation here, we go to settings. It is not a whole number, it is not a decimal, it is not date time or text.
It is text, however, it is not just one text. We want people to select from the list of the data set. So here we go ahead and want it to select it from a
data set. Where is the list? So here is the source. You can go ahead and actually type down the names here, any text that you want to type a b c d, go ahead and type it. It will go ahead
and take that as an input too. The best here is you go ahead and select outside. When you select, just make sure you are not selecting the header. When you select like this, just make sure you are not selecting the header.
Click okay here and tell me do you have the drop downs everyone? when we need uh to apply any formula there.
formula or logical formula there. logical formula there. Then we go to custom. cells are going to have the proper drop down and we can go ahead and select the
drop down here. So this is one of the one of the here. So let's go ahead save and close this file everyone. So we have seen a text to column duplicates and data validation.
Now we'll move on and we'll see few more examples about formatting and we will uh go ahead go ahead and move one step further in formatting,
number formatting and conditional formatting. Okay? Now please go ahead and open so that was all the three data cleaning and preparation techniques, okay? Let's go to the formatting part here
because in the formatting part we have more data. we go we select the cells without the header. all. Then we go to
data validation settings in the data validation settings here in in the data validation settings here in the allow you select list. And in here you select the source of the list.
the source cursor is blinking here and we select the or bunch of cells here which select the data set. which select the data set. Yes?
Yes Akshika I will share this file here too however you can find the file in session materials it is also in the reference materials under formatting. Give me a moment I will share it here
Hi Abhishek how do we hide the list without using the hide option is there without using the hide option is there any formula for same? Yes there is. real time what they do okay in real time what they do here is they keep all the
list in one single sheet and they simply hide the sheet. downs like this they are using it somewhere they add a sheet in that sheet sheet altogether. So that is one practical usage.
Right? However let's say you don't have that luxury you don't want to do that here. So usually they will select this here, okay? here, okay? And they will go to some very far in the
sheet in some thousands and the rows and they keep the list there entirely there. If you don't want to do that also you simply want to make sure that nothing Nothing appears here. So you do one thing here.
the bunch of cells Abhishek right? Select bunch of cells here. And then you go to custom format. So you can press on control one or you can simply go to custom formatting like this here.
You click on custom format and instead of general formatting here, instead of you go ahead and you press the three semicolons right now. If you press the three semicolons here and click okay,
everything disappears from the site but it will remain at the back end. However, if you look at the formula bar it is there.
doing that you still have to make sure these cells are protected. Okay? If I delete that if someone will see here oh nothing is there and they delete that it Okay? So if that is the case
too or they are far somewhere that no one can edit these. the entire file is for the view then it's fine.
[laughter] That is one of the tricks you can use this three semicolon trick anywhere, actually anywhere wherever you can custom format anything you want to hide something
from the view. You can use this trick anywhere. So that is data validation everyone. Okay? Now I would like you all to open up a file called as formatting and I will share that file here in the chat
will share that file here in the chat box too. It will take some time for me because I have to close and open everything again.
conditional formatting there you will format there you will find it. we want to work with everyone and it is going to look exactly like this.
Now we're exactly at the halftime, right? So we'll go ahead for a break. Once we are back from the break, we will continue with our formatting. Here we and then we'll look into the conditional formatting.
and look at this data set. Look at the date right now here and order ID and product is here and look at the profit and loss, but net sales looks Yes. Now we'll go step by step. First, let's
talk about date here because date is one that we have seen before. In this date here, few of the dates that we see here in the formula bar, I see only 1st January 2012. However, here I see something very different.
I see here one, then Sunday, then January 2012. This is kind of the format we never see. So what we see here in the cell is quite different. Here what we see in the cell right now, it is a starkly different
right now, it is a starkly different what we see in the formula bar. It tells us that whatever, no matter what we see in the cell, it's all a facade. At the back end,
will be shown here. So my true cell value is just 1st January 2012 here, but however, with the help of custom formatting, we can go ahead and decide whatever we want to write here.
formatting. Remember that any kind of formatting is just a facade. It's about formatting is just a facade. It's about how do we want to see the data set. at the profit and loss right now. Look at the profit and loss here.
It says 844.158. However, if I see the net sales here, it says 7.2 K. So it is here, K means thousand, right? better in observation?
Why we need this facade? Why we need the custom format? To understand that, please answer me here, which one is easy to grasp and understand? If you want to compare the values here, okay, which formatting is
values here, okay, which formatting is better? net sales. So hi right. Okay, so if we have here, if I
have here normal numbers like this here, it is not that easy to comprehend and look at, right? However, however, if I go ahead and see these values here, they are summarized values and easy to understand, right? So let's go ahead and
we'll see this custom format one by one. First, let's talk about the To format the column here, always go ahead and select without the header. Please don't select the header. Select the column here and control shift
down arrow key. Select the entire column without the arrow key. You can press control one or we can simply go ahead and open the number formatting from here.
header. Once you select the entire column without the header, in the home tab, we will go ahead and expand the number formatting from here. So with control shift down arrow key, you can easily select.
custom everyone. We directly go to custom here right now. And in the custom here, you see a sample here. sample here that says 40909.
So here, go ahead and click on custom. whatever we want here, okay? So currently it is general. We delete this
here and in the sample, we see 40909. What is 40909? It's the date series. In this date series here, okay, if I we will go ahead and write here, for example, D right now I see one.
I type D again, I see zero one. Oh, that's quite fantastic here. slash. Now I need month. So I can type M once. I'm seeing one. M once more here, zero one. M once more
here, January. M once more here the full spelling here. So triple M is getting me the abbreviation. One more M is getting us the January here. We put a hyphen once more. Now we need year. So we can use Y Y here and
accordingly we get the year. After this, maybe I want to go ahead and get the weekday. How to get a week name here? We can type D again, one. D again, zero one. D thrice abbreviation here. D four
times that is Sunday here. It means that by we can go ahead and write any combination. We can go ahead and write any combination and write any combination of D D M M Y here and format as you
of D D M M Y here and format as you want. number keypad for one. Use that upper one here.
Okay? Not the number keypad one, the upper one Not the number keypad one, the upper one here. date. You simply want to see here the month
and the year. You can go ahead and do that too. You can go ahead and do that See? So the entire date is at the back end. entire date. You would simply want to show the month and the year.
So likewise, you can go ahead and type any permutation combinations here and you can get the custom formatting. Go ahead, write this, click okay and see Now here we go ahead and click okay. Let's see the another option here.
Let's talk about here profit and loss, right? So we want this profit and loss thousands. So let's go ahead and select profit and So we are going to the profit and loss right now. Control shift down arrow key
here. And we open the number format right now. In the number format here, we will again go to the custom here. So select the again. And go to custom right.
custom here, you will find that there are automatically some custom formatting codes are written here. Yes, there are already some custom formatting codes are And when we go here to simple number format also, we do have other options
So if we click on the number formatting here first, if you click on the number formatting here first, you have a checkbox called as use thousand What does this thousand separator does? Okay, it goes ahead and identify
wherever the thousands are. Correct? If I check on that, a comma appears and that comma identifies the thousand values there. If I go to custom right now here, in
here also you will identify that oh, there are commas here too. are thousand separators. So wherever the thousand value is there, we can easily identify that. And if you will see the custom
formatting here, there are multiple custom formatings here with the proper thousand separators right here. However, none of them is giving us thousands, millions or billions kind of values here.
So how do we go ahead and identify the thousand right now? So if this is the value here, hashtag comma hashtag hashtag zero, okay? So it zero signs here. What is the zero sign here? Zero sign
here, for example, see very carefully right now. After this zero here, I'm selecting the third option right now. The third option and we have pound comma that is thousand separator and then we have four more signs and then a zero.
type dot period here and then zero and zero, see what happens. what happens. And one more zero here.
I type one zero, it is giving me one single rounded of decimal point, correct? If I type one more zero here, it is giving me two rounded of decimal points. If I type one more zero here, it is giving me three decimal points. If I
type one more zero here, now it is typing exact zero because now the digits are not there. So zero identifies one single digit can be entered. Zero identifies one single digit in this custom format here.
What the does this sign represents here, the hashtag sign or the pound sign here? If I go ahead and delete this sign here, it remains 844. If I go ahead and delete this sign here, it remains 844. Okay, what does that mean here? That
these hashtags that we are giving here, it means variable digits can be there. there. Right? So this is uh if we want to move forward, okay, and we want to go ahead
and make sure it is it go as ahead and identify the thousand and at the end it writes K, that's how we write it. So we are saying that
there can be any number of digits. Find where the thousand is, okay, and give me two decimal points. So 844 here can be written as 0.84K.
Can we write it like this? What we are writing here is this. number of digits before the comma. It means you go and find where the thousand Once you figure out where the thousand is, you separate it there and after that
you give me two decimal points here. And at the end we are typing K. We are Go ahead type this out. Even if you don't understand it now, it's fine. Type Click okay, you will see the changes here and you will immediately know what
here and you will immediately know what we are what we are talking about here. if you are writing for example here uh 4,500, right? So now this gets changed
into, it identifies that, four is after four there is a thousand it will put a thousand separator here. Okay? And this is 4500. Now when we go ahead and format it here and we're asking it that see,
after that give me decimal. So it will identify the four here. It gives us the identify the four here. It gives us the decimal here as 4.50. So as soon as it sees that after four we have a thousand separator, it gives you
a decimal and after whatever is written afterwards, it will convert that into a decimal point. If your number is higher, okay? For example, if your number is let's say like this here,
how will this get changed here? Because after 12 the thousand separator will come in, correct? So here it will go ahead and write like this 12. ahead and write like this 12. three.
this out here everyone and click okay and you'll see the changes there. Let's go ahead and click okay everyone once you have written it here and you will see the options here. If you want to convert this into
if you want to go ahead and convert this into thousands, so we have here we have this here. We can have comma zero and point zero zero k point zero zero k. In a similar manner if you want to convert something
number, you want to convert that into millions, then you go ahead and write here and two commas top zero zero and millions here. So this is for converting it into millions. And now there is nothing that
converts into Indian currency that is lakhs, 10 lakhs and all that. It only gives us option for thousand separators here. So 100,000 likewise.
It doesn't work with Indian currencies, but yes, if we want to convert this into but yes, if we want to convert this into millions, we can. Indian currency, it might change into text and then you will not be able to
text and then you will not be able to use that as in calculations further. are these I'm simply providing you with this here because this is used a lot
in creating reports. This is called this is custom formatting for numbers. In Excel we call it custom formatting. The same formatting we call in Power BI is dynamic formatting.
When number formatting and date formatting. conditional formatting, okay? To learn about conditional formatting
here, what I'm going to do here is I'm going to copy paste everyone. formatting and I'll add a new sheet. I'm going to name this new sheet as conditional formatting.
formatting here few more options here. Okay, I'm going to go ahead and copy some 15 rows from So from the order ID till the profit and loss here, okay? Just select this and
copy and paste here please. Just this much portion, leave the date Copy and paste it here please. We'll talk about conditional formatting. We it, right? And we have seen the simple ones
here based on the numbers. Now here we'll talk a little bit more about conditional formatting. For that I want you to be ready. Please copy paste add one more sheet here and let me know when you're ready.
simply go ahead and add few names here. Okay, this is going to help us in writing few things and okay? So I'm simply changing few names You can go ahead and you can choose your own names. I'm writing few names out
here. Can use a number format Ah it's okay. I will check it out what we are writing and what it is working out. So give me an hour right during the last
five minutes we will figure it out. You know that it works we use that in are going to talk about conditional formatting right now. So as we have seen before we have deleted the duplicates right?
How about we simply want to go ahead and highlight the duplicates? highlight the duplicate instead of simply deleting it out. that. So let's go ahead and select
the order ID remember that in conditional formatting also we will not confuse the conditional formatting. Okay? So go ahead and select the order ID here without the header again.
And we go to conditional formatting here we go to highlight cell rules and here we have duplicate values. Please follow along with me. Select the numbers here okay from two to 15.
Go to home tab conditional formatting highlight cell rules and duplicate highlight cell rules and duplicate values here.
It will give you a pop up here. And read this here it says that the duplicate values okay these are the duplicate values it will get good with that. However when you go to the drop down
here it also gives you the option of unique highlight also. It highlights the unique values here you must be thinking what is the use of this So you can use the unique value highlight whenever you want to figure
out that which product has been sold out only once this month or which particular vendor has been contacted only once this month. So whenever you want to go ahead and figure out which one of these has done
just one single transaction okay we also have the option to highlight unique have the option to highlight unique results here.
Highlights we it is highlighting the duplicate. We're good with that. That one order order ID is repeated multiple times. Maybe inside that one order ID we have multiple products that has been set. That's why it is here.
So it is not essential to delete that, but we now know that there are maybe there are multiple products that is getting added inside one single order Let's click on okay here. So this is how
someone has asked me how to highlight this here. So I'm here. duplicates, right? Now usually when you're in conditional formatting, okay, we have few more options here like data bars,
Okay. So let's go ahead and see what these So let's go ahead and see what these data bars, color scales or icon set are. So these are some visualization options everyone. So when we do not have much
space to create a full-blown column here and we have a huge table like this and we want to still visualize this properly here. We can use these three options here that is data bars, color scales and icon set.
So let's go ahead to the number of customers here. Before I move forward, are you able to highlight duplicates? Now let's go ahead and see data bars everyone. To for the data bars here, I'm selecting some number of customers here,
okay, and we are going to conditional formatting right now. We are going to And in data bars go to more rules. There's a reason I'm asking you to go to more rules. In real time also, if you're highlighting the entire sheet there,
go to data bars and click on more rules. Whether it is color scale or icon set, always click on more rules. Why? Because we have to see at the back end what it is actually going ahead and applying the rule, which rule it is applying at the
So click on the more rules here. highlight here the format all cells based on their
values and how it is formatting right now data bar. In the data bar here it is fetching automatically fetching the minimum value and the maximum value here correct and this is solid fill and if you want
here okay let's change the color here. you want to change you can right now we are good with that.
Change the color click okay you'll have the bar here. option to change the direction as per your discretion.
This is left to right this is by the context. data bars inside individual cells and you are still able to see the and you are still able to see the pattern.
rules no changes here it is highlighted. We simply change the color here and we We simply change the color here and we click okay If you click okay this is how it looks like.
okay if I change any of this here if I change this to four the data length of data bar changes. If I change this to five data bar changes. So as per the value is written inside it
accordingly the data bars will keep on changing. data set and we still want to see some kind of pattern there. So that is data bars everyone now let's look at here the second option that is
Color scales are basically heat maps on the weather maps so we all have seen the dark blue If it we are going towards the higher more red.
out here. Let's go ahead and select some cells out Let's go ahead and select some cells out here.
go to color scales and we click on more rules, everyone. We go to color scales and we click on more rules here. dialogue box here, the format style here is the two color
scale right now. Look at this here. It is a still format all cells and it is In the two color scale here, it is giving us the minimum value and the maximum value, right? So, in this what is happening is it can simply highlight
the extra it can highlight the lowest values and highest values in two different colors. However, we will not be able to see the extreme extremity of the values, right? So, we change it into three color scale.
change this to three colors. When we change this to three color scale here, now we get a better we now we get a better option here. Reason is now we have a midpoint.
So, it is checking the median right now, exact 50th percentile, the midpoint here. So, half of the data is here, half of the data is here. So, now we can see the extreme low values and the extreme high values also.
Right? And right now the midpoint is automatic, which is median right here. However, you do have the option, okay, to select any number that you want here. percentage, you can set a formula here too.
Currently, by default it is going to be exact midpoint and most of the time it serves well. So, this is midpoint, everyone, and we click on okay here. What we are changing? Nothing.
you change it to three color scale and you can go ahead and select any color Right? So, you can go ahead and change the color here. However, minimum value, maximum value will be taken automatically here.
and yes, this is how the heat maps are created in Excel. And see how easy it is right now to see what which are the general values in between, which are the extreme values here.
Now moving forward here, let's look at the profit and loss. Now in profit and loss, okay, here we have a condition. The condition here says that that if the profit, okay, if the profit is, let's say
say less than less than 1000, okay, we want a red down arrow. We want a red down arrow.
more than if this profit is more than If profit is more than let's say 3000, okay, we need a red we need a green up
arrow. We need a green up arrow. If anything is between these two values, or anything is between these two values, okay, between 1K and 3K here, okay, between 1K and 3K here, okay, it can be a yellow circle or just
an arrow that will not show any direction, right? It can be a yellow circle or no direction here. give here. Okay, based on these conditions here. So
right now? To give the icons here based on few conditions, we select these cells here, right? I've been selecting these cells We go to conditional formatting, we go to icon set and we click on more rules,
to icon set and we click on more rules, everyone. the format is style is icon set, we are good. But now let's go ahead and set the rules by ourselves here. Please ignore the icon style for now. Please ignore
the icon style for now. And let's focus on the rules here, okay? So it says that icon is green colored circle. Or do we want icon is green
color circle? No, I want a green up arrow. So I go to the drop down here and I will use a green up arrow right now. Now I need the green up arrow when the value is when the value is greater than 3000,
right? So here if I look at the value right now, it is showing percentage. It is not a percent, it's a number. So we go to the percent drop down here and we change this to a number.
So step number one, we change the icon. Step number two, we change the type to number here. So first go ahead and change both the types to number because based on number we want to apply the values.
Once we have changed to numbers here in the third here, the third step we change The sign here is greater than equals to greater than here. So it should be the value if the value
is greater than 3000. So here we go ahead and write 3000. arrow. Done.
yellow circle. So there is a yellow circle. I'm good with that. And see it is writing here that when value is less than equals to 3000 and greater than equals to what? 1000.
greater than equals to what? 1000. This is specifies between 1000 and 3000. as soon as you type the 1000, it automatically writes here when it is When it is less than 1000, I want to go ahead and select the down arrow here.
type four. Okay, in the fifth step we change the And every rule will get written automatically itself.
it says custom because we are not simply selecting one single style here. You do have the option to change it to anything else. anything that you want. I'm keeping it a
anything that you want. I'm keeping it a circle right now. Wonderful. So here we go. This is the icon rules
here. You can go ahead and set the icon rules as you want and here we are. formatting here, in this conditional formatting here, using no matter how many rules we use here, have you observed one thing here?
We are always going ahead and highlighting, highlighting highlighting only cells, whether we are highlighting color, icon, anything. right? In real time this doesn't work like
like this always. Okay, for example, I want to go ahead and highlight, highlight or I want to highlight entire row. I want to highlight entire row, entire row, okay, based on, based on
row, okay, based on, based on salesperson, based on salesperson. So I want to highlight the entire row based on the salesperson. So for example here, I will go ahead and copy paste these two names here.
So if I'm saying that I want to look at the Adam, automatically the entire rule should be highlighted, right? Because if I simply highlight Adam right now, if I simply highlight Adam right now,
it doesn't make sense because ultimately even if it is saying here that the sales is done by Adam, ultimately I will go ahead and look at the entire row that oh the this is are the corresponding rows and the details there.
Okay, even though you will read here that okay this is 2.3 K, this is the lowest profit that I can find here, you will still go ahead and see the we having the loss? So ultimately you will go and check the
corresponding columns here. But conditional formatting by default But conditional formatting by default cannot highlight the entire row. make it to actually highlight the entire row.
I'm getting okay I'm going back to formatting here here. You can copy paste it from here also but so you can get a small place from here itself like this.
Okay. So just get few rows here like just 10 Copy it. pasting it here but I'm pasting it as values, no
Okay. I'm pasting as values, no formatting at So conditional formatting will be removed. Everything else will be fine.
here okay we have two names only Adam and Smith right now. We have two names Okay. So I will go ahead and give a
this is Adam and Smith right now. I'm keeping these two names here. keep Rose and Nathan also here. I will also copy paste them somewhere here like this okay.
paste it. So this is the list of the unique names here and this is where I am adding few names right here too. Great. Now our objective is
to make sure that the entire cell gets highlighted. have to make we have to understand how conditional formatting actually works. will you will say that okay wherever you
see the customers less than five, highlight that in red. ahead and find out find out that if customer number is less than five, okay,
color. If we are going to ask this question here, don't type it. Pay attention here. How does it actually reads it at the back end? It goes ahead and check each and every cell. All right. So it will go
and every cell. All right. So it will go ahead and check that is this cell is check that. cell less than five? Like this it will keep on checking. Like
this it will keep on checking. Okay. Once it will get the true answer, only it will go ahead and highlight that particular cell in some color, whatever particular cell in some color, whatever color we are saying here.
wherever the condition becomes true, conditional formatting goes ahead and actually highlights that part here. That So if it can do however by default conditional
formatting can work only on one single cell. That's the issue. It cannot go highlight. Reason is if our condition is checked the value less than five, okay, it will go ahead and can check only one value,
one condition. Is it less than five? Is it less than five? Is it less than five? Now understand here, if it goes ahead and understand here, if it goes ahead and check is D24 less than five, is C24 less
answer because these are not numbers, these are just text, right? It doesn't match the condition If you want to go ahead and highlight the entire row, we have to make sure
the entire row, we have to make sure that at the back end each and every that at the back end each and every cell here is turned as true. So I'm going to copy this here. I'm pasting this here to show you an
example right now. Don't need to do this. Okay, I'm just to show you the example I'm filling it right like this here. So what is the condition here that it should be less than that the salesperson
condition we are looking at, right? If that's the condition we are looking so let's check. Let's have a check everyone. I will go with the salesman here and I will ask you to check that go to this
equals to Adam? If it is Adam, I have to go ahead and not case sensitive. I have to give double quotes because If I'll go ahead and press enter everyone, this says true.
it will automatically find out true and true and it can highlight these cells wherever it is true. However, if I go ahead and drag it towards the right hand side, what happens? Let's check how does this looks
happens? Let's check how does this looks like here. is equals to Adam. So D19 definitely do not have Adam. It Same if we go ahead and check here, okay?
E19 is it Adam? Of course it is not Adam because it is a number. Right? If I highlight this here, it will be false too because it is checking at multiple cells out here. A9, B19, C19 and there is no Adam there.
So now we have to trick Excel into thinking that these are not false, these So if you want to go ahead and trick the Excel here, that's where we use the concept called as cell referencing. That's where we use the concept called
as cell referencing here because in each and every cell here we wanted to think that you are checking just one condition that is C19 is Adam. just one condition that is C19 is Adam. If C19 19 is Adam, every uh every
corresponding cell should be true, too. So this is where we use the concept called a cell referencing everyone. Now, what is this concept of cell referencing here? We have been using this always and I'm sure most of you are
already aware of the aware about this. When we go ahead and we give a referencing here something like this. So these are few let's say these are this these are few numbers here.
If I want if we will go ahead and I type equals to like this and we select and I select any cell like this here. Okay, it says 331 and if I change this value here, if I go ahead and change this value here right
now, it changes the value here. Why? Because currently we are currently we Because currently we are currently we are writing C31 and C31 here we are corresponding to C column and 31st row. This is called as relative referencing.
This is called as relative referencing. Why relative? Because it will change. Why relative? Because it will change. It will change if I go ahead and ahead and select this cell here right now and I drag and drop here, it will
What it is changing here? Let's pay attention right now. It is changing it attention right now. It is changing it will it is changing from C31 to C32, 33, It is not fixed. It is not always corresponding to the same value.
And that's what is happening here. If we are dragging and dropping towards other corresponding columns here, the column is changing to C D E A B C D E. That is because it is relative cell
referencing. So what we do here is we fix what we want and that fixing is called as absolute cell referencing. How do we define the fix here? We define the fix here with the dollar sign. We
sign. Right? So if I want to go ahead if I Right? So if I want to go ahead if I want to go ahead and fix column right now, so I type the dollar sign here. I give the column here.
Right? If I want to fix the row also, I we will type the dollar sign again and here we will have the row number again. So if we give the dollar signs like this are fixing the, we are fixing the entire cell.
So if I will now go ahead and select C31 here, okay however I put the dollar sign in front of C, I put the dollar sign in front of 31 now and now when I press enter and drag and drop it is always going to remain 34. Why?
Because now we have fixed the cell and now this doesn't change. So this this is good, very good here right? So this here helps us to fix the entire cell. Fix the entire cell.
However there might be there might be cases where we want to fix only column or only cell. So we can do that too okay. If in the absolute referencing you can choose to fix only column, you can also choose to fix only row.
choose to fix only column the it will look like this A 23 so dollar A 23 fixing only the column. So 23 will change to 24 25 26. A will remain A throughout. If I go ahead and I want to go ahead and
row so it will look like this B dollar four. So the four will remain same but B C D will change right? So this is a very very very important and very very basic concept in
Excel that is cell referencing. Now we use the cell referencing here to trick Excel into thinking that if the entire row is true. Excel here. Now what we want Excel to think that
right? So I will go inside the first option here where the C19 is C19 is and I'm going to fix only
C19. So no matter where it goes in the whole 19th row or in the 20th 20 21 any row it goes here.
same that is same. So if I will write dollar C 19 Adam and dollar C 19 Adam and I apply this condition to entire row. I apply this condition to entire row. Now everything becomes true.
When everything becomes true here, okay we have checked Excel into thinking that the entire row is true and it can go ahead and highlight the entire row now. So that's how we go ahead and we check
absolute referencing by giving a simple dollar What you are supposed to do right now? Okay, please go ahead Please go ahead and type one single thing. Type equals to here.
Select Adam right now wherever Adam is written or any name that is written here at first, right? And put a dollar sign in front of C. Then type equals to here
and give Adam here and double quotes here. Please do that. Once you have written this, make sure that you're copying this too. How you are copying this here? The answer will be true, right? How are
here. Control A, control C. You could click outside here, you control A and you control C.
going into the formula bar copying the entire formula here. entire table everyone. Please go ahead and select the entire table without
and select the entire table without header again. table. After selecting the entire table without the header everyone, okay go to conditional formatting.
Go to conditional formatting. And this time we click on new rule because we have to give our own formula. We click on new rule everyone. This time when we click on new rule, same dialogue box appears.
same dialogue box appears. Same dialogue box will appear here. In this dialogue box, select the last option use a formula. Select the last option use a formula because we want to use a formula here.
again. What does it say? It says that format values where this formula is true. Right? So we go here, okay? And we can simply paste it here. If you are trying to paste it, it is not
getting pasted, just type out manually equals to dollar C 19 type it manually. And while you're typing the atom, make sure you give the double quotes.
or you're able to paste it properly here, please see currently there is no format is set. Currently no formatting is set here. We have to go ahead and give the formatting. So once you have written the formula
here, click on format.
whenever we are doing conditional formatting everyone, right? We think that conditional formatting only fill up the colors here But that is not the only case every time. So because when you click on the
time. So because when you click on the format here, a formula, this is C 19 equals to Adam. Now we click on format. Another dialogue box opens up and this is fantastic. Reason is you can change the number
formatting too. If it matches a certain criteria, you can go ahead and give the font style for example bold italic here. You can go for example bold italic here. You can go ahead and select a color here
is easily visible. So you can select the font style here. You can give a border, colored border here or you can fill a certain color at You can also go ahead and decide any pattern and style you're looking for.
pattern too. So there are a lot many possibilities of formatting here. Go ahead and explore that, but not now, later. Right now, simply select the font here everyone. Okay?
ahead and choose a color here. That will also do. And this is done here. Click okay everyone.
So once you have decided the formatting, click okay here. you will see a preview first. You'll see a preview first.
after the preview here, if you're if you're good with the if you're if you're good with the preview, go ahead and click okay.
highlighted? Names are not case sensitive. already told you number formatting is a facade.
At the back end formula, the numbers are still same. one end conditional formatting. Yes, you can do that.
let you share the screen and I will show you around. You are not able to find it because it is not exactly in front of it, you but is not exactly in front of it, you but it is there.
Right? Now inside this here there is one more Now inside this here there is one more option that we can do. that we can do here. So please see it here everyone.
In the data tab I am giving the list right now. I'm giving the list right now for these individual names here, right? I'm giving the data validation list here. So I'm going to give the data validation
list here. Let me fill up some color so that it is identifiable here. Right? And now here I'm saying that whatever name I select whatever name I select highlight that name here.
does it become? So now here it becomes that dollar C 19 should be equals to whatever I type here, okay? I type here, okay? And now it becomes dollar G dollar 19.
We will fix the entire cell because it's a drop down, right? And when we apply to a entire cell here, we don't want it to change anything. So now this is C 19 and G 19. If I select here
and whatever I select here, okay, it will should go ahead and highlight the So if I select rows here, it will change. with a very simple data validation applied and changing of the row here.
applied and changing of the row here. So let me show you how does it work. part here. So I'm selecting inside this, copying it.
Now selecting the entire table. I'm as usual going to the home tab and row. Inside the new row, we highlight use a And use a formula here. I'm pasting the formula which says that C 19 should be
formula which says that C 19 should be equals to whatever is selected at G 19. formatting here. Okay, filling up with the yellow color right now. And click okay here and click okay here.
Now see what happens if I go ahead and change it here, it highlights. table or one single range can have multiple conditional formatting applied.
They usually overlap each other. If I select Adam, the fill color is remaining. If I select anything else here, it goes back to the previous color and it is highlighting rows in yellow here.
So like this you can actually apply whatever we have learned so far and you can have the dynamic formatting here. Right? It writing Adam here, we are selecting a cell where the names are written
and that cell has a data validation list. share this sheet with you. And conditional formatting is pretty fun And conditional formatting is pretty fun to work with.
be aware about of conditional formatting in real time. Never ever give conditional formatting if your data is beyond some 10 beyond the 1000 rows Even the 500 rows. Reason is at the back end it is actually checking like exactly
like this. So it is going to exact cell and it is checking the entire formula that you're writing there. And because of that your file will file size is going to be huge. When you
file size is going to be huge. When you open that file it can lag a lot. Okay? It might not open if it lags too much. So the processing becomes very So when you're applying conditional formatting, save the conditional
formatting for the smaller reports and the bigger reports. Even the bigger rows. Okay? And when we have 500 rows we don't apply colors and formatting like that. We apply colors and formatting but
within one single screen we want to show some pattern. So avoid that kind of conditional formatting everyone. This is few tips about the conditional formatting here. Right?
have learned about cell referencing because this concept here will be applied to the functions that we are going to learn forward. Now here there is a separate sheet called as logical functions here but we
have right now only 15 minutes here. So we'll discuss the basic logical functions here and when I when I see you tomorrow, we'll start properly with the Right? We'll start entirely with the and new
But here let's talk about few things about the functions here and we are going to discuss text functions. right now and why do we use the text functions here?
where the text functions are generally used. Okay? rid of all the numbers. Let's talk about text here.
and few of the text functions here major problems that we face when we are getting data from somewhere, okay? For example, do you see Adam is written here? Right? Let's talk about region here every here.
This is north. Correct? This north is correctly written here. Maybe somewhere else we have extra spaces coming up here. If there are extra spaces written, okay, are we able to identify that?
Even if I zoom in, I go for example to middle here. We have extra space here. I go to north here. Extra space here. spaces. Are we able to identify that there are
Are we able to identify that there are extra spaces right now? experienced professionals out there. Have you ever done that? Whenever you're looking at, okay, your data set going here like this, what you do?
center, right? And or you try to make it left to make sure that it is patterning right. That's how we do it.
Yes. Now what happens here is we are not able to identify any number of time. This is where this is where the
trim functions comes in. Trim function helps us in removing extra spaces, removing excess spaces, okay? Removing extra spaces.
And I'm writing extra spaces, okay? Not the spaces that are required. So first let's see okay how does it remove the extra spaces here? To so this is not here, right? I'm going to go
How do I apply a trim function? We can type equals to, we can type trim here This is trim function here and I will simply go ahead and select a I will simply go ahead and select a cell here
here and press enter and press enter. Okay? Very very simple function here. I'm going to press enter right now.
is how the formula looks like. Now if I go ahead and drag and drop Now if I go ahead and drag and drop right here, everything is properly here. The problem here is how do I verify this?
right? That yes, it looks fine here, but how do I verify it is correct? how do I verify it is correct? So now see here very carefully. Here this is the trim function. Let me add a row here
Right? So there is a way to do it. This is region here, okay? And it is I am saying that this is trim region here. This is trim region here. So here I'm going to go ahead and count
Count on region column here. What I what do I mean by count the region column here? I am simply going to count the length of the region column that how many words are inside it.
So if I type equals to here and we use the function length l e n this is also a text function. L e n function here will help me to count how many words are inside it and yes its spaces
are considered as words. So if I apply the length function here and I drag and drop everyone right now I will see right here that see if it is will see right here that see if it is not it should be five.
Again this north is five again this west is eight it means we have something bad is eight it means we have something bad going on here. region if I go ahead and count the trimmed region here okay I am going to
apply l e n function again and I type the trimmed region here. And when I type this here okay we'll have the exact we have the correct have the exact we have the correct answers here see.
So this is how we go ahead and we figure it out. Now you must be thinking that in now. In real time we apply the trim function you add a new column you apply a trim function and then you copy and paste his
So in real time we do this but then we copy and paste the values back. So every time we use the trim function okay within the nested functions. So if I'm applying V lookup right I will apply
the trim function so that there are extra spaces in the lookup value okay we will not be hampered by that. Like that. the functions as a nested function to make sure the extra spaces if they are
there they are not hampering us. This is trim function and the length This is trim function and the length function. apply? Let know that place.
never correct you that's the problem here. And that is why that is why I have shown you the length here. No matter how you no matter how you align it. It doesn't matter. Okay, it is always going to have extra spaces. That is why
going to have extra spaces. That is why we have to apply trim function. it is pretty simple, right? I'm applying trim function. I'm asking it to get rid of extra spaces. So wherever the extra spaces are, it is
getting rid of that. And how do I know that? Because when I five. However, if I count the length for this north here, this is 11. But not for this north here.
So trimmed region here, okay, it is trimming up extra spaces here. cell? There might be some upper limit, but I don't remember that.
There is some upper limit. I don't remember that, but usually you can enter the paragraphs here paragraphs in one single cell. paragraphs in one single cell. So I don't know what is the limit.
And about the trim region and here, it is it will not only work on this here, it works on the entire addresses too. So for example here, let's say here I have got house number, okay, then I have got a street name
here. I have got a street name here. And then we have extra spaces because we are collecting the data from somewhere. This is where I have some state and then here I have got country right now or maybe I have some blog post and I have got extra
spaces here. So even if I have a sentence like this which has multiple spaces like this and if I go ahead and apply trim function on this, if I apply trim function on this, it only gets rid of extra spaces.
See, it is still leaving one one space afterwards. Because it understands that there should be one space only.
function here only leaves the extra only get rid of extra spaces and leaves down one space here. I hope Vishal this makes trim understandable here.
trim and the length function everyone, okay? Other and the same similar manner okay? Other and the same similar manner here. We have functions like upper, lower and proper. Okay, these functions are also used as nested functions most
of the time. So for example right now this proper. All three are in the lowercase, correct? So if I go ahead and type equals to upper right now and I choose this here,
uppercase. It will go ahead and write it in the uppercase. lower right now and in the lower here I choose this uppercase that is in caps
right now, it will become a smaller case. It becomes a smaller case. What's a proper case here? So if I go ahead and type proper right now, okay, it gives us the first letter capitalized. This is
the first letter capitalized. This is proper. here, okay, you again copy and paste these values. You don't leave the functions here right away or you use it inside as nested.
So that's upper and proper everyone. Upper, lower and proper. It simply changes the case that we are writing in.
functions everyone and just to clean the data that you have got here. If you have extra non-printable characters, so sometimes we have some question marks so sometimes we have some question marks or windings
getting data from somewhere else. Usually in front of numbers, right? In front of numbers, sometimes numbers will come written like this. So it you can use clean function there. Okay? So clean function gets rid of
non-printable characters. It gets rid of clean function is there. Okay? This clean function is there. Okay? This clean function here, it gets rid of it removes non-printable characters, non-printable characters.
such a way. So these are few text functions here which can help you to properly format your data and to clean your text data Of course, there are more text functions. So as in when we move
here. We will go through lookup and reference functions. Now while going through these functions here, okay, remember that we will go through the most used functions. Okay?
basis of that you can learn other functions. We move on to pivot tables and pivot charts. So let's go ahead and see the functions today.
step one is logical functions. There is a file called as logical functions So we'll in the lesson three, conditional formatting, we have the Excel sheet called as logical functions.
Let's begin. First is this is our simple data set, right? We have been looking at the this data set since they start. Now here I would like you to simply go ahead and add few columns. How do we add columns
Control a space bar, right? And control shift plus plus plus. Add five to seven columns please. Add five to seven columns here and for the purpose of explanation, I will add one more one row up here.
So if there are any explanations, I can go ahead and write it in the row above. Here we go. Right? So let's add few columns here and Right? So let's add few columns here and one row just above the
So control space bar, okay? Select column, control space bar, select column. I'm writing this here. Shift space bar, shift space bar selects row. Now whatever you have selected, whatever
you have selected, if you want to add, if you want to add the selected object, or cell. You go ahead with control and the plus control and the plus sign. You might have to reach it with control shift and
plus because plus is there with this shift there. If you want to delete any selected object, then it goes with control minus. Control plus might be control shift plus if you don't have a number keypad.
There's more to logical functions here. Okay? So let's say let's say here, okay? We
they have done for that particular transaction, how much profit they have gained out of that. Let's say they've decided that if profit and loss, they have decided that if profit and loss is greater than
3,000 is greater than 3,000, okay? They are going to profit and loss is greater than 3,000 or not. If it is greater than 3,000, they want to pay 5%, otherwise they want to pay
This is a this is what they want to check. Now if we want to check this here, we can easily check with equals to, right? We can check whether is this greater than 3,000.
By simply using comparison operators, we can check it because there is absolutely no calculation here. It is a simple logic that we want to check. However, when I'm checking this logic here, the answer we get here is in by
The answer we get here is by true and false. However, can we send this particular file across exactly like this and tell give 5 percent, wherever you see false give them 3 percent.
it is? And tell them to go ahead and decipher the meaning of true and false. No. So what we need to do? We need to right
need to right now replace these true and false with the proper output. So in Excel here or anywhere globally in any tool here, okay if you want to
replace the logical answers, okay if you want to go ahead and replace the logical answers like true and false.
