02 08 Chip Plus Handson With Ms Excel PDF

February 2008 | Free with CHIP, not to be sold seperately
l Tutor a n o s r e P Your
Also available in Bangladesh, Nepal, Bhutan, Sri Lanka, UAE
Editorial........................................................................... 2 Make your data more readable ..................................... 3 Making drop lists for your surveys ............................... 7 Working with Advanced Filters ....................................12 Let Excel do the Solving! ..............................................15 Speed up your calculations with Goal Seek . ...............19 Budgeting with ease . ....................................................21 Consolidation with 3D formulas .................................. 25 Using a Pareto for Data Analysis ................................ 28 Getting Data From the Web in Excel ...........................31 Visually enhance your data .......................................... 34 Personalize your workbooks ....................................... 39 Use Excel to make better Presentations .................... 42 Compare your skills ......................................................47
BUSINESS HEAD & DGM Circulation
Vijay Adhikari
EDITOR
FeatureS EDITOR WRITERS
Kamakshi Venugopal Vijay George, Gareth Mankoo, Dolwin Fernandes
Photo Editor HEAD OFFICE
Shiresh R Karrale
Brian Pereira editor@chip-india.com Jamshed Avari jamshed.avari@chip-india.com

Assistant Editor - Technical
Jagdish Limbachiya, Pramod Jadhav, Brijesh Gajjar, Sachin Pandit, Hemali Limbachiya, Pravin Shinde, Ravi Parmar, Ravikumar Potdar
DESIGN
Intelligent Computing Chip, TBW Publishing & Media Pvt. Ltd. `A Wing, Ruby House, 2nd Floor, J.K. Sawant Marg, Dadar (W), Mumbai - 400 028. INDIA Phone: (91 22) 4030 2323 Fax: (91 22) 4030 2707 E-mail: comment@chip-india.com
HANDS ON WITH MS WORD EDITORIAL
Hands On with MS Excel

Not everyone considers mathematics and numerical calculations as a friend; but they need not be our blood-sworn enemies either. The fate of any successful endeavor depends on the right numbers. From the invention of the abacus to the creation of computers, there has been a great emphasis on automating the task of calculation. In todays times, numerical data is not for the deliberation of an accountant or a mathematician alone; executives and managers also have to consider statistical interpretation for forecasting and decision making. Spreadsheet applications, like Excel, present a two-fold benefit: apart from performing complicated calculations, they also help you to display data in a varied range of formats which include table structures and a wide range of chart types. With tools like excel, every day tasks like creating and managing household budgets or creating that marketing kit is no longer a menial task; it can be quite fun and exciting! Through Hands on with Excel, we have tried to introduce the numerous ways in which Excel can come to your rescue, both on professional turfs and on home grounds. With a variety of do-ityourself articles, you can make excel work to your advantage. Planning your loan repayments, coming up with budget variations, or even following a fixed monthly budget with due consideration to your personal expenses have never been this easy! At work, revamp the look of your workbook to suit your persona using Excel 2007. Make your data scrutiny simpler by following consolidation techniques and using the advance filter. Add more appeal to your survey forms through dropdown lists. Try colour coding your cells with conditional formatting. Explore the barely ventured world of charts; use them as effective marketing tools. You even can automate your data entry through the internet! The potential of Excel as an application has been barely tapped. This software has so much more to offer than just tables and calculations. We hope that with the help of the simplified workshops included in this issue, you can make numbers dance to your tunes! Team CHIP feedback@chip-india.com
february 2008 intelligent computing CHip PLUS
WORKSHOP HanDs On WITH MS excel
Make your data more readable

With some basic formatting, you can make your Excel worksheets more readable and easy to understand. Through this workshop, well present you with a list of options with which you can increase the comprehension of complicated data.
References
How to use the Formatting Toolbar?

The Formatting toolbar provides you quick access to commonly used formatting options. This allows you to apply formatting faster without having to open a dialog box. To view the Formatting toolbar, select View | Toolbars | Formatting. You can click on the different buttons available on the toolbar to apply formatting to the selected area/cells.
STEP 1: Formatting the font

You can change the look of the font to make the data more readable. Formatting the font may also indicate special implications; for example, the highest sales can be in red.
1 2 3 5
Start Microsoft Excel and open the document that contains the sales data. The quarterly data of sales in some states in the UK and Germany without any formatting will appear as follows: Select the cells that require formating. Click Format | Cells.
Note: You can also right-click on the selected cells and click Format Cells. From the Font style list box, select the required style. For example, under Font style list box, select Bold Italic. From the Size list box, select the required style, for example, select 16. From the Color drop-down list, select the color of the font.
Click on the Font tab .
6 7 8 9
What does data formatting mean?

Data Formatting in word does not mean restructuring or erasing or data, like general computing terminology. It also differs from editing, because data editing also involves change in structure of data. Format of data basically refers to the appearance of the text presented.
From the Font list box, select the required font style. For example, under Font list box, select Franklin Gothic Medium. Note: You can view only those fonts that are installed on your computer.
Click OK.
Note: You can also select the options mentioned from Steps 5 to 8 from the Formatting toolbar (See Tips).
10
After applying formatting to the font, the sales data will appear as follows:
Note: If you cannot view the data in a cell completely, you can change the width of the cell or wrap text (See Tips).
intelligent computing CHip PLUS february 2008
HANDS ON WITH MS excel WORKSHOP
STEP 2: Changing the display of numbers

Excel sheets are mainly used to maintain and calculate numbers. The manner in which numbers are displayed is very essential. For example, while maintaining the sales figures, it is essential to specify the currency of the sales digits entered. Without the currency, the numbers will have no meaning. Manually entering the currency may not allow you to perform calculations with them. As a result, it is important to specify the display of numbers.
Start Microsoft Excel and open the document that contains the sales data.
TIPs
Provide column and row headings

Headings are names provided for a sheet, column, or row. These are very essential while maintaining worksheets. Headings make the worksheet more readable by helping you understand what the data available relates to. There are three basic headings that can improve the datas readability. Worksheet headings: These provide information on what data is in the entire worksheet. The heading needs to be short and specific. For example, the heading Sales for the year 2006 is more specific and appropriate than just Sales. Column headings: These provide information on what data is in the column. These can contain further subheadings to make the data more readable. For example, the column heading Germany can have sub-headings Berlin and Hamburg. While reading the data under Berlin, you know that the data is for the sales made in 2006 for Berlin. Row headings: These provide information on what data is the row. For example, when you read 300000 under Berlin, you know that the data is for the sales made in Quarter 1 of 2006 for Berlin. Similarly, rows can also have sub-headings in order to make data more readable.
1 2 3 4 8
From the Category list box, select the format in which you want to display the text/ numbers. For example, click on Currency.
Select the cells that contain the sales figures.
Click Format | Cells.
From the Symbol drop-down list, select the required currency symbol.
6 7
Click on the Number tab.
Click OK
After changing the manner in which the numbers are displayed, the sales data will look as follows:
STEP 3: Merging cells

Merging cells converts multiple cells into one cell. You may have a heading that contain sub-headings in a worksheet (See Tips). For example, the column heading Germany has two sub-headings Berlin and Hamburg. This can be better displayed by merging the two cells above Berlin and Hamburg.
Select the cells that need to be merged.
3 4
Click on the Alignment tab.
Under Text control, insert a check mark in the Merge cells box.
Click Format | Cells.
Click OK. You can center align the data after merging cells (See Tips).
TIPs
Split merged cells

If you want to split merged cells in a worksheet, heres how to do it: Select the merged cell. Click Format | Cells. Click on the Alignment tab. Under Text control, click in the box for Merge cells to clear the check mark. Click OK. The merged cells will split back to the original appearance.
After merging the cells, the sales data will appear as follows:
1 2 3 4 5
STEP 4: Applying background color

Adding background color to cells helps to visually group cells on the basis of color. For example, one color can be applied to country names, while state names can have another background color.
Select the cell/s to which you want to apply a background color.
3 4
Click on the Patterns tab.
Wrap text
You may want to view all data available in a cell without changing the column width. You can wrap the text in the cell to fit exactly into the cell. Open Microsoft Excel. Select the cell whose text you want to wrap. Click Format | Cells. Click on the Alignment tab Under Text control, insert a check mark in the Wrap text box. Click OK.
Under the Cell shading section, click on the background color that you want to apply to the cells.
1 2 3 4 5 6
Click Format | Cells. Note: You can also select the background color from the Formatting toolbar (See Tips).
Click OK.
After applying the different background colors, the sales data will appear as follows:
STEP 5: Applying borders

Borders make it easier to identify the different parts of information entered in an Excel file. For example, you can apply a border around the data for the United Kingdom and a different border around that of Germany to distinguish between the data of the two countries. Borders can also be used to indicate special implications; for example, a double underline below the numbers could indicate that they are grand totals.
Select the cell/s to which you want to apply a border.
1 2 3 4
Under the Line section, from Style, select the line style that you want to use for the borders.
TIPs
Aligning data
Aligning the data makes it more readable and easy to understand. For example, by default, all dates and numbers are right aligned, while the normal characters are left aligned. You can change these default alignments. For example, headings are generally center aligned. Open the Excel sheet. Select the cell whose text needs to be aligned. Click Format | Cells. Click on the Alignment tab. Under Text alignment, from Horizontal, select the required options, for example, select Center. Under Text alignment, from Vertical, select the required options, for example, select Bottom. Click OK.
Click Format | Cells. Under the Line section, from Color, select the color that you want to apply to the border.
Click on the Border tab.
Under the Border section, click on the required buttons depending on where you want the border to be applied for the selected range of cells. Note: You can also use the Formatting toolbar to apply borders.
Click OK.
1 2 3 4 5 6
After applying borders, this is how the sales data will appear:
STEP 6: Use AutoFormat to apply a design scheme

If you have very little time to prepare a worksheet and yet want it to look easily comprehensible, you can use the AutoFormat feature. This feature provides you with some pre-defined formatting schemes, which you can apply to your data to make it more readable.
Select the cell/s to which you want to apply a formatting scheme.
7
Click on the required formatting scheme.
Click Format | Autoformat.
Click OK.
Make drop lists for your surveys

Survey respondents can often come up with the most offbeat answers to your questions. Check boxes could perhaps solve this problem, but they seem boring and outdated. Try using Excels drop lists to add more appeal to your survey forms
References
STEP 1: Rename your Sheets

The first step to making a presentable survey would be to rename the sheets. This helps in organizing your data and making it easier to use. Renaming sheets also helps users become aware of the sections in the workbook.
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. Customize your settings.
1 2 3
Open an Excel worksheet.
Rename sheet1 as Questions.
Select File | New
Right-click on the Sheet1 tab and click Rename.
Similarly, rename Sheet2 as Lists.
CHANGE THE COLOR OF ACTIVE CELLS

Only the active cell on a worksheet will display a data validation drop-down arrow. To mark cells that contain data validation lists, you can add color to the cells. Right-click on the active cell (a cell with data validation). Go to Format | Cells. In the Format Cells dialog box that opens, click on the Patterns tab. Under the section Cell Shading, select the color of your choice (left click) and Click OK.
STEP 2: Creating the question list

For an effective survey, a list of questions needs to be short listed based on the research of the subject. The questions should be framed in such a way that it corresponds to any option on the drop list. Ensure that your questions are short so as to limit them to only one answer. This helps avoid confusion and also results in a more effective survey.
1 2 3 4
Click on the Questions tab (earlier known as Sheet1).
Type in a question in a cell, for example: How would you describe your sense of humor?
STEP 3: Creating the answer list

The next step would be to create an answer list to the questions in your survey. The answer list will contain all the options that will be visible in the drop list. It should contain values that are easy to understand. Also avoid short forms or abbreviations.
Click on the Lists tab (earlier known as Sheet2).
TIPs
PASSWORD PROTECT YOUR SHEET

Data validation is not foolproof. It can be circumvented by pasting data into the cell or by choosing Edit | Clear | Clear All. You can prevent this by password protecting your sheets. When you are working on a sheet that contains data validation, go to Tools | Protection. Click on Protect Sheet. Make sure that you have check marked the box that says Protect worksheet and contents of locked cells. You will be prompted to enter a password. Enter a password. The list of check boxes provided determines the freedom you can give to another user. It is advisable to check mark the first two boxes that read Select locked cells and Select unlocked cells.
1 2 3 7
4 5 6
Insert these options in cells A1 to A6.
Type the list of options to a question that appears on Sheet1.
Highlight the space A1 to A6 (also known as range A1:A6).
A corresponding list of options available for Question 1 would be Obscure, Witty, Sarcastic, Goofy, Whacky, Friendly. Click in the space next to the formula bar where A1 appears. Type a name for your list, for example: Humor List.
Go to View and click on Formula Bar to enable it.
2 3 4 5 6
8
STEP 4: Creating a drop-down list
Press Enter.
Creating a drop-down list in one or many cells of an Excel worksheet is very easy and very useful. With drop-down lists, there is no need to manually enter the same values again and you can be sure that the spelling is correct. Also by using drop-down lists, you can be sure that the respondents supply you with valid answers because the drop-down list limits them to a choice of preset answers.
Click on the Questions tab (earlier known as Sheet1).
Go to Settings |Allow.
Click on the cell where you need your drop-down list to appear.
Go to Data |Validation.
Select the List option from the drop-down box.
TIPs
ACCESS YOUR LISTS FROM ANYWHERE IN THE WORKBOOK

You can use the F3 key to paste names of the lists you have created. So instead of typing out the list name in the Source text box, all you need to do is click in it and then press F3 to select the list name of your choice. Go to Data |Validation. Click on Allow. Use the drop-down box to select List. Click on the Source field and press F3. A Paste name box will open up with lists that have been created. Simply select a relevant list and then click on it to enter it in the Source field.
In the Source field that opens up, type =HumorList.
1 2 3 4
A drop-down box appears on the selected cell with the options of the HumorList entered on the Lists sheet.
STEP 5: Using a combo box to create a drop-down list

When creating drop lists using data validation, you can see the drop-down arrow only when you have selected the particular cell. If you prefer visible drop-down boxes to invisible ones, the use of the combo box feature in Excel would be your best option. It allows you to create combo boxes as independent objects (or controls) that can be placed onto a worksheet and are not contained within a cell.
Go to View |Toolbars | Forms.
USE BORDERS TO CREATE A TABLE

Before typing the list of questions in the survey, ensure that you have a small table for respondents personal information. Use borders to enhance this section to give it a tabular look. Highlight the cells where the information appears using the mouse (left-click and drag). Then, right-click and select Format Cells. Click on the Border tab. Under the Presets section, click on the Outside and Inside buttons. Click OK.
Left-click on a suitable place in the worksheet and adjust the combo box according to the required size.
Click on the Combo Box on the Forms toolbar. Right-click on the combo box and select Format Control.
1 2 3 4 5
In the Format Control dialog box that opens, click on the Control tab.
Click on the red arrow to the right of the Input Range field.
10
box.
This will open the Format Object
TIPs
INSERT INSTRUCTIONS
Excel allows you to easily define a message that should appear whenever the cell is selected after you have decided the terms to the data validation. These messages are called input messages, and their purpose is to help the user understand what information should be entered in the cell. To enter a data validation input message, follow these steps: Select the cells in which you want to apply the data validation. On the Settings tab, apply the required data validation. Click on the Input Message tab to activate it. Add a check mark in the Show Input Message when cell is selected box. Type in your message heading in the title box, for example, type in Category. This text will appear in bold at the top of the message. Type your message in the Input Message box, for example, type in Select a category from the dropdown list. Click OK.
Go to the Lists sheet.
Select the range with the help of the mouse (left-click and drag).
10
Click on the red arrow again.
11
You are directed back to the main Format Object box. Now, click on the Control tab.
In the Cell Link field, the cell that is selected will display the combo box option that has been chosen from the drop list.
12 13
The Format Object box will appear. Now, click on the cell that displays the combo box selection.
Click on the red arrow to the right.
14
again.
Click on the red arrow
1 2 3 4 5 6 7
You will be directed to the main Format Object box.
15
Click OK.
11
TIPs
ENABLE ERROR ALERTS

An error alert can be displayed if invalid data is entered. If the error alert is turned off, users will be able to type any entry without receiving an error message. To turn the alert on: Select the cell that contains a data validation list. Choose Data |Validation. Click on the Error Alert tab. Insert a check mark in the box Show error alert after invalid data is entered. Select the style of your choice from the drop list. In the Title field, type the heading of the error message, for example, Invalid Category. This text will appear in bold at the top of the message. Type your message in the Error message box. For example, You must choose a category from the list. Click OK.
16
When you select a value, the linked cell should display the index in the drop list. For example, if the 5th option (Whacky) is chosen from the drop list, then the linked cell will show the number 5.
1 2 3 4 5 6 7 8
To shift a combo box, right-click on it, then left-click and drag it. You can then place the combo box any where on the worksheet.
STEP 6: Prevent invalid entries

Even though you have created data validation drop-down arrows on some cells, users may be able to enter invalid entries. There are two reasons for this: the first being that the error alert option is turned off. To turn on the error alert, select the cell that contains the data validation list. Another reason could be the presence of blank cells in the source list. If the source list is a named range that contains blank cells, users may be able to type any entry without receiving an error message. Follow these simple steps to prevent invalid entries:
Go to Data |Validation.
Add a check mark in the Show error alert after Invalid data is entered box.
2 5
Click on the Error Alert tab.
4 7
Click OK.
Again, navigate to Data | Validation.
Remove the check mark from the Ignore blank box.
6
12
Click on the Settings tab.
Click OK.
Pick up exactly what you want

Looking for specific data sets in a large table is not an easy task. With the Advanced Filter tool, you can specify exactly what you want and have the results in a new table.
STEP 1: Prepare the List Range
To use the advanced filter, you must first prepare a table with headings and values. This is the place from where data will be extracted. For this workshop, we are preparing a table with sales data for products sold in 2006. Later, we shall extract specific data from this table. Prepare the following table.
References
What is Advanced Filter?

Excel offers filtering tools to extract selective data from a table. But the Advanced Filter tool lets you specify multiple sets of criteria. For instance, you can ask for a list off all the products that were sold before a certain date whose sales amounts exceed $10,000. You can also use computed criteria, for instance, list all the employees whose salary exceeds the average salary.
For clarity, we have made the headings bold and also drawn a border around the table.
STEP 2: Criteria for the And condition

Next, we shall prepare a little criteria range, a place where you can specify some condition. The criteria range can be anywhere in the worksheet. But for easy readability, well put it below the list range.
Select the headings from the list range.
Using two rows for criteria
2
Paste the headings in the unused rows below the table.
Select Edit | Copy.
If you want to specify an OR condition, then you will need to use two rows for the criteria range (as shown in Step 4). For instance, if you want to see data for which the quantity was more than 100 or whose sales value exceeded 2,500. Under the heading Quantity on the first row of the criteria range, type in >100. Under Sales in the second row, enter >2500.
13
TIPs
Specifying a condition with AND

Suppose we want to search for all products sold in the first three months of 2006 whose sales value exceed $10,000. In speaking terms, this condition is List all products sold before 01-April-2006 AND whose value exceeds $10,000. The key word here is AND, which means both conditions should necessarily apply. To show AND conditions, you must specify both conditions in the same row.
In the row below the headings in the criteria range, type this condition.
Do not filter in-place

When extracting data from the table, according to a specified condition, you will have the option to filter the list in-place or copy it to another location. Excel is just asking you where to put the output result. We suggest that you do not choose to filter the list in-place. If you do so, then your original table will be lost; the results of the advanced filter will overwrite the original table. But if you choose to copy it to a new location, the original table will be left intact and the results will appear in an unused part of the worksheet that you choose.
STEP 3: Applying the Advanced Filter

Youve just specified the criteria/condition and have prepared a list range (table). But you need to tell Excel about these ranges (by selecting them) when applying the advanced filter.
Select the list range (the table with data, including the headings).
Repeating column headings

If you want to show criteria for values in a range, then it is necessary to repeat column headings in the criteria range. For instance, if you want data for a range of dates like between 1-Jan-2006 and 31-Jan-2006, then you can type the Date heading twice adjacent to each other. Under the first heading, type >=01-Jan2006, and under the second Date heading, type <=31-Jan-06. The same can also be used for Quantity. To show Quantity between 30 and 60, you will type two Quantity headings in the criteria range. Under the first type >=30 and under the second type <=60.
Select Data | Filter | Advanced Filter.
Under Action, select Copy to another location (see Tip in side bar).
Click in the box corresponding to the Criteria range.
Select the heading row and the row immediately below that in the criteria range.
Click in the text box corresponding to the Copy to dialog box.
6 7 8
Click in a blank cell, a few rows below the criteria range. Click OK to complete the command.
14
TIPs
Working across worksheets

The data is now filtered in the cell that you specified in the Copy to dialog box.
STEP 4: Criteria with Or

If you want to specify two criteria with an either-or type of condition, then use two rows in the criteria range. When selecting the criteria range during the Advanced Filter command, you will have to select three rows (two rows for the condition and one for the heading). Lets apply a condition that says list all product sales whose quantity was more than 100 or whose sales value exceeded 2,500.
Delete the previous conditions in the criteria range (but not the headings).
1 2 4
Type the following criteria.
Remember that your criteria range is now three rows.
For the OR criteria, be sure to select all the three rows in the criteria range.
If you work with a set of related worksheets, you may be considering having the output range in another worksheet or workbook. It is not possible to extract data to another worksheet or workbook. You have to extract it to the active worksheet. After you extract a set of rows, you can copy it to another worksheet or workbook. Your criteria range can be on another worksheet, but the list range and output range have to be in the same worksheet.
Again select the list range.
Click in the dialog box for Criteria range and delete what was previously inserted therein.
Using OR with and

You will learn that putting two criteria on the same row means AND. Putting criteria on different rows implies OR. But what if you want to use both OR and AND? For instance, if you want to see the Sales in the North that are >=2000 and also Sales in the South that are >=2000. In that case, repeat the condition on two rows of the criteria range as shown below.
Select Data | Filter | Advanced Filter.
6 8
Select Copy to another location.
Select the three rows in the criteria range.
Click in the Copy to box and delete the text that was inserted earlier.
10
Click in a blank row below the criteria range.
11
Click OK.
The data is now filtered a c c o rd i n g t o t h e criteria you specified in the criteria range.
15
Let Excel do the Solving!

Its the end of the month and once again you find yourself turning to your family and friends to see you through to pay day. Try using the Excel Solver to help you create your personal budget with a fixed amount of savings every month.
References
What is the Excel Solver?

The Solver is an Excel add-in that helps derive a single cell formulas exact desired value, its minimum or maximum possible value. The Solver does this by working on controllable worksheet cell values that are specified by the user to produce the selected cell formulas desired value. The user can also apply constraints to the cell values that Solver can use to arrive at the desired value.
STEP 1: Load the Solver add-in

Since the Excel Solver is an add-in, the Solver command or the Analysis group may not always be available immediately. You first need to load it to get started with the program.
Click on the Microsoft Office Button that appears on the top left corner of your worksheet.
Then click on the Excel Options button.
When should the Solver be used?

If you are a regular user of Goal Seek, then you will simply love using the Excel Solver. The Excel Solver is quite similar to Goal Seek, but only more powerful for calculations. The Solver is a great tool to use when you want to find an optimal solution to a problem, for example, an exact desired outcome or the lowest or highest possible outcome.
In the left pane of the Excel Options dialog box, click on the Add-Ins option.
In the Add-ins section, select Solver Add-ins.
Click on the Go button.
16
In the Add-Ins dialog box that opens up, insert a check mark in the Solver Add-in box.
TIPs
What is the Set Target box?

The Set Target box is used to enter the cell that allows the user to set the co-ordinates of a cell in a worksheet. It is essential that the target cell must contain a formula. The radio buttons Min, Max, or Value are selected based on the function they carry out; For example, to set the value of the target cell to be as large as possible, click on Max. To set the value of the target cell to be as small as possible, click on Min. To set the target cell value to be of a certain value, click on Value of, and then type the value in the box.
7
You will receive a prompt to load the solver. Click on the Yes Button.
Click OK.
8 9
Microsoft Office will then configure the settings in Excel.
10
After you load, click on the Data tab and then go to the Analysis group so you can access the Solver Add-in.
Save a scenario
If you want to experiment with different scenarios before finalizing on one, then try saving the first scenario before moving on to the next. Heres how: In the Solver Results dialog box that appears after pressing the Solve button, click on the Save Scenario button. In the Save Scenario dialog box, enter a name in the Scenario Name dialog box. Click OK.
STEP 2: Create a Solver-friendly worksheet

Once you have successfully run the Excel Solver, it is essential to build a Solver-friendly worksheet with all the data that is included in your calculations before you can start using the Solver. So if you are planning on starting your own home business, this is how your worksheet will look:
In your Excel Worksheet, prepare a table that represents your current budget determining what you spend on each month and exactly how much you spend in the area of expense.
Then enter your total income per month.
1 2
Using the SUM formula, calculate the amount of expenditure next to a cell that marks the Total Expenses.
Calculate the total savings (or debt) by calculating Total Income - Total Expenses next to the cell marked Savings.
Mark the area of expenses that are controllable so as to make the calculations easier.
17
TIPs
Guess changing cells

In the By Changing Cells box, you can enter a cell range or even non-adjacent cells by separating them with a comma. The changing cells must be related directly or indirectly to the target cell. The total number of changing or adjustable cells that a user can specify is upto 200. If you dont know which cells to change so as to arrive at the result, let Solver propose which cells are adjustable based on the target cell. Heres how: In the Solver Parameter dialog box, select the Target cell and click on the Guess button. This way the changing cells will be automatically entered by Solver into the Changing cells field.
Then, note the constraints that you have when creating your budget, for example, if you want to have a fixed monthly saving of $250. You also would not like the Phone budget to go below $50.
Also you would not like the total of your shopping and entertainment expenses to go below the $400 mark.
The Dining Out expenses should be atleast $100.
STEP 3: Run the Solver

Now that you have marked all the areas of expenses along with the expenditure per month and the constraints you want to see in your budget. You can run the Solver to have it come up with your ideal budget. Read on to learn how.
To run the Solver, click on the Data tab, and then in the Analysis section, click on the Solver button.
1 2
1 2
In the Solver Parameters dialog box that opens, click on the Set Target Cell red arrow box and select the target cell of your choice. For example, click on the cell B17 that calculates the monthly savings.
Select the Value of radio button and enter the exact desired value of the target cell. (Refer to the tips in this workshop to understand the use of the Min and Max value radio buttons.)
In the Changing Cells: field, click on the red arrow to enter the cells that you wish to change.
Reset all the parameters

If you want to remove the constraints that have been set in Solver and start all over again, this is how to do it: In the Solver Parameters dialog box, click on the Reset All button. Click on the OK button in the Microsoft Excel dialog box that appears. All the parameters set in Solver will then disappear and you can start afresh.
In the Solver Parameters dialog box that opens, click on the cells that have earlier been marked with green as the controllable areas of expenditure, for example, enter B7,B10,B11,B12. It is essential to separate cells that are not in the range by using commas.
5 7
Click on the red arrow again to be navigated back to the main Solver Parameters dialog box.
1 2 3
To add constraints to the change of cell values, click on the Add button.
In the Add Constraint dialog box that opens up, click on the red arrow to select the cell reference of your choice.
18
Enter the cell reference of the current budget for a particular area of expense that is controllable. For example, select cell $F$8, which is the Entertainment + Shopping cell reference.
TIPs
Change the precision

Precision is the measure of accuracy of a particular calculation in Excel. By default, Excel stores and calculates with 15 digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas. Heres how: Click on the Microsoft Office Button and then click on the Excel Options button. In the Excel Options dialog box, click on the Advanced category. In the When calculating this workbook section, select the workbook you want and then check mark the Set precision as displayed check box. Click OK.
10
Then add the other criteria to the constraint. For example, you would like to have your Entertainment + Shopping budget to be worked upon but would like the decided budget to be greater than or equal to (> =) $400 (cell reference: =$B$21).
1 2 3
11
Similarly enter the other constraints according to your preference.
12 13
The Solver Results dialog box opens up with the solution. If the Solver solution is what you want your personal budget to look like, click on Keep Solver Solution and then OK.
Click on the Solve button.
4
14
If you want to change the constraints, select the Restore Original Values radio button and then click OK.
Create a report
Once Solver has found a solution, you can create a report that is based on this solution. Heres how: In the Solver Results dialog box, select the options applicable to your report from the Reports field. Click OK. The report is created on a new worksheet in your workbook. If Solver doesnt find a solution, the option to create a report will be unavailable.
The solution process by Solver can be interrupted anytime by just pressing Esc. Microsoft Office Excel will then recalculate the worksheet with the last values that are found for the adjustable cells.
15
1 2 3
Crosscheck the solver solution with your constraint so you will find that all the constraints have been met as Solver had notified.
19
Calculate Faster with Goal Seek

Youve got a fixed amount of your monthly salary that can go towards your loan payment and are now wondering which interest rate will best suit you. Try using Excels Goal Seek feature to help you speed up your calculations.
References
What is Goal Seek?

Goal Seek is part of a suite of commands sometimes called what-if analysis tools. When you know the desired result of a single formula but not the input value of the formula needed to determine the result, you can use the Goal Seek feature available by clicking on Goal Seek on the Tools menu. When goal seeking, Microsoft Excel varies the value in one specific cell until a formula thats dependent on that cell returns the result you want.
STEP 1: Create a table

The use of estimate amounts for calculations will result in an inaccurate result. Hence, the first step to answer a loan-related query is to get accurate values for all the factors revolving around the loan scheme. Before initiating the goal seek feature, enter these values into a new worksheet.
Enter the factors Loan Amount, Term in Years, Interest Rate, and Monthly Payment in cells A1, A2,A3, and A4.
In cell B2, enter the number of years it would take to complete the loan payment. For example, the term allocated to complete the loan payment is 15 years.
In cell B1, enter the loan amount that you need to pay back to the lender; for example, the total loan amount is $100,000.
Then in cell B3, enter the estimated interest rate that is likely to result in a value close to your fixed monthly budget. For example, on considering the previous values, you can estimate an interest rate of 9% if you have a monthly budget of $900.
STEP 2: Enter the PMT function

In order to use the Goal Seek tool effectively, the base model must be set up in your worksheet with the formula already in place and working.
In the cell B4, enter the formula =PMT(B3/12,B2*12,B1) to calculate the monthly payment. (Refer to tip Access the PMT Function Quickly).
What is the PMT Function?

The PMT function calculates monthly payments based on the loan amount, interest rate, and length of time it will take to pay back the money. The syntax for the PMT function is PMT (interest rate per period, number of payments, and loan amount).
20
You see that a monthly payment of $1,014.27 is calculated.
TIPs
Access the PMT Function Quickly

Cant remember the exact function to get a result? Excel has inbuilt feature to find a function immediately. Use this feature to understand the PMT function. Click on Insert | Function. In the Insert Functions dialog box under Search for a Function Dialog Box, enter a word that describes the function you require. For example: Enter the Monthly Payment, the PMT function will be instantly recommended along with a brief description and click OK. Then, in the Functions Arguments dialog box in the Rate field, enter the interest rate per period, which is the monthly interest rate equal to the annual interest rate divided by 12 months, for example, 9%/12. In the Nper field, enter the number of years of the loan multiplied by 12 months. For example, 15*12 is the total number of months for the loan payment completion. In the Pv field, enter the total loan amount; for example, the total loan amount is $100,000. Click OK. The result of the function is displayed in the cell where the formula has been entered.
1 2
STEP 3: Use the Goal Seek tool
Goal Seek can be used when you know the result of a formula but not the input value required by the formula to decide the resulting reverse calculation. You can change the value of a specified cell until the formula that is dependent on the changed cell returns the result you want.
Go to Tools | Goal Seek.
3
In the Goal Seek dialog box that opens up, click on the cell containing the formula to enter it into the Set cell field. In the field To value, enter the value that you need to arrive at. For example, you have a target value of $900 as the fixed monthly payment you can afford. Enter this value as -900.
2 3
Then, in the field By changing cell, enter the cell reference of the value that needs to be changed. For example, cell B3 needs to be clicked in order to change the interest rate.
Click OK.
The Goal Seek Status dialog box appears with the information of the cell reference, the Target Value and the Current Value that Goal Seek has arrived upon.
Click OK.
5 6
The Set cell must always contain a formula or a function, whereas the Changing Cell must only contain a value and not a formula or function.
21
Budgeting with ease!

Youve worked out the family budget and find you barely have enough left for the upcoming family vacation. With Scenario Manager, you can experiment with different budgets and determine what you can cut back on to give yourself more spending money.
References
What is Scenario Manager?

When analyzing you data, you will frequently want to look at a number of differing options within your spreadsheet. Scenario Manager allows you to change multiple cells in order and see the effect of the changes and keep your scenarios to review them later. With Scenario Manager, the scenarios are useful for forecasting the results of models and they can also be printed in a summary report.
STEP 1: Create a Family Budget table

In order to start using the Scenario Manager tool, you need to first create a basic table of your budget along with the related values on an Excel worksheet.
Create a table named Family Budget with the values of Income, Total Outgoings, Income Left, and Areas of Expenditure with the corresponding Outgoings for each area. In the cell corresponding to the Income Left, enter the formula to calculate the income left after deducting the Total Outgoings from the total Income. For example, in the cell D13, enter the formula = (D3-B12).
In the cell corresponding to the Total Outgoings, enter the formula to calculate the sum of the all the outgoings. For example, in the cell B12, enter the formula =SUM (B3:B10).
STEP 2: Add a scenario

After creating the basic data table, determine the values that you can experiment with, for example, Food Expenditure, Shopping Expenditure, and Phone Bill.
PREVENT CHANGES IN SCENARIOS

When adding a scenario in the Add Scenario dialog box, check mark the Prevent changes check box in order to lock your scenarios and prevent them from being edited. Here, it is important to note that you must also protect your worksheet for the Prevent changes feature to take effect.
Go to Tools | Scenarios.
In the Scenario Manager dialog box, click on the Add button.
22
Then, in the Add Scenario dialog box, enter Original Budget in the Scenario Name field.
Click on the red arrow on the box next to the Changing cells field.
TIPs
Protect your worksheet

Follow these steps to protect your worksheet: Go to Tools | Protection | Protect Sheet. In the Protect Sheet dialog box, insert a check mark in the Protect worksheet and contents of locked cells check box. In the Password to unprotect sheet field, type a password to protect your sheet. Then, in the Allow all users of this worksheet to: section, use the scroll button to uncheck the Edit Scenarios check box. Click OK. In the Confirm Password dialog box, re-enter your password to validate it. Click OK.
1 2
Select the range of cells in your worksheet to enter them in the Edit-Scenario-Changing cells dialog box. For example, select the range $B$7:$B$9 for Food Expenditure, Shopping Expenditure, and Phone Bill.
3
6
In the Edit Scenario dialog box, click OK.
5 6
The Scenario Values dialog box opens up with the range of cells earlier selected and their corresponding values.
select Adjacent changing cells

Click OK.
If you need to select adjacent cells in your data table as the changing cells instead of an entire cell range, just press the Ctrl key and then select the cells of your choice.
The values remain untouched in the Scenario Values dialog box since this is the basic scenario needed for comparison in the budgeting process.
STEP 3: Create additional scenarios

After adding a basic scenario to imitate the original budget, create another set of scenarios to experiment with in order to obtain a budget that will allow for maximum savings.
23
TIPs
Hide your scenarios

The Hide option when selected will do exactly as its name suggests; it will hide or prevent users from viewing your scenarios. If you need to hide your scenario, ensure that you check mark the Hide check box when adding a scenario in the Add Scenario dialog box. This will lock your scenarios and prevent them from being viewed. This option also requires sheet protection for it to take effect. The Hide option will prohibit those users without a protection password from viewing scenarios other than the one on the worksheet.
Once again, go to Tools | Scenarios.
In the Scenario Manager dialog box, click on the Add button.
Then, in the Add Scenario dialog box, enter Budget 2 in the Scenario Name field.
Keeping the other values the same, click OK.
Then, in the Scenario Values dialog box, enter new values for each of the cells in the cell range for the purpose of budgeting. For example, in the field $B$7, enter the value 180; in $B$8, the value 100; and in $B$9, the value 20.
Edit a scenario
You miprocess of budgeting. To do this, Go to Tools | Scenarios. In the Scenario Manager dialog box, select the scenario you need to modify. Click on the Edit button. In the Edit Scenario dialog box, you can make changes to Scenario Name, the Changing cells field, which determines the cells that will be changed, and also the Comments field.
Click OK.
1 2 3 4
Similarly, create additional scenarios with new values for the purpose of experimenting with various budgets. For example, create Budget 3, Budget 4, Budget 5 etc. with different values for $B$7, $B$8, $B$9.
STEP 4: Budgeting with Scenario Manager

Scenario Manager allows you to create multiple scenarios with different values. It then enables you to look at a number of differing means of arriving at the same goal.
24
In the Scenario Manager dialog box, select Budget 2.
1 2
The Total Outgoings will be reflected in cell B12. For example, the Total Outgoings for Budget 2 is $979.
TIPs
Merge scenarios
With Scenario Manager, you can bring together similar scenarios set up in different workbooks into your current worksheet. However, this will only work if the input cells are exactly the same as those nominated in your current worksheet. This can be easily done by following these simple steps: Go to Tools | Scenarios. In the Book field, use the drop list to select the workbook from where you have to select your scenario. In the Sheet field, select the specific sheet in your workbook that contains the scenario that you need to merge with the scenarios in your current workbook.
Clicking on the Show button will enter the values of Food Expenditure, Shopping Expenditure, and Phone Bill entered in Budget 2. For example, the value of Food Expenditure changes to 180, that of Shopping Expenditure changes to $100, and that of Phone Bill changes to $20.
The Income left will be displayed in cell D13. For example, Income Left for Budget 2 is $521.
1 2
STEP 5: Summarize your scenarios

Excel provides a command that enables you to summarize all your scenarios in one worksheet; you can then print the worksheet to make simple comparisons of your various scenarios.
In the Scenario Manager, click on the Summary button.
In the Scenario Summary dialog box, select the Scenario Summary radio button for a report style format of your scenarios or the Scenario Pivot Table report radio button for a Pivot table style format.
Delete a scenario
You may need to delete a scenario if you find that it is no longer relevant during the course of budgeting. Follow these steps to delete a scenario: Go to Tools | Scenarios. In the Scenario Manager dialog box, select the scenario you need to delete. Click on the Delete button.
In the Result cells field, enter the cell that reflects the income left. For example, in this case, enter D13 as it corresponds to the Income Left in the Budget.
1 2
Use the + or - symbol to display or hide the data in the summary.
Click on the Sheet 1 tab to go back to the Family Budget table. The Scenario Summary reflects the changing values for each scenario and the corresponding result. The current values column represents the values of the changing cells when the summary was created.
25
Summarize with Excel Consolidate

Are you spending painstaking hours trying to create reports that need you to merge huge amounts of data? Dont fret. Excel is capable of doing the same task in just a few minutes! Read on to find out how.
References
What is consolidation?
Consolidation is the process of combining values from several ranges of data either from within the same or different workbooks. It can be used to summarize data from different worksheets into a master worksheet and create a report using a variety of calculations.
STEP 1: Consolidate by position

This type of consolidation is used when worksheet data is identical in terms of order and location. It is the easiest form of consolidation and works using a layering operation.
Open the workbook you wish to consolidate the data in. Click on the upper left corner of the destination area for the consolidated data. This is where your consolidated data will begin.
In the Consolidate dialog box, use the Function drop list to select the summary function of your choice. For example, if you need to add the data from several worksheets use the SUM function.
Use labels
If youre consolidating by position, leave the boxes under Use labels in blank. Microsoft Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually. If youre consolidating by category, select the check boxes under Use labels in that indicate where the labels are located in the source ranges (either the top row, left column, or both).
Go to Data | Consolidate.
4 5
After selecting the range, click on the red arrow again in the Consolidate-reference dialog box in order to be navigated back to the Reference field.
To enter a source area in the Reference field, click on the red arrow to select a range from within the worksheet.
For example, if you need to find out the total number of new employees hired for each month, per region, select the ranges that has the corresponding data you require from the various worksheets. Also ensure that the d a ta i n a l l yo u r worksheets are in the s a m e o rd e r a n d position.
Click on the Add button.

26
Similarly add the range information from other worksheets that you would need to consolidate. Ensure that the data to be consolidated is in the same position and order for all the ranges selected.
Leave the Top Row and Left Column boxes unchecked.
TIPs
What is a 3D formula?
3D formulas are formulas that refer to multiple worksheets and can be used to combine this type of data. These are also known as cubed formulas. A reference that refers to the same cell or range on multiple worksheets is called a 3D reference. Using 3D formulas allows you to calculate data throughout a workbook using multiple worksheets. All 3D formulas are based on the syntax: Sheet1:Sheet4! A2:B5.
9 10
The result data appears in the exact position where the data to be consolidated appeared in the respective worksheets.
Click OK
When you consolidate by position, Microsoft Excel does not copy the category labels in the source areas to the destination area. If you want labels for the destination worksheet, copy them or enter them manually.
Consolidate by category
This type of consolidation is used when data is organized differently but has identical row and column labels. Excel examines the row and/or column headings and plots the layout of your worksheets and consolidates your data for you by examining the contents of the ranges to be used.
Open the worksheet in which you wish to consolidate the data. Click on the upper left corner of the destination area for the consolidated data. This is where your consolidated data will begin.
View many worksheets at one time

If you need to refer to data on different sheets in the same workbook, heres how you can do it: Ensure that you have only a single workbook open. Go to Window | New Window. Excel opens up a second window of the same workbook. Go to Windows | Arrange. Excel will display the Arrange Windows dialog box. Choose the method by which you want Excel to arrange your windows. For example, you could select Tiled if you need to see the display of the workbooks in the manner displayed in the screenshots in this workshop. Click OK.
2 3 4 7
Go to Data | Consolidate.
In the Consolidate dialog box, use the Function drop list to select the summary function of your choice. To enter a source area in the Reference field, click on the red arrow to select a range from within the worksheet after selecting the range.
In the Consolidate referencedialog box, click on the red arrow again to be navigated back to the Reference field.
5 6 8 9
1 2
3 4
Similarly add the range information from other worksheets that you would need to consolidate. For example, this consolidation is required if the ranges that you have to select to consolidate your data do not appear in the same position and order.
Check mark the Top Row and Left Column check boxes.
Similarly add the range information from other worksheets or workbooks that you would need to consolidate.
10
Click OK
27
TIPs
Description of summary functions

Sum: The sum of the values. This is the default function for numeric source data. Count: The number of items. The Count summary function works in the same way as the COUNTA worksheet function. Count is the default function for source data other than numbers. Average: The average of the values. Max: The largest value. Min: The smallest value. Product: The product of the values. Count Nums: The number of rows that contain numeric data. The Count Nums summary function works the same way as the COUNT worksheet function. StdDev: An estimate of the standard deviation of a population, where the sample is all the data to be summarized. StdDevp: The standard deviation of a population where the population is all the data to be summarized. Var: An estimate of the variance of a population where the sample is all the data to be summarized. Varp: The variance of a population where the population is all the data to be summarized.
11
The data appears with the headings and the addition of all the data in selected ranges irrespective of where the range appears in the worksheet.
Consolidate using 3D formulas

This type of consolidation is used when worksheet data does not have a consistent layout or pattern. It does not use the Consolidate dialog box found under Data | Consolidate but is created entirely as a formula; therefore, it will automatically get updated if the data it is dependent on changes.
Open the worksheet that you wish to consolidate your data in using 3D formulas.
A 3D formula can be used to add cells from different worksheets to consolidate the data into a Summary sheet. For example, if you need to consolidate the total figures for the month of January only for Region 1, then in the Total Sheet (or the worksheet where you would like to see your consolidated data), enter the function =SUM (North ! B14,South ! B16,East ! B4, West! E17).
You can also add ranges from different worksheets to consolidate data into one Summary sheet. For example, if you need to consolidate the total figures for the month of January only for all the regions, then in the Total Sheet, enter the function =SUM (North ! B14:B16,South ! B16:B18,East ! B4:B6, West! E17:E19) to correspond to the data in your worksheets.
28
Save time with a Pareto!

How would you like to get three of your jobs done in the same time as one? If youve been struggling to manage time, dont fret. Try using Excel to build a Pareto that will help you determine the root causes that you can work on to help you save the most amount of time.
References
STEP 1: Create a data table

In order to create a Pareto chart in Excel, setup a data table spreadsheet. The example used in the workshop focuses on how to use your time more efficiently by categorizing where you spend most of your time during the day and how you can pinpoint the exact places on which you can work on to increase the time you have available.
When should a Pareto chart be used?

A Pareto chart is an excellent tool to use when the process you are introspecting produces data that can be broken down into categories, wherein you can count the number of times each category occurs. A Pareto diagram puts data in an descending order, that is, from the highest to the lowest values, which allows the most significant problems to be corrected first. Although it can summarize all types of data, this technique is used primarily to identify and evaluate inconsistencies. Which is why, it is one diagram that is most often used in management studies.
In the first column, mention the categories of your data. Mention all the controllable factors that occupy your time during the week.
In the second column, mention the time (in hours) spent on each task. It is also essential to sort the data in descending order. In order to do this, go to Data | Sort.
Calculate the Sum of the total time spent in cell B:13
In the Sort dialog box, click on the Descending radio button in the Sort by section.
Then, in the third column, calculate the cumulative sum of the second column. In order to do this, use the formula =SUM(C2+B3) (as shown in the screenshot)
29
TIPs
Use Fill Effects

Adding effects to a chart that you created not only enhances the look of the chart but also plays a big role in making your presentations easier to understand. In order to use the Fill Effects: Select the specific area in the chart that you wish to apply fill effects to. For example, if you wish to use the Fill Effects feature in the bar data series in the chart, select the data series using the mouse. Then go to Format | Selected Data Series. In the Format Chart Area dialog box, click on the Fill Effects button. In the Fill Effects dialog box, select from the Gradient, Texture, Pattern, and Picture tabs to fill in the effects of your choice in the bar data series.
In the fourth column, calculate the cumulative percentage of the third column. To do this, use the formula =C2/$B$13. (The value of =C2/$B$13 is turned to constant. In order to convert a value to constant press F4.)
When calculating the time spent in hours, the unit must remain constant. So if you are calculating your data in hours, avoid using another unit like minutes to express it elsewhere.
STEP 2: Create a Pareto chart

Now that you have the data sorted out in a table, the next step would be to create a Pareto that diagrammatically represents that data. In order to create a Pareto, follow these simple steps.
Use the [Ctrl] key to select the noncontiguous columns and highlight the activities per week column, time spent per week, and cumulative % data. In the figure, that would imply that the cell ranges are A2:A12, B2:B12, and D2:D12.
2 3 4
In the Chart Wizard -Step 1 of 4 - Chart Type dialog box that opens up, click on the Custom Types.
In the Select from section, select the Built-in radio button.
2 5
Go to Insert | Chart.
How is a Pareto chart read?

A Pareto chart is a series of bars whose heights reflect the frequency or impact of root causes of most significant errors. The bars are arranged in descending order of height from left to right. Hence, the categories represented by the tall bars on the left are relatively more significant then those on the right.
In the Chart Type section, select the Line - Column on 2 Axes. In the Chart Wizard Step 2 of 4 - Chart Type dialog box in the Data range field, type =Sheet1!$A$2:$B$12,She et1!$D$2:$D$12.
4 7
Click Next.
In the Series section, select the Columns radio button.
Click on the Next button.
30
10
In the Chart Wizard - Step 3 of 4 - Chart Type dialog box, click on the Titles tab and enter the Chart Title, the Category (X) axis, and the Value (Y) axis.
TIPs
Why should a Pareto chart be used?

What a Pareto chart does is it breaks a big problem down into smaller pieces thus helping to identify the most significant factors. In this way, it shows exactly where to focus our efforts instead of wasting time on the trivial factors. A Pareto chart most importantly answers the following questions: What are the 20% of sources that are causing 80% of the problems? Where should we focus our efforts in order to achieve the greatest improvements?
11 12
In the Chart Wizard -Step 4 of 4 - Chart Type in the Place Chart section, select the As new sheet radio button. In the field, type the name of the Pareto chart, for example, type Pareto_Time.
Click on the Next button.
13
Click on the Finish button.
1 2
STEP 3: Use the 80:20 rule

Once you have created the Pareto chart that displays the data, it is essential to then use the 80:20 principle to determine the main activities that you need to focus on to help save the most amount of time. Follow these steps to do this.
Go to View | Toolbars.
Format the chart

Formatting a chart to make it more visually appealing is just a few clicks away and the dramatic change in its appearance is really worth the effort. To format a chart, follow these simple steps: Select on the specific area in the chart that you wish to format. For example, if you wish to format the plot area in the chart, select the plot area using the mouse. Then go to Format | Selected Plot Area. In the Format Chart Area dialog box, click on the formatting options from the Patterns and Area sections. Click OK.
Click on Drawing to select the Drawing toolbar.
2 4
1 2 3
Click on the Line tool and then use it to draw a line from the 80% mark on the right to the graph line.
The Activities (categories) that feature towards the left and do not come in this section are the ones that take up the most of your time. So according to the 80:20 rule, focusing on these categories will help save 80% of your time. Use the tips in this article to format the Pareto Chart to make it more visually appealing and also easier to understand.
31
Getting data from the Web

Ever had to regularly go through the laborious process of manually entering data from an online data source? Then try using the powerful Web Query feature in Excel that enables you to automate this task. Read on to learn how.
References
What can you retrieve from the Web?

You can use a Web query to retrieve refreshable data such as a single table, multiple tables, or even an entire Web page. Web queries are especially useful for retrieving data that is in tables or pre-formatted areas (The pre tag tries and makes the Web browser display the text as you type it). Most tables on the Web are defined with the HTML (Hypertext standard Markup Language). The data that is retrieved from the Web will not include pictures, such as .gif images and the contents of scripts.
STEP 1: Create a Web query

In order to create a new Web query, you must first be connected to the Internet. Then, open the Excel Worksheet where you would want to see your imported data and follow these steps to create a new Web query.
Click on the Data tab and then go to Get External Data | From Web.
In the New Web Query dialog box that appears, type in the website that you wish to import the data from in the Address field.
Click on Go.
4 5
Scroll through the website to select a particular table of your choice. Click on the yellow box with the black arrow icon next to the tables you wish to select.
Click on the entire Web page if you want to import it.
On clicking on the icons, they will turn to green boxes with black ticks to show the tables that you have selected.
7
32
After completing your selection, click on the Import button.

In the Import Data dialog box, click on the worksheet and the cell location of your choice for your imported data. For example, import your data into your existing worksheet.
TIPs
Web Query options

In the New Web Query dialog box, click on the Options button that features on the extreme right side. Here you can set the formatting options of whether you want to have rich formatting or full HTML formatting, import preformatted <PRE> blocks into columns, and also whether or not you want to use the same import settings for the entire selection. You can also adjust other settings such as Disable date recognition and Disable Web query redirections.
Excel will then begin importing the selected data from the Web.
Format the worksheet using the formatting options in the Home tab.
STEP 2: Refresh the data

Depending on your needs, you can get data from a Web page and keep it static on the worksheet or you can enter data in your worksheet that will be updated with the latest data on the Web page. Follow these steps to refresh your data:
Click on the Data tab and then go to Refresh All | Refresh All to refresh all the data on your worksheet.
Hide gridlines
Excel makes it easy to work with numbers and text by providing gridlines that align data in each cell, making it more readable. Once you have entered all the data, you may want to hide the gridlines and only display the data. Hiding the gridlines does not change the layout of the data. This keeps the data readable without providing a tabular format to it. To hide or display gridlines: Open the Microsoft Excel document. Click on the View tab. Select Hide | Gridlines.
Select Refresh All | Refresh to refresh only a selected portion of your worksheet. In the Connection Properties dialog box that opens, under the Refresh control section, check mark the Refresh every check box.
2 4
To set the time for automatic refreshing of your data, go to Refresh All | Connection Properties. Enter the interval at which you want your data to be retrieved from the Web. For instance, you can have your data refreshed every 10 minutes.
3 6
1 2 3
Also check mark the Refresh data when opening the file option.
Click OK
33
TIPs
Edit a query
If you have missed out on information while creating your Web query, dont fret. Heres a simple way to help resolve the issue: Highlight the range of cells that has been imported and then rightclick on your mouse. Select the Edit Query option. Then in the Edit Web Query dialog box, reselect the table of your choice. Click OK.
STEP 3: Conditionally format the sheet

With conditional formatting, the greatest benefit is that it allows viewers to easily identify the data at one glance. In case you are dealing with currency exchange and want to seize the best chance for doing so, heres how you can use conditional formatting to check for a particular surge in a currency rate.
1 2 3 4
Select the cell that you wish to format.
Then click on the Home tab and then go to Conditional Formatting | Highlight cell Rules.
Save A query
In the New Web Query dialog box, click on the Save Query button to save the Excel workbook with the current Web query in the IQY (.iqy) format. This format can be opened in Notepad and it contains all the settings for the query for further reference. The default file name can be replaced by something easier to remember. This IQY (.iqy) file can then be saved anywhere you want, youll then probably find it convenient to save the file to the folder C:\Program Files\Microsoft Office\ OFFICE11\QUERIES. This will be helpful when you choose to import external data from the Web, Excel automatically displays the Select Data Source dialog box, which includes the queries from this folder.
Depending on your condition, select the option of your choice from the drop-down list. For example, if you want to see when there is a change in currency cross rate of the UK with the US $ as value of 1.95 and above. In the Greater than dialog box, enter the cross rate value 1.95.
In this case, click on the Greater than option. Use the formatting options from the available drop-down list to format the cell according to your choice when it meets the condition.
3 5
Click OK
The Conditional Formatting feature can also be used to format the cross rate if in case it does not meet the expected value in the cell, using the Lower than option.
The cell which has been conditionally formatted is highlighted because it met the condition requested. The formatting is applied even after the data is refreshed.
34
Visually enhance your data

Find yourself lost in the sea of data on your Excel sheet? Try using the all new and improved conditional formatting in Excel 2007 to easily locate your data, find exceptions, and spot trends.
References
STEP 1: Highlight certain cells

In order to begin using the various conditional formatting options available in MS Excel 2007, you need to start by first creating a table in Excel that features all your data. Once youve done that, you can start with the first option of conditional formatting, which is to highlight only certain cells in your worksheet depending on their value. Heres how to do that.
Select the Home tab and then click on the Conditional Formatting drop list from the Styles section.
1 2 5
Click on the Highlight Cells Rules option from the drop list.
How does Conditional Formatting in Excel 2007 differ from that in Excel 2003?
With the new Excel 2007, conditional formatting is a cinch. With earlier versions of Excel, the conditions were limited to 3 per cell, whereas the new version allows more than 3 conditions to be used for formatting. When more than 3 conditions are used, they will all be saved, however only the first 3 conditions will be displayed in the previous Excel versions. With great new visualization features such as data bars, color scales, and icon sets, visually enhanced data in Excel is only a click away. And unlike earlier versions in Excel that calculate the conditions across all values, the new Excel allows users to calculate the Top 10 or Compare to Average from all values.
Select the range of cells in your worksheet where you would like to highlight certain cells depending on their value.
Select the Greater than option from the drop list. If you click on the red arrow, you will be directed to the Greater than dialog box.
4 6
In the Greater than dialog box that appears, click on the red arrow to select a value from your worksheet or simply type in the value.
In the Greater than dialog box, click on the cell that displays the value that will reflect the cell value that you would like to start from. Your cell range will then feature highlighted cells with that value and higher values.
35
TIPs
What is the Conditional Formatting Rules Manager?

The Conditional Formatting Rules Manager dialog box is the central place to view and manage all of the conditional format rules in a worksheet. With this dialog box, you can view the rule condition to see the range to which it is applied and the formatting applied by the rule. The Show formatting rules drop list in the dialog box allows users to describe the exact location of the rules. You can view rules for the selected cells, the current worksheet, or other worksheets in the workbook.
Once you are navigated to the original Greater than dialog box, use the with drop list to select which formatting you would like or select Custom Format to create your own formatting.
11
Click on More Rules if you want to adjust the settings of the data bars displayed.
10
Click OK.
Similarly you can conditionally format your other data with the other options available in the H i g h l i g h t ce l l r u le s section.
STEP 2: Use Top/Bottom Rules

The conditional formatting section in MS Excel 2007 also has a great new feature of using Top/Bottom rules to identify certain cells depending on their values. Heres how:
1 2 5
Click on the Top Bottom Rules option from the drop list.
Select the range of cells in your worksheet where you would like to highlight certain cells depending on their Top/Bottom values.
4 7 8
Select the Top 10 items options from the drop list. Click OK.
How is a color scale different from a data bar?

A color scale uses cell coloring and not color bars in cells to express relative values. This is extremely useful when you want to communicate something about the data in your worksheet that goes beyond the relative value of a cell; for example, if you want to identify the high numbers and/or low numbers to check the current sales figures, or if you want to understand variation in your data, color scales are a great choice.
In the Top 10 items dialog box that appears, use the scroll list to select the number of top items if they are more or less than 10.
You will notice that the top ten values (or the value set) in the selected range will be highlighted. Select the option of your type from the data bars displayed or click on More Rules to customize the Top/ Bottom Rules.
Use the With drop list to select which formatting you would like or select Custom Format to create your own formatting.
STEP 3: Using data bars

Another very effective option that is part of conditional formatting is data bars, which distinctly color each cell with bars depending on their values. Heres how:
1 2
Select the Home tab and then click on the Conditional Formatting drop list from the Styles section. Select the range of cells in your worksheet where you would like to highlight cells using data bars.
3 4
Click on the Data Bars option from the drop list.
Select the option of your type or click on More Rules to customize your data bars.
You will see that the cells in your selected range contain data bars that visually represent the values contained in the individual cell.
36
STEP 4: Using color scales

Using the color scales option allows the user to color cells based on their values. The cells are colored so as to allow the user to identify the value of each cell. Heres how you can make use of the color scales option:
TIPs
Format unique or duplicate values
1 2 3 4
Click on the Color Scales option from the drop list. You will see that the cells in your selected range contain color scales that reflect colors depending on the value contained in the individual cell. Select the option of your type from the color scales displayed or click on More Rules to customize your color scales.
3 4
Select the range of cells in your worksheet where you would like to highlight cells using color scales
STEP 5: Using icon sets

The icon sets are another option in the Conditional Formatting section in Excel 2007 that give you an opportunity to create visual effects. It will help your see how the value of a cell compares with that of other cells. Heres how to implement this feature:
Select the Home tab and then click on the Conditional Formatting drop list from the Styles section. Select the range of cells in your worksheet where you would like to highlight certain cells depending on their values. Select the option from the icon sets displayed or click on More Rules to customize your icon sets.
1 2
Click on the Icon Sets option from the drop list.
3 4
You will see that the cells in your selected range contain color scales that reflect colors depending on the value contained in the individual cell.
5 6 1
Select the Home tab and then click on the Conditional Formatting drop list from the Styles section. Click on New Rule. In the New Formatting Rule dialog box that opens, click on Format only unique or duplicate values from the Select a Rule Type: section. In the Edit the Rule Description: section, use the Format all drop list to format either the unique or the duplicate values in the selected range. Click on the Format button to select the Formatting of your choice. Click OK.
Format values that are only above or below average

Similarly to format values that are above or below average, in the New Formatting Rule dialog box , click on Format values that are only above or below average from the Select a Rule Type: section. In the Edit the Rule Description: section, select the option from the Format values that are: drop list in order to select the cells you want to format in relation to the average of your data values.
STEP 6: Edit an existing rule

If you have already created a rule and now want to edit that existing rule, heres how to do it:
Click on Manage Rules.
37
TIPs
Format all cells based on their values

Select the Home tab and then click on the Conditional Formatting drop list from the Styles section. Click on New Rule. In the New Formatting Rule dialog box that opens up, click on Format all cells based on their values from the Select a Rule Type: section. In the Edit the Rule Description: section, make use of the Format Style, Type, Value, and Color in order to format the cells based on their values. For example, in the Format Style, select Icon Sets. Go to Icon Style and select the icon set of your choice. Assign the Value and Type for each icon depending on your preference and the data values in your worksheet. Click OK.
1 2 3 4
In the Conditional Formatting Rules Manager dialog box that appears, click on the Edit Rule button.
From the Select a Rule Type: section, select the existing rule in order to edit it. For example, select the Format only cells that contain rule. In the Edit the Rule Description section, make the necessary changes using the available drop lists.
Either type in the number or click on the red arrow toward the right to select a cell reference from your worksheet.
Click on the Format button to select your cell reference.
5 6 7
Stop If True check box

For backward compatibility, you can select the Stop If True check box in the Manage Rules dialog box to decide how conditional formatting might appear in earlier versions of Microsoft Office Excel that do not support more than three conditional formatting rules.
In the Format Cells dialog box, make use of the Number, Font, Border, and Fill tabs to customize the formatting according to your preference.
10
In the Conditional Formatting Rules Manager dialog box, select where you would like to see the formatting using the Show formatting rules for: drop list provided.
9 11
Click OK. Click OK.
STEP 7: Clear rules

Once the purpose of setting the rule has been served, you can clear the rule either on a selected cell range or on the entire worksheet. Heres how to do that:
38
Select the cell range if you want to clear the rule of a selected area or just leave all cells unselected.
3 4
Click on the Clear Rules option.
TIPs
Format cells with text

Select the Home tab and then click on the Conditional Formatting drop list from the Styles section. Click on New Rule. In the New Formatting Rule dialog box that opens up, click on Format only cells that contain from the Select a Rule Type: section. In the Edit the Rule Description: section, start by using the first drop list to select the Specific Text option. Then use the second drop list to select Containing. In the list box, type the text that you want to see formatted, for example, you can type Health. Click on the Format button to select the formatting of your choice. Click OK.
1 2 3 4 5 6 7 8
Go to the Home tab and then click on the Conditional Formatting drop list from the Styles section. You will see that all the formatting has been cleared in the cell range that you have selected.
2 5
Select the Clear Rules from Selected Cells or the Clear Rules from Entire Sheet depending on your preference.
STEP 8: Delete a rule

If the rule that has been created is no longer required to feature in your Excel worksheet, then heres an easy way to get it deleted:
Select the cell range if you want to clear the rule of a selected area or just leave all cells unselected.
Click on the Manage Rules Section.
In the Conditional Formatting Rules Manager dialog box that opens, use the arrow buttons to select the rule that you wish to delete.
Use the arrow buttons next to the Delete buttons to select which rule you want to delete.
More than one conditional formatting rule is true

For a range of cells, you can have more than one conditional formatting rule that evaluates to true. When the rules dont conflict between the two formats, both rules are applied. When the rules conflict, the rule that is applied is the one that is higher in precedence.
Click on the Delete Rule button.
6
Click OK .
39
Personalize your workbooks

Microsoft Excel 2007 is power-packed with a bunch of cool features that are bound to add spice to your documents. Read on to find out how you can customize these great enhancements to make your workbooks look like they have been created just for you!
References
What is the Microsoft Office Button?

The Microsoft Office button allows the user to access the most commonly used functionalities of the Office application. It is located at the top-left corner of the screen and is easily identified because of the Microsoft Office logo that appears on it.
STEP 1: Customize the Quick Access toolbar

How Excel is used depends on the personal working style of each user. Which is why Excel 2007 comes equipped with a Quick Access toolbar that is displayed on the upper left corner of the workbook. The Quick access toolbar is an easy way to access frequently used commands. Heres how you can customize this toolbar to suit your working style:
Click on the Microsoft Office button.
Change the Grid color
1 2 3 4
Click on the Microsoft Office button. Click on the Excel Options button at the bottom-right corner of the dialog box. In the Excel Options dialog box that opens up, select the option Advanced. Go to the Display options for this worksheet: section and change the Gridline color using the drop arrow according to your preference. 5Click OK.
Click on the Excel Options button at the bottom right corner of the dialog box. In the Excel Options dialog box that opens up, select the Customize option.
2 6 7 8
3 4
Click on the Add button to enter it in the Quick Access toolbar list.
Use the Choose commands from: drop list to select the section from which you would like to select the Quick Access tools.
Use the Customize Quick Access Toolbar to select the workbook to revise the selection of tools. Click OK.
In the displayed list, select the tool of your choice.
Click the Reset button for the default quick access toolbar tools displayed if the new selection is not suitable.
40
STEP 2: Customize the document theme

Besides picking from a collection of new themes, Excel 2007 allows you to customize colors, fonts and effects to create customized document themes of your own. Heres how:
In the worksheet that contains your existing formatted data, click on the Page Layout tab.
TIPs
Change the display color

Bored of the standard appearance of your Excel workbooks? Try using the choice of color scheme available to spruce your documents. Click on the Microsoft Office button. Click on the Excel Options button at the bottom right corner of the dialog box. In the Excel Options dialog box that opens up, select the option Popular. In the Top options for working with Excel, select the color scheme using the drop list. Click OK.
In the Themes group, click on the Colors drop-list. In the Create New Theme Colors dialog box, click on the button of the theme color element that you want to change.
2 4 5 6
In the Built-in section, click on Create New Theme Colors. Under Sample, you can see the effect of the changes that you make. To see how the colors that you select affect styles that you have applied in your document, click Preview.
1 2 3
4 5
Under the Theme Colors section, select the colors that you want to use.
In the Name field, type an appropriate name for the new theme colors.
Customize the status bar

To return all the theme color elements to their original status, click Reset before you click Save. A custom document theme is saved in the Document Themes folder and is automatically added to the list of custom themes.
Click on Save.
STEP 3: Create custom views

Try custom views to change the view on your worksheet without interrupting with the progress of your work. To create a custom view:
Go to View | Custom Views.
In the Add View dialog box, enter a name for the view.
Click OK.
Unlike in previous versions, Excel 2007 now comes with an improvised version of the status bar. In Excel 2007, you can now show up to 6 different summary points of data on the status barsum, count, average, numerical count, minimum, and maximum. To customize the status bar: Right-click on the status bar. Select the summary item(s) that you wish to show on the status bar. Once you select a range of numbers, the data will display itself on the main screen of Excel 2007.
1 2 3
41
TIPs
Use Themes
In Microsoft Excel 2007, using a document theme is really simple. All you have to do is start by changing the colors, the fonts, or the line and fill effects that are used. Changes that you make to one or more of these theme components immediately affect the styles that you have applied to the active document. If you want to apply these changes to new documents, you can save them as custom document themes. In the worksheet that contains your existing formatted data, click on the Page Layout tab. Click on the Themes group. In the Built-in section, select the theme of your choice. Similarly use the drop lists to select theme color fonts and effects for your layout. You can save the theme by clicking on the Save current theme in the Built-in section in the Themes group. In the File Name box, type an appropriate name for the theme.
Repeat these steps for each custom view you want to present. To quickly go to a view, click on the Custom View button and in the custom views dialog box, select the view of your choice, and then click on the Show button from the list.
STEP 4: Define new table styles

Just as you define themes and apply them to entire workbooks, you can also define and apply table styles. To apply styles:
1 4
Select the table, and then click on the Home tab.
Go to the Styles group.
Click on the Format as Table option.
1 2 3 4 5
In the Format as Table gallery, click on New Table Style to display the New Table Quick Style dialog box.
5 6
In the Name field, type a name for the new style.
In the Table Elements section, select the first table element you want to format. For example, select the option First Column Stripe from the Table Elements section
If you want to make your new style the default style for any new tables created in the current workbook, select the Set as default table quick style for this document check box.
When you click OK, Office Excel 2007 saves your custom table style.
Click OK.
If you need to Format the cells click on the Format button. In the Format Cells dialog box define the elements formatting with respect to font, border and fill format.
When the New Table Quick Style dialog box reappears, its Preview pane displays the overall table style and the Element Formatting section displays the selected elements appearance. Also, in the Table Element list, Office Excel 2007 displays the elements name in bold to indicate that it has been changed.
10
42
Click OK.
Impress your boss with 3D image charts

Survey respondents can often come up with the most offbeat answers to your questions. Check boxes could perhaps solve this problem, but they seem boring and outdated. Try using Excels drop lists to add more appeal to your survey forms
References
STEP 1: Create a chart

The first step in creating a good presentation is to collect all the basic data and formulate it into a table. For the presentation, you would need to produce a chart that will reflect the data that you have included in your table. Follow these steps to do this:
WHY USE CHARTS?

Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than analyzing several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods or how the actual sales compare to the projected sales.
In the Chart Wizard-Step 1 of 4-Chart Type dialog box, select the 3-D chart of your choice. For example, click on the Custom Types section and select the 3 D B&W column from the Chart type section.
1 2
Go to Insert | Chart.
Select the Builtin radio button from the Select from: section.
4
In the Chart WizardStep 2 of 4-Chart Type dialog box, click on the red arrow to select the range of cells that will appear in the Data range field.
After you have decided on which chart type best suits the type of presentation you intend to make, click on Next. Select the radio button next to Rows if you want to see the series data in rows or select the radio button Columns if you want to see the series data in columns.
WHAT IS THE CHART WIZARD?
The Chart Wizard is an inbuilt feature that Microsoft Excel comes endowed with to help you create a visual of your data in the form of a chart in 4 easy steps.
7
Click Next.
43
TIPs
USE A BORDER
Adding pictures on the bars of your chart is one fantastic idea to add power to your presentations. You could also format the chart further by using borders. To add borders: Click on the first bar of your chart in order to select it. Go to Format | Selected data Point. In the Format Data Point dialog box, click on the Patterns tab. In the Border section, select the Custom radio button. Use the Style, Color, and Weight drop lists to select a border of your preference. In the Sample selection, you can try various options to see what the final border will look like. Click OK.
1 2 3 4 5 6 7
In the Chart Wizard-Step 3 of 4Chart Type dialog box, click on the Titles tab, enter the names of the Chart Title, Category (X) axis, and Value (Z) axis. For example, the Chart title could be CAR LOANS 2006-07, the Category (X) axis could be Locations, and Value (Z) axis could be Subscribers.
9 10
In the Chart Wizard-Step 4 of 4- Chart Type dialog box, select the radio button As new sheet if you want the chart to appear in a new sheet or As object in if you want the chart to appear in a sheet that already exists in your workbook.
Click on Next.
11
Click Finish.
STEP 2: Use stacked images for bars

Perhaps the most exciting effect you can create in 3-D Excel charts is to replace the usual bars or columns with pictographs or images stacked to the appropriate height. The best part is that its really simple to do:
Click on the bars of your chart in order to select them.
Right-click and select Format Data Series.
CREATE A SHADOW EFFECT

Just as you add a border to the bars in your chart; it is also possible to create a shadow effect to enhance the final look. Click on the first bar of your chart to select it. Go to Format | Selected data Point. In the Format Data Point dialog box, click on the Patterns tab. Insert a check mark in the Shadows option and Click OK.
1 2 3 4
In the Format Data Series, click on the Patterns tab.
In the Area section, click on the Fill Effects button.
44
In the Fill Effects dialog box, click on the Picture tab.
Click on Select Picture.
TIPs
FORMAT THE CHART FLOOR

Just as you format the bars of your chart, you can also format the floor. In order to do this: Right-click on the floor of the chart and select Format floor. In the Format Floor dialog box, click on the Fill Effects button. Click on the Pattern tab. Click on the pattern of your choice from the Pattern section. Use the drop lists to select the Foreground and Background of your choice. Click OK.
In the Select Picture dialog box, use the Look in drop list to select the folder where you have stored the image you would like to insert into your chart.
Click on the Insert button.
1 2 3 4 5 6
In the Fill Effects dialog box, select the Stack radio button from the Format section.
11
Click OK.
ROTATE THE CHART

The 3-D view dialog box enables you to change the 3-D settings of your chart to rotate the bars of your chart to get a different view. To rotate the Excel chart: Go to Chart | 3 D View. In the 3-D View dialog box, click on the Up or Down arrow or enter the angle of Elevation and Rotation that you prefer. When the Right-Angle Axes box is checked, the option to set perspective is not visible. This overrides the perspective effect and forces the X and Y axes to meet at a right angle. Auto Scaling overrides the Height % of the Base setting. When applied, Excel adjusts the height and width ratio.
10
In the Apply to section, check mark the options available depending on the area in which you would like to see the stacked images. For example, keep the End checkbox unchecked if you do not want to see an image on the visible end of the bar in the graph.
12
In the Format Data Series dialog box, click OK.
1 2
STEP 3: Customize the chart backdrop

Once you have created the chart based on your data table, insert a suitable texture for the walls of your chart to bring out the theme of your presentation effectively. In order to do this:
Right-click between the grid lines of the walls of the chart and select the Format Walls option.
In the Format Walls dialog box, click on the Fill Effects button.
45
TIPs
FORMAT DATA TABLE

If you dont like the way the data table appears on your chart, you can format that too! All you have to do is: Right-click on the data table area and select the Format data table option. In the Format Data Table dialog box that opens up, select the Font, Font Style, Size, Color, and Background of your choice. Click OK.
In the Fill Effects dialog box, click on the Gradient tab.
1 2 3
Under the Colors section, select the preferred color scheme of your choice. For example, select the Two colors radio button to manually select a combination of two colors for your backdrop by using the drop lists for Color 1 and Color 2 or select the Preset button to select color schemes that have already been preset.
Under the shading style section, select the way you would like the color scheme to show up on the backdrop. For example, the radio button Horizontal can be selected if you need to apply the color scheme you have selected in a horizontal fashion.
Under the Variants section, decide on which variant of the color scheme you prefer by clicking on it so that it appears in the Sample section.
Click OK.
STEP 4: Insert the chart in PowerPoint

It is simple to integrate resources created in one Microsoft Office program into another. To incorporate a chart created in Excel into your PowerPoint presentation:
In your PowerPoint presentation, go to Format | Slide Layout and select the Slide Layout of your choice.
DISPLAY EXCEL SHEET AS AN ICON IN PPT

Insert a check mark in the Display as Icon check box, if you want to insert the contents of the file as an object into your presentation. This way you can activate it using the application that created it by viewing it as an icon.
2 6
Go to Insert | Object.
In the Insert Object dialog box, select the Create from File radio button, and click on the Browse button.
In the Browse dialog box, use the Look in dialog box to look for the Excel file you want to use.
Click OK.
Double-click on your chart to go to the original Excel sheet to make any changes.
Note: Simple Copy and Paste functions can also be used to insert an existing Excel chart on to a PowerPoint slide. The Excel chart will appear on your PowerPoint slide. Right-click on the chart, and use popup menus to modify chart characteristics.
46
Compare your skills!

Charts are a great way to represent your data to check for individual trends. But what if you need to compare your performance with others and not just have your data represented in a pattern? Try using a Spider Chart to help you.
References
Why use a spider chart?

Spider charts are useful when you need to have an overall view of your data. They help take several aspects relating to one subject or item and plot them on multiple axes. Spider charts also help create such graphical views for individual entities all revolving around the same parameters, thereby offering a comparative view of your data in relation to others. For example, you can check your performance on a set of parameters and then plot that of your contenders and compare the two. This allows you to check where you are falling back as compared to your contenders. In a company, a spider chart is extremely useful to help employers decide which individuals or teams possess a certain skill sets and make better use of them for increased efficiency.
STEP 1: Prepare the data table

Consider that you want to gauge your overall performance in a test that checked various skills. You also want to compare the results with that of your competitors who also took the same test. The first step to creating a spider chart is to collect your raw data, that is, the data you would plot on the various axes. This would contain your own results of the test as well that of your contenders. After collecting the information, open Excel and enter the data. Heres how
Enter the various parameters as individual parameters in one column.
To understand your data better, assign various colors for different aspects. To do so, click on the Home tab and select a color from the Font section.
Use the next columns to list the test scores against each parameter . For example, consider that in this table, you occupy the position of John.
To round off the values of the scores for making cleaner charts, click on the Decrease Decimal button in the Number section.
STEP 2: Prepare the spider chart

After you have entered the data in a tabular format, you can then proceed to build your spider chart. The spider chart is created from the radar chart that is found in the chart options in MS Excel. To access this option and use it to represent your data graphically, follow these simple steps:
Click on the Insert tab.
Select the parameters and their values and then go to the Charts section, and click on the Other Charts option.
Select the first radar chart that displays values relative to a center point where the categories are not directly comparable.
48
Then click on Style 2 to select the color code that will help you easily differentiate between the different subject on the chart.
TIPs
What is a spider chart?

A spider chart is a radar chart that allows a graphical display of the differences between your performance and that of others as well as differences in individual performances based on a set of parameters. It is extremely useful for both individuals and businesses to help identify their strengths and weaknesses. These charts are also known as star charts and are usually represented in a two-dimensional chart of 3 or more parameters represented on axes starting from a common point.
Right-click on the Legend on the chart and select the Select Data option. In the Select Data Source dialog box, click on the Series 1 option.
7
In the Edit Series dialog box that opens up, enter the name John in the Series name: field.
Click on the Edit button.
Radar chart with markers

If you need to use a radar chart that distinctly notes the exact location of the score value on the chart, then try using the radar chart with markers. Heres how you can change the current chart into a radar chart with markers. Click on the Insert tab. In the Other Charts section, click on the second option under Radar charts. Click OK. On the other hand, you can simply right-click and select the Change chart type option to select the radar chart with markers.
9 10
Similarly edit Series 2, Series 3, and Series 4 to edit the other names of the Legend entries.
Click OK.
1 2
11
Once you have edited the other legend entries, click OK.
3 4
49
TIPs
Create a 3D radar chart

In order to create a 3-D radar chart, you will need to follow these simple steps. Click on the Insert tab. In the Other Charts section, click on the third option under Radar charts. Click OK. Then right-click on the chart and click on the Format Data Series option. In the Format Data Series dialog box, select the 3-D Format option from the left pane. In the 3-D format section, select Bevel, Depth, Contour, or Surface according to your preferences. Click on the Close button.
STEP 3: Evaluate your performance

To evaluate your performance in a radar chart, it is important to understand your position in the radar chart. Refer to the legend to understand the color codes in the chart. In the radar chart, a point closer to the center on any axis will indicate a low value. A point that finds itself closer to the edge is considered to be a higher value. In this manner, you can check each axis to rate your own performance as compared to others.
Use the tips in this workshop to enlarge and change the chart type to show markers. Check the areas in which you (in our case, John) has scored as opposed to your contenders. Look at the blue markers to read your score.
1 2 3 4 5 6 7
1 2
John has a high rating in the areas of multitasking and product knowledge whereas you would need to work on your leadership skills. You may want to learn these from Michael who has a good lead over the rest in this area.
STEP 4: Save the chart as a template

If you want to save the settings to the chart you just created for further use, simply save it as a template. By doing so, you will save yourself the effort of going through the entire process again. Heres how to save a chart as a template
In the Type group section, click on the Save as Template option.
1 2 5
Click on the Design tab.
In the Save Chart Template dialog box that opens, type a name for the template. and Click Save. In the Change Chart Type dialog box, click on the Templates option in the left pane.
Click on the Insert tab.
Move the chart to another location

If you have a chart created in one worksheet and would like to move it to another worksheet in the same workbook, this is how you would do it: Click on the Design tab. In the Location section, click on the Move Chart option. In the Move Chart dialog box that appears, select the radio button Object in: and click on the sheet you want to see the chart in, for example, Sheet 2. Click OK.
6 7 9
Click on Other Charts.
1 2 3 4
Select the All Chart types option. You will then be navigated to the My Templates section, where you can access your template whenever required.
You can also access the template by clicking on the Design tab and then in the Type group section by clicking on the Change Chart Type button. A chart template contains the same formatting like the chart saved as a template. To use the document theme colors instead, right-click on the chart area and click Reset to Match Style option on the shortcut menu.
50
Essential tips for MS Excel

Printing Grids Excel by default does not print the gridlines (the cell markers). This is because by default the cells in Excel do not have a colored border. To enable gridlines while setting up your document for print, perform the following steps: 1. From the File menu select the Page Setup option. 2. Click the Sheet tab on the dialog box. 3. Under the Print section, check on Gridlines. 4. Click OK. 5. To verify whether the gridlines appear properly, use the File | Print Preview command. Using Borders for Grids Using the gridlines option to print may not be such a good idea if you do not wish to print all the gridlines on the page to be printed. You can instead as a solid border to the cells as an alternative. To add a border to a set of cells: 1. Select the required cells. 2. Click the drop-arrow near the Border button on the Formatting toolbar to choose from a list of border styles. 3. Select the Outside borders or Top and Bottom borders style. 4. The border style will be applied on the selected range. 5. Repeat these steps for other cell ranges that are to be formatted. 6. Finally save the file. 7. Before printing the worksheet you can preview it by using the File | Print Preview command. Then click the Close button. Auto Fill a series Excel offers a time-saving feature called the fill handle. With the fill handle, you can fill data series like consecutive numbers and dates, numbers or dates at specific intervals, numbers with specific patterns, alpha numeric data in specific patterns, month, and days of the calendar etc To type numbers with specific patterns: 1. Type the first two numbers in consecutive cells (odd, even, consecutive or pattern of numbers to be followed). 2. Select both the cells. 3. Place the mouse pointer to the extreme bottom-right corner of the selection the this cross will change to a thin plus + sign 4. Now left click and drag the fill handle down or across to as many cells as required to extend the series, to cpmlete your series.
Next month in CHIP Plus

Learn to organise your email - Managing your mails efficiently - Give your e-mails a personal touch - Making distribution lists - Ward off spam with junk filters - Schedule meetings in MS Outlook

02 08 Chip Plus Handson With Ms Excel PDF

Uploaded by

Copyright:

Available Formats

02 08 Chip Plus Handson With Ms Excel PDF

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

02 08 Chip Plus Handson With Ms Excel PDF

Uploaded by

Copyright:

Available Formats

February 2008 | Free with CHIP, not to be sold seperately

Also available in Bangladesh, Nepal, Bhutan, Sri Lanka, UAE

BUSINESS HEAD & DGM Circulation

FeatureS EDITOR WRITERS

Kamakshi Venugopal Vijay George, Gareth Mankoo, Dolwin Fernandes

Photo Editor HEAD OFFICE

Brian Pereira editor@chip-india.com Jamshed Avari jamshed.avari@chip-india.com

HANDS ON WITH MS WORD EDITORIAL

Hands On with MS Excel

february 2008 intelligent computing CHip PLUS

WORKSHOP HanDs On WITH MS excel

Make your data more readable

How to use the Formatting Toolbar?

STEP 1: Formatting the font

Click on the Font tab .

What does data formatting mean?

HANDS ON WITH MS excel WORKSHOP

STEP 2: Changing the display of numbers

Provide column and row headings

Select the cells that contain the sales figures.

Click Format | Cells.

Click on the Number tab.

STEP 3: Merging cells

Select the cells that need to be merged.

Click on the Alignment tab.

Click Format | Cells.

february 2008 intelligent computing CHip PLUS

WORKSHOP HanDs On WITH MS excel

Split merged cells

STEP 4: Applying background color

Click on the Patterns tab.

STEP 5: Applying borders

intelligent computing CHip PLUS february 2008

HANDS ON WITH MS excel WORKSHOP

Select the cell/s to which you want to apply a border.

Click on the Border tab.

STEP 6: Use AutoFormat to apply a design scheme

Click Format | Autoformat.

february 2008 intelligent computing CHip PLUS

WORKSHOP HanDs On WITH MS excel

Make drop lists for your surveys

STEP 1: Rename your Sheets

WHAT IS DATA VALIDATION?

Open an Excel worksheet.

Rename sheet1 as Questions.

Select File | New

Right-click on the Sheet1 tab and click Rename.

Similarly, rename Sheet2 as Lists.

CHANGE THE COLOR OF ACTIVE CELLS

STEP 2: Creating the question list

Click on the Questions tab (earlier known as Sheet1).

intelligent computing CHip PLUS february 2008

HANDS ON WITH MS excel WORKSHOP

STEP 3: Creating the answer list

PASSWORD PROTECT YOUR SHEET

Insert these options in cells A1 to A6.

Type the list of options to a question that appears on Sheet1.

Highlight the space A1 to A6 (also known as range A1:A6).