Advanced Features of Ms-Excel ,sort the data, filter the data, use the goal seek feature, print a worksheet

Microsoft Excel, a versatile spreadsheet application, offers a wide range of features that cater to both casual users and advanced data analysts. Beyond the basic functions of creating spreadsheets and performing calculations, Excel provides several advanced features that can significantly enhance productivity and data analysis capabilities.

**1. Data Analysis Tools:**

* **PivotTables:** PivotTables are powerful tools for summarizing and analyzing large datasets. They allow you to quickly create cross-tabulations, group data, and calculate aggregates.
* **Data Validation:** This feature ensures data integrity by setting rules for input values, preventing errors, and providing helpful feedback to users.
* **Goal Seek:** Goal Seek helps you find the input value that produces a desired output. For example, you can determine the sales volume needed to achieve a specific profit target.

**2. Formulas and Functions:**

* **Array Formulas:** Array formulas enable you to perform calculations on multiple cells simultaneously, simplifying complex tasks.
* **Conditional Formatting:** This feature allows you to visually highlight cells based on specific criteria, making it easier to identify trends and outliers.
* **Custom Functions:** For advanced users, Excel provides the ability to create custom functions using VBA (Visual Basic for Applications), extending the functionality of the built-in functions.

**3. Data Visualization:**

* **Charts and Graphs:** Excel offers a variety of chart types, including line charts, bar charts, pie charts, and scatter plots, to visualize data effectively.
* **Sparklines:** These tiny charts can be embedded within cells, providing a compact way to display trends and patterns.
* **Power BI Integration:** Excel can be integrated with Power BI, a business intelligence platform, to create interactive dashboards and visualizations for larger datasets.

**4. Automation and Macros:**

* **VBA:** By using VBA, you can automate repetitive tasks and create custom tools to streamline your workflow.
* **Macros:** Macros record a series of actions and can be played back to automate tasks, saving time and reducing errors.

**5. Collaboration and Sharing:**

* **SharePoint Integration:** Excel can be integrated with SharePoint to facilitate collaboration and version control within teams.
* **Online Collaboration:** Excel Online allows you to work on spreadsheets simultaneously with others, enabling real-time collaboration.

In conclusion, Microsoft Excel's advanced features empower users to perform complex data analysis, create sophisticated visualizations, and automate tasks efficiently. By leveraging these capabilities, individuals and organizations can make informed decisions, improve productivity, and gain valuable insights from their data.

1. ADVANCED
FEATURES OF
MS-EXCEL
Presented by : NIKHIL SHARMA

2. LEARNING OBJECTIVES
SORT THE DATA
FILTER THE DATA
USE THE GOAL SEEK FEATURE
PRINT A WORKSHEET

3. SORTING DATA :
Sorting means arranging the given data according to a
particular order, either in an ascending or descending
order.
To sort the data, use any one of the following methods.
METHOD 1 :
1.Select the cells or a range of cells.
2.Click the data tab.
3.Click the AZ OR ZA button to sort the data in an
ascending or descending order respectively.

5. METHOD 2 :
1.Select a cells or a range of a cells.
2.Click the sort button in sort and filter group
of data-tab. The sort dialog box opens up.
3.Select the column on which you want to
apply sorting.
4.Select the values from sort on drop down
menu.
5.Select the order of sorting from order drop
down menu.
6.Click ok. The data will be sorted.

7. FILTERING DATA
Filtering data mean working with only
selective range of cells, it help to hide
unwanted data temporarily. After filtering
the data, only those range of cells are
displayed that meet the specified criteria.
These are following two types :
1.Auto filter
2.Advanced filter

8. 1.AUTO FILTER :
To use Auto filter feature, follow the given
steps:
Select the cell which are to be filtered.
Click the data tab.
Now, click the filter option from sort and
filter group.
Small arrow will be added above the
selected cell or column header. The
arrow is used to filter the data.

10. 2. ADVANCED FILTER :
It is used to filter the data in multiple field using
some specified criteria. To use advanced filter,
follow the given steps:
1.Click the data tab.
2.Click the advanced in sort and filter group.
The advanced filter consists of the following options:
List range The range of cells that you want to filter.
Criteria range A range of cells in which the filtering criteria
are specified.

11. 3. Click the list range box. To enter the range in it.
4. Click the criteria range.

5. Click ok button.

12. USING THE GOAL SEEK
It is a feature for fixing a specific result for one cell by
adjusting a value in another cell.
Example : A student needs at least 75 marks to get
distinction in exams. So we need to find how many
marks in science are required to get the distinction in
exam.
Subject Marks
Hindi 65
English 60
Maths 85
Computer 72
Science
Percentage 70.5

13. To use goal seek, follow the given steps :
1.Create a table in MS- Excel and apply average
function to find the percentage.
2.Click the data tab and click the What –if Analysis
drop down option.
3.Choose Goal seek option.
4.Click the ok button the goal seek status dialog
box appears that shows status of Goal Seek
operation click ok.
5.The result will appear in the specified cell.

15. PRINTING A WORKSHEET :
To print a worksheet, follow the given steps :
Open the worksheet you want to print.
Click the file print. The print window
appears.
Select the desired option and click print
option.

16. THANK YOU