Excel notes

Excel notes

1. Q). What are the features of Excel?
1. Pivot tables: A pivot table is a summary of your data. It allows you create a report.
For example consider the following data
ID NAME MARKS
1 RAM 98
2 AJAY 95
3 AJAY 92
4 RAM 58
5 AJAY 68

Now click on insert tab and select pivot table. In “choose the fields to add to report, check
NAME first and then check MARKS. You will get the below output
Row Labels
Sum of
MARKS
AJAY 255
RAM 156
Grand Total 411

2. Conditional Formatting: You can use conditional formatting in Excel to quickly highlight
cells that contain values greater/less than a specified value. You can also Highlight the cells
containing Top/Bottom 10 values in a range of values. We can also Highlight Errors/Blanks.

3. Charts and Graphics : Excel allows users to create different types of charts. Excel also
enables users to use mixed charts, meaning that we can use/ combine two styles of charts. For
example, we can use the line chart and the column chart on the same range of data.
SmartArt graphics and tools are available in Excel. We can also insert different shapes,
symbols, 3D maps, Screenshots and pictures. To insert charts and other graphics objects, we
are required to go to the Insert tab.
4. Auto-Fill Data : Using the Auto-fill feature, users can fill data in series. For example-
values from 1 to 10, weekdays, months name, dates, etc.

5. Paste Special Feature: For example, by default, if we press the shortcut 'Ctrl + V', Excel
paste the contents similar to the copied content. Therefore, if we want to paste only some
specific part, such as the values without formatting or the formatting without values, we can
use the 'Paste Special' feature.

2. 6. Built-in Formulae: Excel contains more than 450 functions and formulae. It allows users
to perform basic to complex operations.To access the formulae in Excel, we are required to
go to the 'Formulas' tab.
7. Data Sorting and Filtering: Instead of using the 'Find and Replace' feature, users can also
use 'Data Sorting and Filtering' to quickly find the desired data. It allows users to filter the
data. We must go to the Home tab> Sort and Filter or Data tab > Filter to sort and filter
data.
8. Protecting with the Password: Excel allows users to password-protect their documents.
To protect the excel documents with the password, we need to navigate to the File > Protect
Workbook.
Q) What are parts of Excel window?
A. The Screen and its Elements: When you start Excel, you will automatically start in a new,
blank workbook.
1. Workbooks and Spreadsheets: An ordinary Excel file is called a “Workbook” and can
contain many spreadsheets and charts.
2. The Ribbon: The Ribbon with its Tabs and buttons is located at the top.

3. 3. Office Button: In the top left corner of the screen you will find the round
Office Button. Click on this button when you want to create a new blank
spreadsheet, and when you need to save it, print it etc..
4. Quick Access: is located to the right of the Office Button. Using this, you can save, undo,
etc., This toolbar can be customized as per your requirement.

5. The Workspace or spreadsheet: The workspace is located below the Ribbon. The
spreadsheet is a huge table with “columns” and “rows”. The columns are named with letters
like A, B, C etc.. and the rows are labelled with row numbers.
Wherever a row and a column meet, we have a “cell”. Each cell in the worksheet has a
unique name.
For example, the cell located where column C and row 4 meet is called “C4”.
A cell can contain numbers, words and formulas.
6. Formulas: Excel comes with many inbuilt formulas. You can type your own formula in
excel. Formula in excel start with = sign.
7. Sheet Tabs: The “Sheet Tabs” are located just below the worksheet, on the left side. This
is because you can work with multiple worksheets at once. An Excel file is therefore also
called a “Workbook”, because it is similar to a folder containing a number of sheets. The
Sheets are by default named “Sheet1”, “Sheet2” etc.,

Q. Explain different types of charts?

4. It is difficult to understand a lot of data. Charts allow you to illustrate your data graphically.
A chart is a visual representative of data in both columns and rows. Charts are usually used to
analyse trends and patterns in data
Excel has several different types of charts.
1. column chart:
 uses vertical bars to represent data.
 They are used to compare information

2. Bar charts: are similar to column chart but these use horizontal bars.

5. 3. Line chart
 the data are connected with lines
 making it easy to see whether values are increasing or decreasing over time.

4. Area Chart are similar to line chart except the areas are filled in

5. Pie chart:
 makes it easy to compare proportions
 each value is shown as slice of pie
 so it is easy to see percentage of an item to the whole

6. 6. Doughnut Charts: It is almost similar to a pie chart but it can show more than one data
series.

7. Surface Charts:
 allow you to display data across 3D landscape
 allow you to see variety of information at the same time along two or more
data series

8. XY (Scatter Charts): This type of chart is normally used to show the relationship between
two
variables.

7. Q. Write about Data Filtering?
If your worksheet contains a lot of data, it can be difficult to find information quickly.
Filters allows you to view only the information you need.
Filtering data: In the following example, I am applying a filter to below Students data to display only
the students from Vijayawada.
ID NAME AGE MARKS CITY
1 ANU 16 98 VIJAYAWADA
2 AJAY 18 95 GUNTUR
3 ARUN 17 92 GUNTUR
4 BABITHA 15 58 VIJAYAWADA
5 BINDU 18 68 GUDIVADA

1. Select the Data Tab and then click the Filter Command

filter city column
2. The Filter menu will appear.
3. Uncheck the box next to Select All to quickly deselect all data.
4. Check the boxes next to the data you want to filter, and then click OK. In this example we will
check VIJAYAWADA to view only Vijayawada student’s details

8. 5. The data will be filtered, temporarily hiding any content that doesn't match the criteria.
Final Output we get is shown below

In the same way we can apply multiple filters. For example if you want students details whose city is
Vijayawada and got more than 90 marks, we can apply filters on city column and marks column.