1 / 48

Day 10 : Excel chapter 5

I ask not for a lighter burden, but for broader shoulders . ~ Jewish Proverb. Day 10 : Excel chapter 5. CS 101 cody.cutright@mail.wvu.edu February 10 th , 2014. Large datasets. So far we have covered relatively small data sets

lakia
Download Presentation

Day 10 : Excel chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. I ask not for a lighter burden, but for broader shoulders. ~Jewish Proverb

  2. Day 10:Excel chapter 5 CS 101 cody.cutright@mail.wvu.edu February 10th, 2014

  3. Large datasets • So far we have covered relatively small data sets • What if we need to navigate large amounts of data?

  4. What are we looking at?

  5. How can we fix it? • In the previous example we could not see our column titles • This becomes very difficult with multiple columns of similar data • Is there a way to keep our headers?

  6. Yes - freeze it!

  7. Freeze options Table 5.1

  8. Freeze panes G3

  9. Freeze panes – cont’d Freeze Panes

  10. Now scroll down, and voila!

  11. Other options • Freeze Top Row: Freezes only the top row of the excel sheet. • Freeze Top Column: Freezes only the first column of the excel sheet.

  12. Data tables A table is an area in the worksheet that contains rows and columns of related data formatted to enable data management and analysis.

  13. Table Advantages • Freeze headings on screen during scrolling • Filter lists • Predefined table styles (i.e. table colors) • Create and edit calculated columns • Have a calculated total row with which you can perform a variety of functions • Data exports/imports

  14. Table Design • A field is an individual piece of data • A record is a complete set of data for an entity

  15. Sample table ID Field Record

  16. Creating a table from existing data • Click within the existing range of data • Insert Tab • Tables group -> Table • Fill out the Create Table dialog Box • Click OK

  17. Select the data Insert -> Table

  18. Create table dialog box

  19. Voila!

  20. Adding records (rows) • Click a cell below where you want the new record • Home Tab -> Cells Group -> Insert • Insert Table Rows Above

  21. Before the new record

  22. With the new record

  23. Delete a record • Select a cell in the record to be deleted • Home Tab -> Cells Group -> Delete • Delete Table Rows

  24. Add/delete fields (COLUMNS) Sometimes you may need to add a field,to insert a field: • Click a cell to the right of where the new field (column) will be • (If you want a new field between columns A & B, click a cell in column B) • Home Tab -> Cells Group -> Insert • Insert Table Columns to the Left

  25. Table styles

  26. Sorting data Table 5.3

  27. Sort menu

  28. sort by multiple criteria • Select any cell in the table • Data tab -> Sort & Filter Group -> Sort • Select the primary sort level by clicking the Sort by arrow, selecting the column, then the sort order • To add another level, click Add Level, select the second level by selecting Then By, select the order… repeat

  29. Sort dialog box

  30. Filtering data Filtering is the process of displaying only records that meet specific conditions.

  31. Things to filter by • Dates • Numbers • Specific Words (Text)

  32. To add a filter to a field • Click a cell in the field • Data Tab -> Filter • Specify Criteria

  33. Filter dialog box

  34. Clearing filters

  35. Structured references • Used to create results by performing calculations on columns • Of the form =[Row1]-[Row2] • Can still use semi-selection • Make sure you use the brackets

  36. Example structured reference

  37. Total Row A Total Row appears below the last row of records and enables you to display summary statistics. • Design Tab • Table Style Options -> Total Row • Select each cell, then the function

  38. Subtotal function Subtotal calculates an aggregate for values in a range or database

  39. Subtotal function form =SUBTOTAL(function_num,ref1,…) ***The benefit of subtotal is that it will only display records that have been filtered.

  40. Function_num Table 5.4

  41. Conditional formatting Helps you and your audience understand a dataset better by adding a visual element to the cells. (A failing grade, highlighted in red for instance)

  42. HighLIGHT Cells • Home Tab -> Styles group -> Conditional Formatting • Highlight Cells Rules

  43. Between dialog box

  44. Top/Bottom Rules • Right Below Highlight Cells • Allows you to format by either a percentage (i.e. top 10%), or by a number (i.e. the last 10) Example use: Finding the ten lowest grossing car lots, so you can close them.

  45. Custom Rules • If the default formatting options don’t fit your needs, you can create your own formatting rules. • Home -> Conditional Formatting -> New Rule

  46. New Rule Dialog Box

  47. Adding/remove/modify rules Home Tab -> Conditional Formatting -> Manage Rules

More Related