Microsoft Excel Basics Pivot Tables e Xcelerate Pivot
Microsoft Excel Basics: Pivot Tables (e. Xcelerate)
Pivot Tables DB Admin creates new database & tables Microsoft Excel, Access, Text File What is a Pivot Table? It is a table that give you an organized summary of a large SQL set of data, which makes it easier to analyze data Software How to Access: Reporting Tool has data imported DB Admin updates existing database & tables Select dataset> Insert Tab> Pivot Table> Select New Worksheet> OK > Analyze Tab Data flows through to designated database & tables No. SQL Key Fact #1: All columns in data set must have headings Key Fact #2: There should be no empty columns or rows in the data set Key Fact #3: All columns and rows in dataset CAN’T BE BLANK Key Fact #4: Column headers can’t be merged cells (e. Xcelerate) Analysts query desired data from tables within desired database 2
Pivot Tables- The Quadrant The Pivot Table quadrant allows you to maneuver data according to your layout preferences, making it easier to analyze based on these preferences. SQL Headings of data columns (called fields) will appear here Reporting Tool 1. Filters- Drop down list of attributes. Place attributes here 2. Rows- Breaks out attributes vertically. Place attributes here (e. Xcelerate) Software has data imported Data flows through to designated database & tables DB Admin updates existing database & tables 3. Columns- Breaks out attributes horizontally. Place attributes here 4. Values - Place metrics here Analysts query desired data from tables within desired database 3
Ribbon Functions DB Admin creates new database & tables Microsoft Excel, Access, Text File Data Source: Allows you to see the data connected to the Pivot Table. Go here SQL when you want to update your columns/rows/cells in your data set. Software has data that. Reporting is connectedimported to your Refresh: Any updates you make to the data Tool can be updated to the Pivot Table with the Refresh function. When updating your dataset: 1. ) Update via Data Source 2. ) Select Refresh DB Admin updates existing database & tables Pivot Table Data flows through to designated database & tables No. SQL Analysts query desired data from tables within desired database (e. Xcelerate) 4
Slicers DB Admin creates new database & tables Microsoft Excel, Access, You can choose multiple Text File Slicers allow you to easily and quickly filter pivot data. Think of is as a filter for SQL your entire pivot table. Software selections by holding the Ctrl key has data or the Multi-Select Icon Clear Filter imported Reporting Tool How to Access: Analyze Tab> Insert Tab DB Admin updates existing database & tables Data flows through to designated database & tables Choose field(s) that is appropriate for analysis No. SQL Analysts query. Delete slicer by left clicking the desired data from tables within slicer and hit the Delete key desired database (e. Xcelerate) 5
Value Quadrant: Mathematical Operations DB Admin creates new database & tables Microsoft Excel, Access, Text File You can change the mathematical operation of the field in the Values quadrant (i. e. SQL if you don’t want a sum, but an average instead) by doing the. Software following: has data imported Reporting Field (columns) for the Go to the drop down arrow of the Pivot. Table Tool item in the Value quadrant> Value Field Settings> DB Admin updates existing database & tables respective Data flows through to designated database & tables No. SQL Analysts query desired data from tables within desired database (e. Xcelerate) 6
Value Quadrant: Multiple Columns DB Admin Microsoft new If you want to look at the same field in different. Excel, aggregate functions, (i. e. creates if you database & Access, SQL want to view the Total Order $ field as a sum and average aggregate function, tables Text File Software you can move the Total Order $ field to the Values quadranthas twice. data You can’t DB Admin updates imported do that in any other quadrant. Reporting existing Tool database & tables Data flows through to designated database & tables No. SQL Analysts query desired data from tables within desired database (e. Xcelerate) 7
Layouts DB Admin Microsoft creates new If you want to change the layout of your Pivot Table, the Design tab will be your Excel, database & Access, SQL best friend! tables Text File Software Reporting Tool 3 Layout Types: Compact Tabular Outline has data imported DB Admin updates existing database & tables Data flows through to designated database & tables No. SQL Analysts query desired datato from Play around with the Subtotals and Grand Total tabs if you want have tables within Subtotals and Grand Totals to appear or disappear from your Pivot Table. desired database (e. Xcelerate) 8
Calculated Fields Microsoft Excel, Access, Text File Go to Analyze tab > Field, Items & Sets> Calculated Fields Reporting Tool SQL Software has data imported DB Admin creates new database & tables DB Admin updates existing database & tables The dialog box below should appear. Here is where you will create your new Data flows formula column. In the Name Box, new column. through to designated input a name database & tables for your No. SQL In the Formula Box, input your formula. Double left click on your column name in Analysts query the fields list in order to data input desired fromthat tables within column into the formula bar. Once done, desired database select OK. (e. Xcelerate) 9
- Slides: 9