Ms Excel Spreadsheets allow a user to sort data by specifying various criteria. Sorting can be done on numbers, text or custom lists. Sorting rearranges the contents of a column.
When sorting a table with multiple columns, all the rows are rearranged based on the contents of a particular column.
Session 4A: Data Manipulation with Formatted Data
It is also possible to sort several columns of data by adding several levels of sorting criteria such as sorting a report first by student grades, then by course then by student name. Each sort level is represented by a single row in the Sort dialog.
Class Files: ChurchActivities ; OlympicStandings
Class Notes: Module 4- Introduction to spreadsheets -Session 4A
Session 4B: Formatting for Enhanced User Interface
Session 5A: Rules – Based Data Manipulation and Formatting
Spreadsheets provide a quick and effective way to create professional-looking worksheets that effectively display data.
Styles are used to change the look of tables, data, charts or shapes and are predefined in the spreadsheet application.
Using styles makes it faster to apply a style and multiple formatting attributes to a worksheet or to multiple worksheets as opposed to changing each element individually.
- Cell styles – used to ensure that all cells have consistent formatting: font size, number formats, cell borders and cell shading
- Table styles – select predefined alternate row styles on table rows
- Chart Styles – apply a predefined style to a chart
- Pivot table styles – select a predefined alternate-row style
- Class Notes: Module 4- Introduction to spreadsheets -Session 5A
Session 5B: Working with Variables and Data Types
Data on a spreadsheet can be displayed in different formats such as number, currency, percentage, fraction, date, Time, and fraction.
In the Home tab, number group, there are different menus available. The currency format, percentage and decimal points are the default formats for numbers.
Class Notes: Module 4- Introduction to spreadsheets -Session 5B
Session 6A: Report Generation
Data filters applied in a spreadsheet shift the data so that only data that meets set conditions is displayed. This makes it easier to focus on specific information. Filtering focuses on rows of data. If the row does not meet the condition, it is filtered out by temporarily hiding it from view. Data filtering can be applied to
- Numeric data – this can be filtered on the whether the data equals a certain number, the data is above or below the average value or whether the data is greater /less than a specific number.
- Text data – this can be filtered on whether the word begins/ends with a specific letter of the alphabet, contains one or more letters or matches a certain word
By filtering information in a worksheet, you can find values quickly. You can filter on one or more columns of data. With filtering, you can control not only what you want to see, but what you want to exclude. You can filter based on choices you make from a list, or you can create specific filters to focus on exactly the data that you want to see.
You can search for text and numbers when you filter by using the Search box in the filter interface.
When you filter data, entire rows are hidden if values in one or more columns don’t meet the filtering criteria.
You can filter on numeric or text values, or filter by color for cells that have color formatting applied to their background or text.
Class Notes: Module 4- Introduction to spreadsheets -Session 6A
Session 6B: Managing Headers/Footers and Page Setups
Page margins are the blank spaces between the worksheet data and the edges of the printed page.
Top and bottom page margins can be used for some items, such as headers, footers, and page numbers.
To better align a worksheet on a printed page, you can use predefined margins, specify custom margins, or center the worksheet horizontally or vertically on the page.
Note: Page margins that you define in a given worksheet are stored with that worksheet when you save the workbook. You cannot change the default page margins for new workbooks
Class Notes: Module 4- Introduction to spreadsheets -Session 6B