Exam Help CIS 150
Popular in Business Analytics (Excel)
Popular in Computer Information Systems
verified elite notetaker
This 10 page Class Notes was uploaded by an elite notetaker on Thursday August 27, 2015. The Class Notes belongs to CIS 150 at University of Miami taught by Mary McKenry in Summer 2015. Since its upload, it has received 84 views. For similar materials see Business Analytics (Excel) in Computer Information Systems at University of Miami.
Reviews for Exam Help
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 08/27/15
CIS 150 Tutorial 5 Excel Tables Pivot Tables and Pivot Charts Croonenberghs 102713 1 Visua l0verview Filtering Table Data Every Excel table has a Table Name which you can change to a descriptive name Each column represents a Field which describes some characteristics of a person place or thing such as last name address city or state Header Row the rst row of the range contains eld names it is often row 1 Each row represents a Record a collection of related elds that are grouped together You can Rearrange or Sort the records in a table or range based on one or more elds You can create a custom list to sort data in the order you specify by any ranking or AZ A Field Name or Column Header is a unique label that describes the contents of the data in a column The Filter Arrow changes to a sort icon as a reminder that the data is sorted by that eld Table Styles provide a way to quickly format an entire table using a preset style de nition You can click a Filter Arrow to sort the table by data in that column An Excel Table is a range of related data that is managed independently from data in other rows and columns in the worksheet The elds that you use to order data are called Sort Fields 2 Planning a Structured Range of Data Data De nition Table a documentation that lists the elds to be maintained for each record and description of the information each eld will include gt Freezing Rows and Columns Freezing a row or column lets you keep headings visible as you work with the data in a large worksheet As you scroll the data the row or column frozen will stay on the screen Freeze Click liewl Freeze Panes l Freeze Top Row CtrH0me to return to A2 the cell directly below the frozen row UnFreeze Click liewl Freeze Panes l Unfreeze Panes 3 Creating an Excel Table 0 Excel Table makes it easier to identify mange and analyze the groups of related data You can format it quickly add new rows and columns and add a total row to calculate things such as SUM AVERAGE COUNT MIN and MAX one formula in a cell can be copied to another ce Create an Excel Table Select Data Click lnsert Table l 0K Style the Table gt Renaming an Excel Table Renaming to assign a more descriptive name to a table especially when creating several tables no spaces allowed Renaming Click Design Tab Table Name Change Name Press Enter gt Formatting an Excel Table Formatting you can chose design in Table Style Options to quickly and easily add or remove table elements or change the table s formatting Format Click Tabe Toos Design More Choose what style is best 4 Maintaining Data in an Excel Table gt Adding Records 0 Adding you add a record to an Excel table in a blank row Enter in the rst blank row below the last record Adding Click Last Row Endl Type new data Press Tab or Enter gt Finding and Editing Records 0 Find amp Edit It is easier to use the Find Command to nd what needs to be edited Find amp Edit Click CtrH0me Editing Group I Find amp Selectlj Findl type what needs to be found Find Nextl Close lCIick the Cell I edit the cell gt Deleting Records 0 Deleting if you need to delete an entry in the table Deleting Click Editing Group Find amp Selectlj Find type what needs to be found Find Nextl Close Click Cells Group Delete Button Arrow Delete Tabe Rows 5 Sortin9Data o Sorting Data arrange data with a certain criteria so that it is easier to read and manage 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 reserve alphabetically from Z to A numbers from largest to smallest and dates from newest to oldest gt Sorting One Column Using the Sort Buttons You can easily sort data with one eld using the Sort A to Z Button or the Sort Z to A button One Column Click any cell in the column you want to sort Data I Sort amp Filterl SortA to Z gt Sorting Multiple Columns Using the Sort Dialog Box When sorting columns sometimes it is necessary to sort with two different elds Primary Sort Field the rst sort eld Secondary Sort Field the second sort eld Multiple Columns Click any cell Sort amp FiterGroup Sort Button lAdd Level I Sort Byarrow l Column Heading Sort 0n arrow l Orderarrow Do it for as many columns you need OK gt Sorting Using a Custom List Custom List indicates the sequence in which you want data ordered Custom List Click Sort amp Filterl Sort button l Orderarrow Custom List lList entries box type each entry Enterl Addl 0K 5 Visual Overview Filtering Data Total Row is used to calculate summary statistics including sum average count maximum and minimum for any column in an Excel Table Check box to display or hide the Total Row Button to convert an Excel Table to a range The status bar indicated that the table is ltered The lter arrow opens the Filter menu which includes options to sort and lter the table based on data in that column The selection list displays the unique items in the selected column You can select on item or multiple items from the list to lter the table by The split bar indicated the division between the panes You can drag the spilt bar to resize the panes Filtering is a process of displaying a subset of rows in excel table that meets the criteria you specify Each panes has its own set of scroll bars for shifting different parts of the worksheet into view in each pane To view different areas of a large worksheet you can split the window into two or four resizable areas Panes 7 Filtering Data 0 Filtering Data temporarily hides any records that do not meet the speci ed criteria After data is ltered you can sort copy format chart and print it gt Filtering Using One Column 0 You can lter with the AutoFiIter menu for that column You can use all the options to create three types of lters One Colum Click Category Fiterarrow lselect what items you need to specify OK gt Filtering Using Multiple Columns 0 When you need to lter more than one column you can do so and it will add to the already ltered data Multiple Columns Click Condition Fiterarrow l select what you need to lter OK gt Clearing Filters 0 Clear or remove the lters is when you want to redisplay all of the data in a ltered table Clearing Filters Click the column s FilterArrowl Clear Filter From quotselected columnquot gt Selecting Multiple Filter Items 0 Selecting Multiple Filters this is because sometimes you need to specify a more complex set of criteria to fond the records you want 0 You check the boxes of the items you want in the order you want to lter them Multiple Filter Items Click the columns Fiterarrow Check the boxes you want to lter OK gt Creating Criteria Filters to Specify More Complex Criteria 0 Criteria Filters enable you to specify various conditions in addition to those that are based on an equals criterion Create a Filter Click the elds Fiterarrow Select the Filter needed click the Criteria wanted eg Greater Than l AutoFiIter Dialog Box Enter Value Clear Filter Data Tab Sort amp Filter Group Clear Filter Criteria Records Displayed Equals Exactly match the speci ed text string Does Not Equal Does not exactly match the speci ed text string A Begins With Begins with the speci c text string Ends With End with the speci c text string Contains Have the speci ed text string anywhere Does Not Contain Do not have the speci ed text string anywhere Iquot w Equals Exactly match the speci ed number Greater Than or Equal To Are greater than or equal to the speci ed number Less Than Are less than the speci ed number Between Are greater than or equal to and less than or equal to the speci ed numbers Top 10 Are the top or bottom 10 Above Average Are greater than the average Today Have the current date Last Week Are in the prior week Next Month Are in the month following the current month Last Quarter Are in the previous quarter of the year Year To Date 2 C 3 W U gt Are since January 1 of the current year to the current date Last Year Are in the previous year current date 8 Usin the Total Row to Calculate Summar Statistics Total Row You can calculate summary statistics on all columns in an Excel Table or on a ltered table which you display at the end of the table Add Total Row Click Tabe Toos Design Total Rowlj check box in Table Stye Options by clicking the arrow in Total Row you can chose which Function you want to use gt Splitting the Worksheet Window into Panes Panes you can split the worksheet window unto two or four separate panes This allows to easily view data from several areas at the same time Each panes has its own scroll bar so you can navigate easily To create 2 panes you select a cell in row 1 or column A To create 4 panes you select any other cell Insert Panes Click CtrH0me View Tab l Split button Remove Panes Click View Tab l Split button l you can also doubleclick the split bar to remove the panes 9 Inserting Subtotals Subtotal offers many kinds of summary information including counts sums averages minimums and maximums Summarizes data in a range 0 Subtotal Command inserts a subtotal row into the range for each group of data and add a grand total row below the last row of data Does not work for Excel tables only ranges If it is in a Excel Table go to Table Toos Design then click Convert to Range button and then click the Yes button to convert it Subtotal Click the column you want l Outline Group Data tab Subtotal button I At each change in arrow l the column that contains the group you want Use Function arrow l the function you want l Addl check the boxes of the values you want to include To calculate another subtotal click the Replace current subtotals check boxes to remove the checks mark then repeat the subtotal steps then OK Remove Subtotal Click Data Tab Subtotal button Remove All button gt Using the Subtotal Outline View Subtotal Outline outlines your worksheet so you can control the level of detail that is displayed The three Outline buttons are at the top of the outline area on the left of the worksheet Level 3 gives you the most detail the individuals the subtotals and the grand total Level 2 only displays the subtotal and the grand total Level 1 only displays the grand total Visual Overview Pivot Table and Pivot Chart Pivot Table an interactive table used to group and summarize either a range of data or an excel table into a concise tabular format for easier reporting and analysis Uses the data from the column selected as row labels You can add many levels of subtotals and totals to a Pivot Table You can create a Pivot Table using the Field List The upper section displays names of each eld in the excel table and the lower section displays four areas in which you place elds to de ne the Pivot Table You can click one or more slicer buttons to lter the Pivot Table Value Fields the elds that contain summary data in a Pivot Table Pivot Chart a graphical representation of the data in a Pivot Table The button with the funnel image is to lter the Pivot Chart Category Fields the elds that group the values in a Pivot Table Category Fields appear in Pivot Tables as row labels column labels and report lters Fields in the Column Labels area are displayed as columns at the top of the PivotTable Fields in the Values area are summarized in the Pivot Table Analyzing Data with Pivot Tables Pivot Tables summarizes data into categories using functions such as COUNT SUM AVERAGE MAX and MIN 0 To create a Pivot Table report you need to specify which elds in the data source to summarize In PivotTable terminology the elds that contain summary data are known as value elds 0 Category elds are the elds that group the values in the Pivot Table the columns they appear as row labels column labels and report lters in the Pivot Table which allows you to focus on a subset of the Pivot Table by displaying one several or all items 0 You can easily rearrange hide and display different category elds to provide alternative views of the data Creating Pivot Tables 0 To create a Pivot Table you will need to use to Pivot Table Dialog Box to select the data to analyze and the location of the Pivot Table report 0 Usually created with the information from a worksheet but can also be done with data from an external database le Pivot Table Select the table or range Insert tab Click Pivot Table button Select a table or range option button New Worksheet or Existing Worksheetl Okljl select to boxes that you want to include in your Pivot Table l drag the elds to different boxes in the layout section Layout Area Description Row Labels The elds you want to display as the rows in the Pivot Table One row is displayed for each unique item in this area You can have nested row elds Column Labels The elds you want to display as columns at the top of the Pivot Table One column is displayed for each unique item in this area You can have nested column elds Report Filter A eld used to lter the report by selecting one or more items enabling you to display a subset of data in a Pivot Table report Values The elds you want to summarize gt Adding Fields to a Pivot Table 0 To add elds to a Pivot Table you select the boxes you want to summarize in the order you want them summarized o By default the Pivot Table report uses the SUM function for numbers in the Values area and the COUNT function for text and other nonnumeric values If you want a different summary function click appropriate button in the Values box The Value Fields Settings dialog box allows you to select the type of calculation you want from the list available and then click OK gt Applying Pivot Table Styles 0 You can apply preset styles for Pivot Tables which you can choose from a gallery of styles It does not affect the numeric formatting Pivot Table Styles Click Pivot Table Toos Design More select a style you like gt Formatting Pivot Table Values Fields 0 You can change the numeric formatting by clicking the value you want to change and selecting the style you want Value Fields Formatting Click the eld you want to change Pivot tabe Toos Options Field Settings lNumber Formatl select the format eg Currency OK gt Rearranging a Pivot Table 0 The Pivot Table Field List enables you to change or pivot the view of the data in the Pivot Table by dragging the eld buttons to different areas in the layout sections Rearrange Layout Section nd the eld you want to move l drag the eld into the Labeyou want it in gt Changing the Pivot Table Report Layout Options 0 You can chose two other layouts than the default one for the Pivot Table reports In the Outline report layout each eld in the row area takes a column in the Pivot Table 0 The Tabular report layout displays ones column for each eld and leaves space for column headers Layout Options Click Pivot Table Toos Design Layout Group gt Adding a Report Filter to a Pivot Table 0 Report Filter used to lter a Pivot Table to display summarized data for one or more eld items or all eld items in the Report Filter Area 0 Report Filters are added to see if displaying the information in this way adds value to the report x Add Report Filter Select the eld you want to move lDrag it to Report Fields Click the report lterarrow to select different values you want to edit 0 To lter more than one eld at the time click the Select Multiple Items check box in the AutoFiter menu to add a check mark next to each item and then uncheck items you don t want to include gt Filtering Pivot Table Fields 0 Filtering a eld lets you focus on a subset of items in that eld 0 You can lter items in the Pivot Table by clicking the eld arrow button in the Pivot Table that represents the data you want to hide and then uncheck the check box for item you want to hide Filtering Fields Click Column Labels Fiterarrow Check boxes you want to hide or unhide OK gt Collapsing and Expanding Items 0 Collapsing amp Expanding to view elds at different levels of detail the buttons identify where more details exist 0 Expand Button sign indicates you can show more details for that item Collapse Button sign indicated you can hide details for that item Collapse Click Collapse button it will show what is collapsed or expanded so you can adjust Expand Click the Pivot Table Tool Options Active Field Group Expand Entire Field button gt Sorting Pivot Table Fields Sorting you can sort eld either by its own eg Alphabetizing a eld or by the values in the body of the Pivot Table 0 To sort a Pivot Table eld you can use any of the Sort buttons on the Options Tab to sort the information in a report m Click the cell you want to sort Sort amp Filter Group Sortlj select the option you want eg Largest to Smallest gt Adding a Second Values Field to a Pivot Table 0 Adding the Title eld to the Values box would count the number of objects in the category because the title is a nonnumeric eld Adding Second Value Drag Field from the eld area to the values box Rename gt Removing a Field Field Headers and Button from a Pivot Table 0 Removing a eld from the Pivot Table has no effect on the underlying Excel table Removing Show Group Click Field List check box of the eld you want to remove Hide ExpandlCollapse Show Group Buttons they will disappear gt Using a Slicer to Filter a Pivot Table Slicer allows to quickly and easily lter a Pivot Table or Chart Every slicer consists of an object that contains a button for each unique value in that eld Slicer Sort amp Filter Group Click Insert Slicer button I select the eld you want a slicer for l grad to place the slicer l choose a style for the slicer 13 Refreshing a Pivot Table 0 Refresh a Pivot Table you cannot directly change data in a Pivot Table so you need to edit the table of data and then refresh or update the Pivot Table to re ect the updated data Refresh Edit the Original Table Click any cell in Pivot Table l Pivot Table Tool Options Data Group Refresh button 14 Grouping Pivot Table Items Grouping Items combines dates into larger groups such as months quarters or year or numeric items into equal ranges so that the Pivot Table can include the desired level of summarization Count Number of Items in a Group Click any cell in the group l Pivot Table Toos Options Calculation Button arrow l Summarize Value Byl Count gt Grouping Data Fields 0 Group Field Command allows you to a range of dates into periods Group Click any date in the Row Labels Column Pivot Table Toos Options Group Fieldl Deselect and then Select the one needed OK 15 Creating Pivot Chart 0 Pivot Chart allow you to interactively add remove lter and refresh data elds in the Pivot Chart similarly to the Pivot Table They have the same formatting Pivot Chart Click any cell in Pivot Table l Pivot Table Toos Option Group Pivot Chart Clustered Column Format Pivot Chart l39l Pivot Chart Layoutl Legend button arrow I None Name Chart Title lDrag Pivot Chart to position The Pivot Chart Tools contextual tabs enable you to manipulate and format the selected Pivot Chart the same way as an ordinary chart
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'