Excel Mid.docx

13 Pages

Computer Science
Course Code

This preview shows pages 1,2,3. Sign up to view the full 13 pages of the document.
Excel Mid-Term Notes Chapter 1 Excel Terminology  Workbooks : Excel files o Workbooks are made up of sheets  Worksheets  Chart Sheets o The sheet open when many are open is your active sheet o Rows and Columns intersect at Cells, which may be identified by their cell reference Creating Effective Workbooks  In practice, workbooks can be: o Used and updated by many o Very complicated  Multiple worksheets and chart sheets, extensive data  Formulas may be used to populate cells  One work sheet can be the source data for other worksheets or workbooks  Contain Macros (some programing code) to automate activities Good Practice  Plan out your workbook o What problems do you want to solve? o What data is needed? o What calculations are required? o What form should the solution nice? Navigating in Excel  There are generally multiple ways to do things in excel (mouse, keyboard shortcuts, dialogue boxes, etc.) o E.g click tabs Ctrl+ PgDn o Go to vs name box vs ctrl + G5 vs F5 Entering Text, Numbers, & Dates  There are three data types o Text: a combination of letters, numbers, and sane symbols that form words and sentences o Number: any numerical value that can be used in a mathematical calculation o Date and Time: Commonly recognize formats for date and time values Working with Rows & Columns  You can Change column width and row height o Drag o Autofit o Properties o Multiple Selections  You can insert rows or columns anywhere in a worksheet o Button o Right Click  You can delete rows or columns anywhere in a worksheet o Button o Right Click o Multiple Selections Cells and Cell Ranges  Cell Range: is a group of cells; also referred to as ranges  Ranges also have references o Adjacent o Non-adjacent  Cell ranges can be moves, copied, etc o Drag and drop o Copy/cut and paste  Cell ranges can be inserted and deleted o Shifting will occur Working With Formulas  A formula is an expression that returns a value o Begin with = o Operators combine different values o Generally arithmetic operators o Usually use numbers stored within cells o Order of precedence applies  To enter a formula o Type in a cell o Select Cells o Verify Via F2  Formulas can be copied and paste o Beware of absolute vs. relative references o Can “Paste special” in a number of ways  You can view all of the formulas in your worksheet by pressing Ctrl+ Working With Functions  A function: is a named operation that returns a value, generally used to simplify formulas  To enter a function: o Type o Select Cells o Function Wizard  AutoSum can be used for Sum, Average, Count, Minimum, and Maximum  What makes a good Formula ? o Use functions when possible  cleaner o Don’t hide data values in formulas o Show intermediate results to make it easier to follow the logic Working With Worksheets  A variety of actions can be performed on worksheets o Insert o Delete o Rename o Move o Copy o Tab Colour Editing Your Work  To edit cell contents you enter editing mode: o F2 o Double-Click o Click the Formula Bar  Press Entre when done  Press Esc to exit without applying changed  Find and Replace feature available Viewing and Printing  Page Break preview is very handy as it helps in being able to view where the pages will break and whether it will look awkward or not Excel Tables  In Excel, data is typically structured in rows and columns o Each column represents a field  An attribute or characteristics of a person, place, or thing  Eg, First name, last name, city, province, phone number o Each row represents a “record”  A specific, unique entity or occurrence  Eg. A person o The first row in the table is the “header row”  Contains Column Headings  An Excel Table is a Structured Range of data that is managed independently from the rest of the data in a worksheet  Why use them over structured ranges of data ? o Can format tables quickly o The table range is expanded automatically o Can add a total row to calculate summary o Formulas that are entered in one table cell are automatically copied o Can created formulas that reference cells in the table using tables and columns  Totals and Subtotals o Totals available through the “Total Row” box o Subtotals can only be applied to ranges  Sort the data by the column you want to subtotal  “Convert to Range”  Data tab, Outline group, Subtotal button  “At each change in” (sorted column)  Outline levels allow you to view the data in various levels of detail Chapter 5 Data definition table  Is a documentation list that lists the fields to be maintained for each record and a description of what each field will include Freezing Rows and Columns  Freezing is done to maintain an unchanged look of one row or column so you may scroll through a document as far as you’d like with seeing these frozen row or columns Sorting Data  Ascending order – arranges text alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest  Descending order – arranges text in reverse alphabetically from Z – A, numbers from largest to smallest, and dates from newest to oldest. o Sorting using one button A-Z or Z-A is usually common for single sort fields  Sorting multiple columns – to sort upon many column fields o Primary Sort Field –  Custom List – the sequence in which the user wants data ordered Filtering Data  When a table is created arrows are shown at the top of every heading (Auto Filter Menu) these drop down arrows can help in filtering several different items or objects  It is possible to filter using multiple columns  Clearing Filters – removes all filters  Criteria Filters – enables you to specify various conditions in addition to those that are based on an “equals” criterion (you may use numerical limitations) Total Row  Calculates summary statistics o Sum o Average o Count o Maximum o Minimum  These functions will show up upon clicking an arrow that will be seen at the total row Splitting Worksheets into Panes  Splitting can occur between 2- 4 panes o These panes help in navigating and checking material from area to another area Inserting Subtotals  Subtotals can only be inserted into a range not a table o Includes many summary information (count, average, sum , etc.)  Help summarize information categorically  To insert subtotals information must be arranged based on alphabetical order (A-Z or Z-A) o This is so each categorized field has a subtotal Subtotal Outline view  This view enables us to view data in detailed to least detailed summary Pivot Tables  used to organize data into a meaningful summary  A pivot table summarizes data into categories using functions such as COUNT, SUM, AVERAGE, MAX, and MIN  Fields that contain summary data are known as values fields  Category Fields – are the fields that group the values in a PivotTable(bottom left corner of the
More Less
Unlock Document

Only pages 1,2,3 are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

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.