SPREADSHEET

S
Objectives
• Manipulate Columns and Rows • Sorting Data
• Insert • Use Primary Field
• Delete • Use Secondary Field
• Modify • Use Ascending vs Descending
order
• Manipulate Data in spreadsheet
• Numeric Data Formatting • Filtering Data
• Currency • Use multiple criteria
• Accounting • Use complex criterion
• Percentage
• Decimal places

2
MANIPULATE COLUMNS &
ROWS
Columns and Rows
• By default, every row and column of a new workbook is set to the same
height and width

• Excel allows you to modify column width and row height in different
ways.

4
Columns and Rows
How to modify column width? 3. Release the mouse. The
1. Position the mouse over the column width will be changed.
column line in the column
heading so the cursor becomes a
double arrow.
NB: With numerical data, the cell
2. Click and drag the mouse to will display pound signs (####) if
increase or decrease the column the column is too narrow.
width
Simply increase the column width
to make the data visible.

5
Columns and Rows
How to AutoFit column width?
• The AutoFit feature allows you to set a column’s width to fit its content
automatically
• Position the mouse over the column line in the column heading so the cursor
becomes a double arrow.
• Double-click the mouse. The column width will be changed automatically to fit
the content

6
Columns and Rows
• You can also AutoFit the width for How to modify row height?
several columns at the same time. 1. Position the cursor over the
• Simply select the columns you want row line so the cursor
to AutoFit.
becomes a double arrow
• Then select the AutoFit Column
Width command from the Format 2. Click and drag the mouse to
drop-down menu on the Home tab. increase or decrease the row
height.
3. Release the mouse. The height
of the selected row will be
changed.
7
Columns and Rows
How to modify all rows and columns simultaneously?
1. Locate and click the “Select All” button just below the name box to
select every cell in the worksheet.
2. Position the mouse over a row / column line so the cursor becomes a
double arrow.
3. Click and drag the mouse to increase or decrease the row / column
size.
4. The row / column size will be changed for the entire worksheet.

8
9
Columns and Rows
How to insert rows?
1. Select the row heading below
where you want the new row to
appear.
2. Click the Insert command on the
Home tab
3. The new row will appear above
the selected row

10
Columns and Rows
How to insert columns?
1. Select the column heading to the right of where you want the new
column to appear.
2. Click the Insert command on the Home tab
3. The new column will appear to the left of the selected column

11
Columns and Rows
How to delete a row or column? How to move a column?
1. Select the desired column heading for
1. Select the row or column you the column you want to move.
want to delete. 2. Click the Cut command on the Home
tab, or press Ctrl + X on your keyboard
2. Click the “Delete” command on 3. Select the column heading to the right
the Home tab. of where you want to move the column
4. Click the Insert command on the Home
3. The selected row or column will tab, then select Insert Cut Cells from
the drop-down menu
be deleted, and those around it 5. The column will be moved to the
will shift. selected location, and the columns
around it will shift

12
MANIPULATE DATA IN A
SPREADSHEET
Numeric Data Formatting
• Whenever you’re working with a spreadsheet, it’s good idea to use
appropriate number formats for your data

• Number formats tell your spreadsheet exactly what type of data you’re
using, like percentages (%), currency ($), accounting, times, dates, and
so on.

14
15
Numeric Data Formatting
• Number formats don’t just make your spreadsheet easier to read, they
also make it easier to use.
• When you apply a number format, you’re telling your spreadsheet exactly what
types of values are stored in a cell.
• For example, the date format tells the spreadsheet that you’re entering specific
calendar dates.
• This allows the spreadsheet to better understand your data, which can help ensure that
your data remains consistent and that formulas are calculated correctly.
• If you don’t need to use a specific format, the spreadsheet will usually
apply the “General” number format by default.

16
Numeric Data Formatting
How to apply number formats?
1. Go to the Home tab, click the
Number Format drop-down
menu in the Number group.
2. Select the desired format. You
can also click one of the quick
number-formatting commands
below the drop-down menu

17
SORTING DATA
Sorting Data
• As you add more content to a worksheet, organizing this information
becomes especially important.

• You can quickly reorganize a worksheet by sorting your data.

19
Sorting Data
Types of sorting
• When sorting data, it’s important to first decide if you want the sort to
apply to the entire worksheet or just a cell range.
• Sort sheet organizes all of the data in your worksheet by one column. Related
information across each row is kept together when the sort is applied.
• Sort range sorts the data in a range of cells, which can be helpful when working
with a sheet that contains several tables.
• Sorting a range will not affect other content on the worksheet

20
Sorting Data
How to sort a sheet?
1. Select a cell in the column you
want to sort by
2. Select the Data tab on the
Ribbon, then select A-Z
command to sort from A to Z
(Ascending), or the Z-A
command to sort Z to A
(Descending)

21
Sorting Data
How to sort a range? 4. Decide the sorting order
(either ascending or
1. Select the cell range you want to
descending)
sort
5. Once you’re satisfied with
2. Select the Data tab on the
your selection, click OK.
ribbon, then click on the sort
command 6. The cell range will be sorted by
the selected column
3. The Sort dialog box will appear.
Choose the column you want to
sort by

22
23
Sorting Data
Sorting Levels
• If you need more control over how your data is sorted, you can add
multiple levels (primary field and secondary field) to any sort.

• This allows you to sort your data by more than one column.

24
Sorting Data
How to add a level? 5. Select the next column you
1. Select a cell in the column you want to sort by, then click OK
want to sort by 6. The worksheet will be sorted
2. Click the Data tab, then select according to the selected
the Sort command order.
3. The Sort dialog box will appear. NB: If you need to change the
Select the first column you want order of a multi-level sort, simply
to sort by select the desired column, then
4. Click Add Level to add another click the Move Up or Move Down
column to sort by arrow to adjust its priority.
25
26
FILTERING DATA
Filtering Data
• If your worksheet contains a lot of content, it can be difficult to find
information quickly.

• Filters can be used to narrow down the data in your worksheet,

28
29
Filtering Data
How to filter data? 4. Click the drop-down for the
1. In order for filtering to work column you want to filter.
properly, your worksheet should 5. The filter menu will appear
include a header row, which is 6. Uncheck the box next to “Select
used to identify the name of All” to quickly deselect all data
each column
7. Check the boxes next to the data
2. Select the Data tab, then click you want to filter, then click OK.
the Filter command
3. A drop-down arrow will appear 8. The data will be filtered,
in the header cell for each temporarily hiding any content
column that doesn’t match the criteria

30
31
Filtering Data
How to apply multiple filters?
• Filters are cumulative, which means you can apply multiple filters to
help narrow down your results.

1. Click the drop-down arrow for the column you want to filter.
2. The Filter menu will appear
3. Check or uncheck the boxes depending on the data you want to filter,
then click OK
4. The new filter will be applied

32
Filtering Data
How to clear a filter? NB: To remove all filters from
• After applying a filter, you may want to your worksheet, click the Filter
remove or clear it from your worksheet
so you’ll be able to filter content in command on the Data tab
different ways.
1. Click the drop-down arrow for the
filter you want to clear
2. The filter menu will appear
3. Choose “Clear Filter From [Column
Name]
4. The filter will be cleared from the
column. The previously hidden data
will be displayed.

33
34
Filtering Data
Advanced Filtering
• If you need a filter for something specific, basic filtering may not give
you enough options.

• Fortunately, Excel includes many advanced filtering tools, including

35