3 Pages
Unlock Document

BUS 150
Maryann Barber

SIMPLE TABLES (STRUCTURED DATA) Data: Raw facts. Information: Data made useful by rearranging/processing (Filtering, Sequencing, Calculating) E.g: Filtering & Calculating: Average Grade for sections, Count of males & females, Average GPA for majors Sequencing & Filtering: Alphabetical list by majors/ just Finance major Field: (Column) Characteristic or attribute of the entity. Record: (Row) Stores data on a specific occurrence of the entity; made up of fields. Table (entity): stores data on a single entity type; made up of of records Record layout: For every record is identical, same field in the same order Unique fields: cannot duplicate entries. Non-unique fields: Duplicates are permitted When you do searches, it is important to know if it is unique or non-unique Simple table (structured data) vs Excel table: Created from a simple table with the insert table command Excel’s rules for creating tables: • Field names (Header rows) should be descriptive, unique, no blank cells and must be formatted differently • Data records start in row immediately after field names, must be consistent, unique and on separate rows • No blank space at the beginning of the entry • Blank row above (except row 1) and below the last record; to the left (except column A) if the first field and right of the last field Excel’s rules for creating fields: • Use data that requires the least amount of maintenance (bdate instead of age) • Store the data in the smallest usable unit (last name & first name)  Greater flexibility • Store numeric data that you will not calculate with as text (ID, phone #)  Format before entering data Filtering data : W/in the same field  OR, AND can be done with numeric data (preset filters: between). Different Fields  AND (can’t use OR) Sequencing data Ascending: Oldest date, Smallest number, A-Z vs Descending order: Newest date, Largest number, Z-A Primary sort keys: Grouping by vs Secondary sort keys: w/in primary (if using A-Z/Z-A key  sequence Secondary first Subtotals: Sequence on the group first (sum, average, max, min, count). Can collapse/expand the outline. Cannot be inserted in an Excel Table. Don’t replace current total PIVOTTABLES AND PIVOT CHARTS Purpose: Make it easy to summarize and analyze large amounts of data. Easily rearrange the fields to look at the data from different perspectives. Can go on the same/separate worksheet Value fields: Text = Row labels. Numeric = Value area (Sum) (Can have multiple value fields). Text dragged in value area (Count). Report Filter fields: Allow filtering Ability: Adding/Removing fields; Rearranging fields; Sorting and filtering fields; Grouping items Refreshing PivotTable data: Change table data, doesn’t automatically update, has to refresh st Creating Pivot Charts: Build a pivot table and pivot chart. X-axis (category label) = 1 column Modifying PivotCharts: Can switch rows and columns. If modified, pivot table is also modified Slicers: Allow filtering by any field (w/in or outside a PivotTable). Can connect multiple PivotTable with 1 slicer LOGICAL FUNCTIONS IF Function (condition, true, false). Condition can be compound VLOOKUP Function - (What to look up (Cell), Lookup table, column #, True/blank (if approximate) or False (if exact). E.g: VLOOKUP (G4, $E$11:$H$15, 2, false). Have to be in ascending order if approximate match Conditional formatting Finding duplicates through Highlight Cells Rules: To look for error in uni
More Less

Related notes for BUS 150

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.