Attend FREE Webinar on Digital Marketing for Career & Business Growth Register Now

Data Analytics Blog

Data Analytics Case Studies, WhyTos, HowTos, Interviews, News, Events, Jobs and more...

Advanced Excel Tricks every Data Analyst Should Know

5 (100%) 1 vote

There are several types of data. It varies from simple sales data, customer data, inventory data, supply chain data, to even scientific data to social statistical data. The choice of which tool to use depends on the field of study. Here, we will understand the tools in Excel that can be used for Data Analysis.

Data Analytics tools and software are typically used to sort through academic, scientific and organizational statistical data to identify patterns and establish relationships. These two things are what we refer to as Analysis.  Like “Data Mining,” data analysis techniques can help virtually any business gain greater insight into organizational, industry, and customer trends.

Analyzing data is an important tool to make decisions in all sectors of life. The ability to analyze data is a powerful skill that helps you make better decisions. Data analysis using excel is one of the powerful tools to analyze data. It is not arguable to say that it is the most popular tool for data analysis with its built-in pivot tables. Another reason why it is popular is that you don’t have to spend long periods of training to learn Excel to deliver simple data analysis.

Data analysis is not possible if the data is extremely raw and unstructured. Hence, before data analysis, one must know how to:

Data Analytics Course by Digital Vidya

Free Data Analytics Webinar

Date: 25th Jan, 2018 (Thursday)
Time: 3 PM to 4 PM (IST/GMT +5:30)

  • Enter data correctly & systematically into Excel
  • Clean unstructured data

There are a lot of text related functions you can use to extract & clean data, thereby getting structured data on which analysis can be done.

Analysis generally means numbers, right? But before that, we can even run a lot of calculations on dates and times.

  • Date functions come in extremely handy when it comes to quickly calculating delivery dates, payment dates, etc.
  • Similarly, time functions come in handy for example like time taken to complete a process, time stamps, etc.

Moving on from the cleaning up of raw data and calculations, there are a lot of features in Excel that are used for analysis of data.

data analytics using excel

Features in Excel

We will be using this small dataset to understand the concepts used for analysis.

  • Sorting

Grouping of data is extremely important when you’re working on a good number of rows, say in thousands.

Let’s take Sales Data as an example, you would want to see your data Product-wise, Store-wise, Date-wise, Sales person-wise, and so on.

A simple sorting feature can do this for you and you can scroll through your data way better and easily understand it.

Data analytics using excel

sorting in excel

Sorting can also happen on multiple levels, wherein, you can group your data into various layers, for example, product-wise -> store-wise, store-wise -> date-wise, sales person-wise – product-wise – date-wise, and so on.

  • Filtering

What if your data runs in not thousands, but tens of thousands or lakhs of rows? Even if you sort it, still the data will be way too long. Thus, you can rather filter it.

Basic filtering can be that you want to see data of only 1 store. So, you apply the filter and see data of only that store, completely your analysis and you’re done!

But there are certain advanced options as well, like, you can filter the sales data to see data of records only where the quantity sold is great than 100.

Data Analytics using Excel

Filtering in Excel

If you have a total of 50,000 rows of data, and you apply the filter of quantity as mentioned above, you may end up seeing only 100 rows of data instead of the full 50,000. This narrows your data to what you really want to analyze on.

There is something called as Advanced Filter also, which can run a filter on any permutation combination in your columns, the way you want to. It’s completely customizable in a way you want it to be. Again, it’s extremely useful when you have a lot of data, lakhs of rows, and you want to see only a particular set of it. For example, out of the entire sales data, you only want to see data of

  •  Mumbai city
  •  Andheri store & Goregaon store
  •  Sales done only in the month of April 2017
  •  Orders where quantity was > 50 only
  •  Green color products only

This entire combination can be created using Advanced filter.

  • Conditions

The 2 main ways to analyze using conditions are Conditional formatting & Logical functions.

Conditional formatting can help you highlight the data that you want, based on whatever conditions you provide, like,

  •  Sales value > 5000
  •  Quantity < 5
  •  A particular product
  •  A particular store
  •  Sales person
Data Analytics using Excel

Applying Conditions in Excel

Logical functions – IF, AND, OR, NOT are super useful to create various kind of analysis and calculations. For example, you want to give a 10% offer code on the next order to customers who have bought worth more than Rs. 5,000 from you. You want a list of 10,000 customers. If you sit and do this manually, that means you will check for each customer if he/she has bought worth more than Rs. 5000 or not, it will take a couple of days for you to complete this task and definitely there are great chances of human error. If a human error happens and 2 customers who have bought more than Rs. 5,000 talk to each other and realize that 1 has received the offer code and the other hasn’t, the entire reputation of the company crashes down in the minds of these customers.

Such a situation can be easily avoided using Logical functions. Not only will the reputation be safe, but also the work that took a couple of days to complete, would get done in a couple of minutes!

This was a very simple example, sales > Rs. 5,000. The conditions could be extremely complex, like the same one we saw before – give a 10% offer code to only the customers of,

  •  Mumbai city
  •  Andheri store & Goregaon store
  •  Sales done only in the month of April 2017
  •  Orders where quantity was > 50 only
  •  Green color products only
Data Analytics using Excel

logical functions in Excel

Logical functions can do this, again, in a couple of minutes only!

  • Vlookup & Hlookup

When it comes to fetching data from various data sets and putting them together, or comparing data sets, Vlookup & Hlookup are amazingly amazing.

For example,

