Microsoft Excel (part 3)

Pivot Table & Chart

What is Pivot Table?

A PivotTable report is an interactive table that quickly combines and compares large amountsof data. It allows go around its rows and columnsto see different summaries of the source data, and you can display the details for areas of interest.

We can use a Pivot Table report when we want to examine related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

Excel is a fantastic and powerful tool to analyze data and report findings, trends and different data relationships. However, sometimes you have to analyze large amounts of data and produce reports based on this data and present it to your boss and colleagues.

But, thankfully Excel has shortcuts within itself – these shortcuts are the Pivot Tables and Reports. You may be intimated by Pivot tables and reports because perhaps you think they are too hard, or you thought they were great when you learned about them in training class or when someone showed them to you, but you quickly forgot about them because you did not use them on a daily basis. Well, this tutorial will show you how to create a pivot report in no time.

Advantage of using Pivot Table

Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. Creating a Pivot Table Report To create a Pivot Table report we need to have a massive amount of data insert a new worksheet in your workbook and enter the below given data in the worksheet: After you’ve entered data in the worksheet Go to Data>Pivot Table and PivotChart Report and select this option the Pivot table dialog box will be displayed as shown below: Select Microsoft Excel list or database from Where is the data that you want to analyze? Section and PivotTable from What kind of report do you want to create? And click on Next to continue then select the specify the range where your data is and click on next then select the location where you want to store your PivotTabel report and click on Layout button Place the column headers in PAGE, ROW, COLUMN, DATA section by dragging them with mouse and click on OK after you have arranged all headers and then click on Finish to view the Pivot Table If we want to view only selective data in our pivot table report we can place some table headers in the PAGE section of pivot table layout. What is Chart? A chart is a graphical representation of a numeric that is more easy to understand, analyze and demonstrate in comparison to numeric data. Like if you used to watch Cricket Match you must have seen the graph that compare score of two teams or the chart that display the performance statistics of a bowler over-by-over when we such a graph we quickly understand that, taller a bar is expensive the over was etc. Definition A chart or graph is a type of information graphic or graphic organizer that represents tabular numeric data and/or functions. Charts are often used to make it easier to understand large quantities of data and the relationship between different parts of the data. Charts can usually be read more quickly than the raw data that they come from. They are used in a wide variety of fields, and can be created by hand (often on graph paper) or by computer using a charting application. Charts allows us to present data entered into the worksheet in a visual format using a variety of graph types. Before you can make a chart you must first enter data into a worksheet. Creating a Chart in MS-Excel To create a chart in MS Excel Follow the these steps: 1. Enter the data into the worksheet and highlight all the cells that will be included in the chart including headers. 2. Select the data and Click on Insert --> Chart to view the first Chart Wizard dialog box. 3. Chart Type - Choose the Chart type and the Chart subtype if necessary. Click Next. 4. Chart Source Data - Select the data range (if different from the area highlighted in step 1) and click Next. 5. Chart Options - Enter the name of the chart and titles for the X- and Y-axes. Other options for the axes, grid lines, legend, data labels, and data table can be changed by clicking on the tabs. Press Next to move to the next set of options. 6. Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu. 7. Click Finish to create the chart. Resizing the Chart To resize the chart, click on its border and drag any of the nine black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart. Moving the Chart Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it. Chart Formatting Toolbar Chart Objects List To select an object on the chart to format, click the object on the chart or select the object from the Chart Objects List and click the Format button. A window containing the properties of that object will then appear to make formatting changes. Chart Type Click the arrowhead on the chart type button to select a different type of chart. Legend Toggle Show or hide the chart legend by clicking this toggle button. Data Table view Display the data table instead of the chart by clicking the Data Table toggle button. Display Data by Column or Row Charts the data by columns or rows according to the data sheet. Angle Text Select the category or value axis and click the Angle Downward or Angle Upward button to angle the the selected by +/- 45 degrees. Chart Types Microsoft Excel offers a wide selection of chart types and sub types, to help you use your data to get your point across. There are over a dozen standard chart types, each with several subtypes. Excel also offers twenty built-in custom types, many of which are called "Combination Charts," because they combine different types in the same chart. Applying a Chart Type to a New Chart Select your data and click the Chart Wizard button on the Standard toolbar (or choose Chart... from the Insert menu. The first step of the Chart Wizard is the Chart Type dialog. The default chart type (a Clustered Column chart, if you've never specified another default type) is selected. Note the list of chart types in the left side of the dialog, the thumbnails of subtypes in the right, and below the subtypes, a description of the selected subtype. Click on the Press and hold to view sample button to see a thumbnail of your chart formatted as the selected chart subtype. Select a chart type and subtype, and click Next to continue with the rest of the wizard. Changing an Existing Chart's Chart Type You can easily change the chart type of an existing chart. Select the chart and select Chart Type... from the Edit menu, or right click on the chart and select Chart Type... from the pop up menu. The chart's type is selected (in this case, an XY Scatter chart with lines but no markers). Select a different type or subtype, and click OK. The Default Chart Type As mentioned above, the Clustered Column chart type is the default type applied to any chart created in Excel. To select a different chart type for the default, activate any chart, and select Chart Type... from the Edit menu, or right click on the chart and select Chart Type... from the pop up menu. Select the chart type you want to designate as the default type, and click on the Set as default chart button at the bottom of the dialog. MACRO What is Macro? In MS Excel Macro is a set of recorded keys strokes that can be used later be used later to perform the task. Macro is similar to a stored program. It is very useful for formatting etc. When we record a macro each and every action is recorded and when we can run that recording to perform the similar tasks again. For Example you are a shop owner and you prepare and prints your bills using MS Excel for that you have to format Header of your bill for this you can record a macro and use it as per your need. Recording a Macro To Record a new macro follow these steps Click on Tools--> Macro --> Record New Macro Type the Macro name Define a Shortcut Key and Click on OK to start recording. after clicking on OK button you will see the Stop Recording toolbar Perform the actions you want to record in macro and when you have finished with it click on stop recording button to stop the recording. By Default Macro usages Absolute use Relative/Absolute Reference button. Running a Macro As Mentioned Macro are similar to recorded programs that can be run later to perform the Recorded task. To run a recorded Macro do the following: Click on Tools -->Macro -->Macros Select the macro you want to run and click on Run button Result: Deleting a Macro To run a recorded Macro do the following: Click on Tools -->Macro -->Macros Select the macro you want to run and click on Run button it will prompt you select Yes if you are sure that you want to remove this Macro. Forms Creating and using Forms What is a data Form? Use of Excel’s built-in Data Form makes it easier to enter data in a list. A data form is a dialog box that gives us a convenient way to enter or display one complete row of information, or record, in a list at one time. MS Excel can create a built-in data form for our list. The data form displays all of our column labels in a single dialog box, with a blank space beside each label for us to fill in data for the column. We can enter new data, find rows based on cell contents, update existing data, and delete rows from the list. Use a data form when a simple form listing the columns is sufficient and you don't need more sophisticated or custom features. A data form can make data entry easier than typing across the columns when you have a wide list with more columns than will fit on the screen at one time. Creating a Data Form Select the Column headers to be included in the data Form and click on Data -->Form MS Excel will generate data form displaying all our Column header as Label and text box for typing data for that particular column as shown below: type the value for each column and click on New to enter new record. After completion click on Close to close the data form. Validation What is Data Validation? Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can: --provide users with a list of choices --restrict entries to a specific type or size --create custom settings Many times, we are building spreadsheets for other people to fill out. If those other people don’t watch Call for Help, they may not be as spreadsheet savvy as you. These people might find unusual ways to enter the wrong type of data – entering text where you expect numbers, or spelling regions the wrong way. Use Excel’s data validation features to control what can be entered in your spreadsheet. By default, every cell in Excel is set to allow any value. You can use the Data - Validation dialog box to control what values people can enter in a cell. How to Use Data Validation in Excel? Data validation gives you the ability to set “rules” pertaining to data entered on a worksheet. For example, you might want to insure that a column labeled DATE contains only dates or that another column contains only numbers. You could even insure that a column labeled DATE contains only dates during a certain period. With Data Validation, you can control what is entered on the worksheet. NOTE: While Data Validation is a useful and effective tool, please note that pasting data into a cell or selecting Edit, Clear All from the Excel menu will override the Data Validation rules that you set. While there are many ways you can use Data Validation, the following exercise will set up a simple rule requiring the user to input a date within a certain range. Creating a Data Validation Rule The worksheet below contains a list of employees and their salaries. Days of Works need to be entered. The date entered in column C must be less then 26. Select the cells to which you want to apply the Data Validation (in this case, cells C3:C5), then select Data, Validation from the Excel menu. Click the Settings tab and complete the dialog box as shown below, but do NOT click OK when you are done. Click the Error Alert tab. For the error alert, you have three options “Stop”, “Warning” and “Information”. The option you select determines what occurs when if incorrect data is entered into the cell. Stop: The user is blocked from entering any data in the cell except for the data required. Warning: The user is warned that the data being entered is incorrect, and is asked if he or she wishes to proceed with the entry. Information: The incorrect data is entered into the cell but the user is notified that it is incorrect. Complete the dialog box as indicated below, then click OK. Once you are returned to your worksheet, into one of the type value greater then 25 cells in column C(C3:C5)and see the error message display. Using Input Messages with Data Validation To make things clearer for people who might be entering data on a worksheet with data validation rules, you might want to include an Input Message. The Input Message will display whenever the user clicks in a cell requiring data validation. When setting the data validation criteria, click the Input Message tab of the dialog box. Type your message, then click OK. When the user clicks in the appropriate cell: or, if the Office Assistant is not being used: Importing External Data Data Source Importing data is most effective when we are working with large amounts of data that would be time-consuming to enter manually or too large to copy and paste. In addition, if we import our data, Excel can automatically update our reports and summaries whenever the original source database is updated. Our data is always current. Data sources we can access Microsoft Office provides drivers that we can use to retrieve data from the following data sources# : * Microsoft Office Access * dBASE * Microsoft FoxPro * Microsoft Office Excel * Oracle * Paradox * SQL Server * Text file databases * Third-party providers #data source: A stored set of "source" information used to connect to a database. Importing External Data To Import External data into excel follow these steps: Click on Data -->Import External Data --> Import Data It will display a dialog box , we can either use a data source we have created or Select the text file you want to use, and then click Open. It will display the Text Import Wizard click on Next to continue Select the Delimiters, Text qualifier etc and click on Next Select the Column data Format ie Text or Date and click on Finish Select the destination cell where you want to store your imported data and click on OK. We can also import data from MS Access or any other database using ODBC Data Source. Excel Keyboard Shortcuts Keyboard Shortcuts Keyboard shortcuts can save time and the effort of switching from the keyboard to the mouse to execute simple commands. Print this list of Excel keyboard shortcuts and keep it by your computer for a quick reference. Note: A plus sign indicates that the keys need to be pressed at the same time. Action Keystroke Document actions Open a file CTRL+O New file CTRL+N Save As F12 Save CTRL+S Print CTRL+P Find CTRL+F Replace CTRL+H Go to F5 Action Keystroke Document actions Open a file CTRL+O New file CTRL+N Save As F12 Save CTRL+S Print CTRL+P Find CTRL+F Replace CTRL+H Go to F5 Formulas Apply AutoSum ALT+= Current date CTRL+; Current time CTRL+: Spelling F7 Help F1 Macros ALT+F8 Selecting Cells All cells left of current cell SHIFT+left arrow All cells right of current cell SHIFT+right arrow Entire column CTRL+Spacebar Entire row SHIFT+Spacebar Entire worksheet CTRL+A Text Style Bold CTRL+B Italics CTRL+I Underline CTRL+U Strikethrough CTRL+5 Formatting Edit active cell F2 Format as currency with 2 decimal places SHIFT+CTRL+$ Format as percent with no decimal places SHIFT+CTRL+% Cut CTRL+X Copy CTRL+C Paste CTRL+V Undo CTRL+Z Redo CTRL+Y Format cells dialog box CTRL+1 ====> COURSE COMPLETE <====