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
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.
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 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.


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

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.


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.


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.

The user is blocked from entering any data in the cell except for the data required.
The user is warned that the data being entered is incorrect, and is asked if he or she wishes to proceed with the entry.
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
* 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
Print CTRL+P
Replace CTRL+H
Go to F5
Action Keystroke
Document actions
Open a file CTRL+O
New file CTRL+N
Save As F12
Print CTRL+P
Replace CTRL+H
Go to F5
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
Italics CTRL+I
Underline CTRL+U
Strikethrough CTRL+5
Edit active cell F2
Format as currency with 2 decimal places SHIFT+CTRL+$
Format as percent with no decimal places SHIFT+CTRL+%
Paste CTRL+V
Format cells dialog box CTRL+1


No comments:

Post a Comment