Data Analytics using Excel

Vlookup in Excel

1) Your data set has tens of thousands of rows of data and you want to see the details of only 1 particular order. Using vlookup / hlookup, you can get your order details in no time. Just change the order number and you will have details of whatever order you need.

data analytics using excel

vlookup feature in excel

2) You have 2 data sets of sales and need to compare them. See if an item in list 1 is present in list 2 or not, and vice versa.

  • Pivot tables and charts

Considered the best feature of Excel, Pivot tables can do magic for you!

In no time at all, you can get analysis like,

  •  Product-wise, store-wise quantities
  •  City-wise, sales-person wise quantities

Questions like these often will come to mind,

  •  How much profit has each of my business vertical generated?
  •  How much revenue is contributed by stores in Mumbai city?
  •  What is the average quantity per order of Product X that a customer buys?
  •  What is the average sales order value in Delhi city?
  •  How many customers were added month on month?
data analytics in excel

pivot tables in excel

Pivot tables and charts are the answer to this.

Beyond simple pivot tables comes power pivot, power query, power maps. All these are built into 1 single tool by Microsoft called Power BI.

  • Statistical analysis can also be done in Excel using the Analysis Tool Pack.

Analysis Tool Pack is a special Microsoft excel plugin. If you need to develop complex statistical or engineering analysis, you can save steps and time by using the Analysis Tool Pack. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.

Data Analytics Course by Digital Vidya

Free Data Analytics Webinar

Date: 25th Jan, 2018 (Thursday)
Time: 3 PM to 4 PM (IST/GMT +5:30)

The Analysis Tool Pack includes many tools, some of them are described below,

  1. Anova: This is an analysis of variance. The Anova analysis tools provide different types of variance analysis. The tool that you should use depends on the number of factors and the number of samples that you have from the populations that you want to test.
  2. Descriptive Statistics: The Descriptive Statistics analysis tool generates a report of univariate statistics for data in the input range, providing information about the central tendency and variability of your data.
  3. Fourier Analysis: An engineering mathematic analysis method. It is interesting to see something like this being integrated as an ad-on for engineering data analysis in an excel. The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data. This tool also supports inverse transformations, in which the inverse of transformed data returns the original data.

    data analytics using excel

    anova in excel

  • Solver Add-In

This is very familiar to computer scientist. This tool uses the power of all the conditional statements used in all programming languages. Solver is a what-if analysis tool for optimization. It is an add-in licensed from Frontline Systems that has shipped with Excel for many years.  Solver helps to find an optimal value in one cell, called the target cell, on your worksheet. It has three solving methods for solving spreadsheet optimization problems.

  • GRG Nonlinear: The GRG solver is used for solving smooth nonlinear problems. There is a new Multi-start search setting which when used in conjunction with the GRG solver results in better solutions, escaping locally optimal solutions in favor of globally optimal ones.
  • Simplex Method: The Simplex method can simply used for solving linear problems. The Simplex solving method has several performance enhancements in Excel 2010 resulting in greatly improved performance for some problem types.
  • Evolutionary Solver: The new Evolution solver accepts Solver models defined in exactly the same way as the Simplex and GRG Solvers, but uses genetic algorithms to find its solutions. A genetic algorithm is a problem-solving method of an Artificial Intelligence aspect of computer science. A major method in pattern recognition. While the Simplex and GRG solvers are used for linear and smooth nonlinear problems, the Evolutionary Solver can be used for any Excel formulas or functions, even when they are not linear or smooth nonlinear. Spreadsheet functions such as IF and VLOOKUP fall into this category. 

    data analytics in excel

    solver parameters in excel

< A closing paragraph needs to be added like a Summary>

Summary 

Excel is a great starting point for anyone who wants to pursue data analysis as a career. It is easier to learn compared to many other software and also cheaper to purchase.

A small sized company / freelancer can easily run the operations of their entire business on Excel itself, not needing to purchase any expensive ERP applications. Once you learn Excel, you can build your custom systems as per your requirement.

Plus, Google sheets also becomes very easy to use as it is very similar to Excel.

The numerous tools that Excel offers can give a newbie or even a seasoned person to learn data analysis and applications of it. Once you are thorough with Excel and are using it on a regular basis, of course you must get into more advanced (specific) tools like Power BI for dashboards, SQL for database, etc.

Founder & CEO at DLTC.co and Blogger at YouTube.com/ExcelRush. Certified Microsoft Office Specialist in Excel. Trained 1800+ working professionals and management students. Strength is training in Excel’s Advanced features & functions, programming using VBA in Excel, also Power BI & SQL. Multifunctional work experience of 11+ years. Dancer at Heart.

  • Data-Analytics


  • There are 6 comments


    • 10 months ago

      Disha   /   Reply

      Good information given about how one can analyse data analytics using excel and seek information about the work which is going on.Thanks for sharing the blog with us.

      • 9 months ago

        Sahil Arora   /   Reply

        Thanks Disha for sharing your experience.

    • 10 months ago

      Albert   /   Reply

      This is excellent information. It is amazing and wonderful to visit your site.thanks for sharing

      • 9 months ago

        Sahil Arora   /   Reply

        Thanks Albert for sharing your experience.

    • 10 months ago

      Sunil   /   Reply

      The information provided is good and hoping it will help in preparing the analytics report for websites.

      • 9 months ago

        Sahil Arora   /   Reply

        Off-Course Sunil it will help you to prepare your analytics report.

        All the best!!

    Your Comment

    Your email address will not be published.