In Excel, you can filter a data set based on the kind of data.
The following types of data filters are available in Excel:
You can apply filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon.
When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter.
For example: if you have text data, it will show you filters related to text (such as text contains, begins with, ends with, etc.).
There is a sorting feature in Excel that can sort data based on text, numbers, or colors.
Here are some ways to sort data in Excel:
Data Validation allows you to enter only that data in a cell that satisfies criteria.
if you want to only have numbers entered in a cell, then you can use Data validation to do this. In case anyone enters something other than numbers, Excel will show an error and not allow that.
Data validation options are available in the data tab.
Data validation can be really helpful when you're creating forms in Excel.
For example: if you want a user to enter only their age, you can use Data Validation to make sure the cell accepts a numeric value only.
There are two popular ways to transposing data in Excel:
With Paste Special dialog box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option.
If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).
To do this, you need to use the Go To Special dialog box in Excel.
Here are the steps to select all blank cells in Excel:
Excel has an in-built functionality that allows you to remove duplicate cells/rows in Excel.
You can find the option to remove duplicates in the Data tab.
Here are the steps to remove duplicates in Excel:
Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.
Here are some differences between the regular filter and advanced filter:
One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables.
if you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.)
Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.
If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variable that can change, then scenario manager is the way to go.
if you're a regional sales manager and have four areas under you, you can use scenario manager to create different scenarios (such as):
Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.
if you're buying a car and you want to know how many month's installment you should opt-for so that your monthly payment is not more than $500, you can do this using Goal seek.
Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.
With Solver, you can specify what the constraints are and the objective that you need to achieve. It does the calculation in the back-end to give you a possible solution.
Excel Data Analysis Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel Data Analysis Tutorial||Excel pivot tables Tutorial|
|Excel Power View Tutorial|
Excel Data Analysis Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel pivot tables Interview Questions|
|Excel Power View Interview Questions||Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.