Subscribe Us

Enter your email address:

Loading

Outline (group) data in a worksheet

Labels: , ,

If you have a list of data that you want to group and summarize, you can create an outline of up to eight levels, one for each group. Each inner level, represented by a higher number in the outline symbols displays detail data for the preceding outer level, represented by a lower number in the outline symbols. Use an outline to quickly display summary rows or columns, or to reveal the detail data for each group. You can create an outline of rows (as shown in the example below), an outline of columns, or an outline of both rows and columns.

Outlined list
An outlined row of sales data grouped by geographical regions and months with several summary and detail rows displayed.
Callout 1  To display rows for a level, click the appropriate one two three outline symbols.
Callout 2  Level 1 contains the total sales for all detail rows.
Callout 3  Level 2 contains total sales for each month in each region.
Callout 4  Level 3 contains detail rows (only detail rows 11 through 13 are currently visible).
Callout 5  To expand or collapse data in your outline, click the plus and minus outline symbols.

Create an outline of rows

  1. Make sure that each column has a label in the first row, contains similar facts in each column, and that the range has no blank rows or columns.
  2. Select a cell in the range.
  3. Sort the columns that form the groups.
  1. Insert summary rows.
To outline data by rows, you must have summary rows that contain formulas that reference cells in each of the detail rows for that group. Do one of the following:
Insert summary rows by using the Subtotal command    
  • Use the Subtotal command, which inserts the SUBTOTAL function immediately below or above each group of detail rows and automatically creates the outline for you.
Insert your own summary rows    
  • Insert your own summary rows with formulas immediately below or above each group of detail rows.
  1. Specify whether the location of the summary row is below or above the detail rows.
  1. On the Data tab, in the Outline group, click the Outline Dialog Box Launcher.
Outlook Ribbon Image
  1. To specify a summary row above the details row, clear the Summary rows below detail check box. To specify a summary row below the details row, select the Summary rows below detail check box.
  1. Outline the data. Do one of the following:
  1. If necessary, select a cell in the range.
  2. On the Data tab, in the Outline group, click the arrow next to Group, and then click Auto Outline.
Outlook Ribbon Image
 Important    When you manually group outline levels, it's best to have all data displayed to avoid grouping the rows incorrectly.
  1. Outline the outer group.
  1. Select all of the subordinate summary rows, as well as their related detail data.
In the example below, row 6 contains the subtotals for rows 2 through 5, and row 10 contains the subtotals for rows 7 through 9, and row 11 contains the grand totals. To group all of the detail data for row 11, select rows 2 through 10.
A B C
1 Region Month Sales
2 East March $9,647
3 East March $4,101
4 East March $7,115
5 East March $2,957
6 East Mar Total $23,820
7 East April $4,257
8 East April $1,829
9 East April $6,550
10 East Apr Total $12,636
11 East Total $36,456
 Important   Do not include the summary row 11 in the selection.
  1. On the Data tab, in the Outline group, click Group.
Outlook Ribbon Image
The outline symbols appear beside the group on the screen.
  1. Optionally, outline an inner, nested group.
  1. For each inner, nested group, select the detail rows adjacent to the row that contains the summary row.
In the example below, to group rows 2 through 5, which has a summary row 6, select rows 2 through 5. To group rows 7 through 9, which has a summary row 10, select rows 7 through 9.
A B C
1 Region Month Sales
2 East March $9,647
3 East March $4,101
4 East March $7,115
5 East March $2,957
6 East Mar Total $23,820
7 East April $4,257
8 East April $1,829
9 East April $6,550
10 East Apr Total $12,636
11 East Total $36,456
 Important   Do not include the summary row for that group in the selection.
  1. On the Data tab, in the Outline group, click Group.
Outlook Ribbon Image
The outline symbols appear beside the group on the screen.
  1. Continue selecting and grouping inner rows until you have created all of the levels that you want in the outline.
  2. If you want to ungroup rows, select the rows, and then on the Data tab, in the Outline group, click Ungroup.
 Note   You can also ungroup sections of the outline without removing the entire outline. Hold down SHIFT while you click the Plus box or Minus box for the group, and then on the Data tab, in the Outline group, click Ungroup.
 Important   If you ungroup an outline while the detail data is hidden, the detail rows may remain hidden. To display the data, drag across the visible row numbers adjacent to the hidden rows. On the Home tab, in the Cells group, click Format, point to Hide & UnHide, and then click Unhide Rows.

'



Split names by using Convert Text to Columns

Labels: ,

The Convert Text to Columns Wizard is an easy way to separate simple cell content, such as first names and last names, into different columns.
Full name       First name   Last name  
Syed Abbas       Syed Abbas
Barbara Decker       Barbara Decker
Lisa Jacobson       Lisa Jacobson
Diane Margheim       Diane Margheim
Depending on your data, you can split the cell content based on a delimiter, such as a space or comma, or based on a specific column break location within your data.

Split content based on a delimiter

This method works best if your names have a similar format, such as "First_name Last_name" (where the space is the delimiter) or "Last_name, First_name" (where the comma is the delimiter).
Split "First_name Last_name" content
To complete these steps, copy the following sample data to a blank worksheet.
How?
  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
  1. Press CTRL+C.
  2. On the worksheet, select cell A1 and then press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1
2
3
4
A
Syed Abbas
Barbara Decker
Lisa Jacobson
Diane Margheim
  1. Select the range of data that you want to convert.
  2. On the Data menu, click Text to Columns.
  3. In Step 1 of the Convert Text to Columns Wizard, click Delimited and then click Next.
  4. In Step 2, select the Space check box and then clear the other check boxes in the Delimiters section.
The Data preview window shows the first and last names in two separate lists.
Convert Text to Columns Wizard Step 2 of 3
 Note   If your list contains middle names, the full name will be distributed into three separate columns: first, middle, and last.
  1. Click Next.
  2. In Step 3, click a column in the Data preview window, and then click Text under Column data format.
Repeat this step for each column in the Data preview window.
  1. If you want to show the separated content in the columns next to the full name, click the icon at the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).
Destination of splitted contents
 Important   If you do not specify a new destination for the new columns, the divided data will replace the combined data.
  1. Click the icon at the right of the Convert Text to Columns Wizard.
Text to Columns Wizard Step 3 of 3
  1. Click Finish.
Split "Last_name, First_name" content
To complete these steps, copy the following sample data to a blank worksheet.
HideHow?
  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
  1. Press CTRL+C.
  2. On the worksheet, select cell A1 and then press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
1
2
3
4
A
Abercrombie, Kim
Cavaglieri, Giorgio
Ito, Shu
Philips, Carol
  1. Select the range of data that you want to convert.
  2. On the Data menu, click Text to Columns.
  3. In Step 1 of the Convert Text to Columns Wizard, click Delimited and then click Next.
  4. In Step 2, select the Comma check box and then clear the other check boxes in the Delimiters section.
The Data preview window shows the first names and last names in two separate lists.
Convert Text to Columns Wizard Step 2 of 3
 Note   If your list contains middle names, the full name will be distributed into three separate columns: first, middle, and last.
  1. Click Next.
  2. In Step 3, click a column in the Data preview window, and then click Text under Column data format.
Repeat this step for each column in the Data preview window.
  1. If you want to show the separated content in the columns next to the full name, click the icon at the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).
Destination of splitted contents
 Important   If you do not specify a new destination for the new columns, the divided data will replace the combined data.
  1. Click the icon at the right of the Convert Text to Columns Wizard.
Convert Text to Columns Wizard Step 3 of 3
  1. Click Finish.

Split cell content based on a column break

You can also customize how you want your data to be separated by specifying a fixed column break location in the cell.
  1. Select the cell or range of cells, and then click Text to Columns on the Data menu.
  2. In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next.
  3. In the Data preview window, drag a line to indicate where you want the content to be divided.
Text to Columns Wizard Step 2 of 3 using fixed data format
Tip    To delete a line, double-click it.
  1. Click Next.
  2. In Step 3, select a column in the Data preview window, and then click a format option under Column data format.
Repeat this step for each column in the Data preview window.
  1. If you want to show the divided content in the columns next to the full name, click the icon at the right of the Destination box, and then click the cell next to the first name in the list.
Choose destination of extracted cell content
 Important   If you do not specify a new destination for the new columns, the divided data will replace the combined data.
  1. Click the icon at the right of the Convert Text to Columns Wizard.
Convert Text to Columns Wizard Step 3 of 3
  1. Click Finish.
Reference: Microsoft Help

'

Working with Data Tables

Labels: ,

A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.

Overview

Data tables are part of a suite of commands that are called what-if analysis tools. When you use data tables, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts.

Kinds of what-if analysis    There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and determine possible results. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

Like scenarios, data tables help you explore a set of possible outcomes. Unlike scenarios, data tables show you all the outcomes in one table on one worksheet. Using data tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form.

A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

Data table basics

You can create one-variable or two-variable data tables, depending on the number of variables and formulas that you want to test.

One-variable data tables    Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row.

In the following illustration, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3.

Data table with one variable
A one-variable data table

Two-variable data tables    Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula. For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment.

In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

Data table with two variables
A two-variable data table

Data table calculations    Data tables are recalculated whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not the data tables.

'

Join Two sheet together in Excel

Labels: ,

Whether you are working with multiple Excel worksheets or even source data from different workbooks, it is possible to consolidate them into one master worksheet. If your worksheets have been created from a template and are identically structured, they can be combined by position. If they differ in structure but still use the same data labels, they can be joined by category. The Consolidation Tool in Excel allows you to perform these operations in a few simple steps.

 

Instructions

  1. Open each worksheet that you intend to consolidate. After opening up Excel, click on the "File" tab and select "Open." From the dialog box that appears, select the worksheet that contains your desired data and click "Open." Repeat this step until you have opened all of the necessary worksheets.  

  2. Create a new worksheet as a destination for your consolidated data. You can start a new workbook by clicking the "New" icon at the top left. Or, add a new worksheet to your current workbook by clicking the "Insert" tab on the toolbar and selecting "Worksheet." In the new worksheet, click inside the cell to which you would like to transfer your consolidated data. Be sure to leave enough room below and to the right of the destination cell for the transferred data.

  3. Select the data you wish to consolidate. Click on the "Data" tab on the tool bar. From the choices given, select "Consolidate." The Consolidation Tool dialog box will open. The first option you have is to choose the function. Since you are consolidating data, you will be performing a "Sum" function, which is already selected. You may leave that as it is. In the Reference field, type out the range of the first source area. If you are selecting data from different worksheets in the same workbook, include only sheet and cell references. If your data is from different workbooks, include a book reference as well. You can also select the area by clicking and dragging over the source area with your mouse. Excel will fill in the proper range description for you. When the source area range is entered in the Reference field, click the "Add" button to the right. Notice your range has been added to the "All references" box below. Repeat this step until all data sources have been added.

  4. Consolidate the data. By clicking "Create links to source data," your master worksheet will automatically update whenever changes are made to the original sources. If your source data has labels in either the top row or left column, select those options accordingly by checking the appropriate boxes under "Use labels in." When finished, click "OK." Your master worksheet will be created.





References

'

What-If Analysis in Excel

Labels: ,

By using what-if analysis tools in Microsoft Office Excel, you can use several different sets of values in one or more formulas to explore all the various results.

For example, you can do what-if analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs.

Overview

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.

Three kinds of what-if analysis tools come with Excel: scenarios, data tables, and Goal Seek. Scenarios and data tables take sets of input values and determine possible results. A data table works only with one or two variables, but it can accept many different values for those variables. A scenario can have multiple variables, but it can accommodate only up to 32 values. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

In addition to these three tools, you can install add-ins that help you perform what-if analysis, such as the Solver add-in.

The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel. For more advanced models, you can use the Analysis Pack add-in.

Use scenarios to consider many different variables

A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

For example, suppose you have two budget scenarios: a worst case and a best case. You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them. For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values.

Worst case scenario
Worst case scenario
Callout 1 Changing cells
Callout 2 Result cell

Best case scenario
Best case scenario
Callout 1 Changing cells
Callout 2 Result cell


If several people have specific information in separate workbooks that you want to use in scenarios, you can collect those workbooks and merge their scenarios.
After you have created or gathered all the scenarios that you need, you can create a scenario summary report that incorporates information from those scenarios. A scenario report displays all the scenario information in one table on a new worksheet.

Scenario summary report
Excel Scenario Summary report

 Note   Scenario reports are not automatically recalculated. If you change the values of a scenario, those changes will not show up in an existing summary report. Instead, you must create a new summary report.

'

Create a summary report in Excel with data consolidation

Labels: ,

Consolidating Excel worksheets can be a very valuable tool, especially in the corporate world. If you currently have multiple Excel spreadsheets with data that you need to add into one document then all you need to do is consolidate instead of copying the data yourself.

If you have two or more Microsoft Excel worksheets that are identicalto each other (except the values are different), you can have Excel's DataConsolidate feature consolidate the worksheets into a summary report.

For example, suppose you have a workbook that consists of two worksheets. One worksheet has your students' names in A1:A20 and theircorresponding midterm grades in B1:B20. The second worksheet lists thestudents' names in column A and their final grades in column B.
To create a worksheet listing the students' average grade,follow these steps:
  1. Create a new worksheet and click A1.
  2. Go to Data | Consolidate.
  3. Select Average from the Function drop-down list.
  4. Click the Collapse dialog button.
  5. Select A1:B20 in Midterm Grades Sheet.
  6. Click the Collapse dialog button and click Add.
  7. Click the Collapse dialog button and Select A1:B20 in Final Grades Sheet.
  8. Click the Collapse dialog button and click Add.
  9. Under Use Labels In: select the Left Column check box. Click OK.
The students' average grades are now listed in the newworksheet.

'

Time Calculation in Excel

Labels:


Excel can work with time very easily. Time can be entered in various different formats and calculations performed. There are one or two oddities, but nothing which should put you off working with it. See the TimeSheet example for an example.

Typing time

When time is entered into worksheet it should be entered with a colon between the hour and the minutes, such as 12:30, rather than 12.30.

01:30
12:30
20:15
22:45

Excel can cope with either the 24hour system or the am/pm system. To use the am/pm system you must enter the am or pm after the time. You must leave a space between the number and the text.

1:30 AM
1:30 PM
10:15 AM
10:15 PM

Finding the difference between two times

You can subtract two time values to find the length of time between.
Start
End
Duration

01:30
02:30
01:00
 =D24-C24
08:00
17:00
09:00
 =D25-C25
8:00 AM
5:00 PM
9:00 AM

If the result is not shown correctly, You may need to reformat the answer. Look at the section about formatting further in this worksheet.

Adding time

You can add time to find a total time. This works well until the total time goes above 24 hours. For totals greater than 24 hours you may need to apply some special formatting.

Start
End
Duration

01:30
02:30
01:00

08:00
17:00
09:00

7:30 AM
5:45 PM
10:15



20:15
=SUM(E37:E39)
`

Formatting time

When time is added together the result may go beyond 24 hours. Usually this gives an incorrect result, as in the example below. To correct this error, the result needs to be formatted with a Custom format.

Example 1 : Incorrect formatting
Start
End
Duration

07:00
18:30
11:30

08:00
17:00
09:00

07:30
17:45
10:15


Total
06:45
 =SUM(E49:E51)

Example 2 : Correct formatting
Start
End
Duration

07:00
18:30
11:30

08:00
17:00
09:00

07:30
17:45
10:15


Total
30:45
 =SUM(E56:E58)

How To Apply Custom Formatting

The custom format for time use a pair of square brackets [hh] on either side of the hours indicators.

  1. Click on the cell which needs the format.
  2. Choose the Format menu.
  3. Choose Cells.
  4. Click the Number tag at the top righ
  5. Choose Custom.
  6. Click inside the Type: box.
  7. Type [hh]:mm as the format.
  8. Click OK to confirm.



'

Split Forename and Surname with Excel

Labels:


The following formulas are useful when you have one cell containing text that needs to be split up.
One of the most common examples of this is when a persons Forename and Surname are entered in full into a cell.

The formulas use various text functions to accomplish the tasks. Each of the techniques uses the space between the names to identify where to split.

Finding the First Name

Full Name
First Name


Alan Jones
Alan
 =LEFT(C14,FIND(" ",C14,1))
Bob Smith
Bob
 =LEFT(C15,FIND(" ",C15,1))
Carol Williams
Carol
 =LEFT(C16,FIND(" ",C16,1))

Finding the Last Name

Full Name
Last Name

Alan Jones
Jones
 =RIGHT(C22,LEN(C22)-FIND(" ",C22))
Bob Smith
Smith
 =RIGHT(C23,LEN(C23)-FIND(" ",C23))
Carol Williams
Williams
 =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names. If there is also a middle name, the last name formula will be incorrect. To solve the problem you have to use a much longer calculation.

Full Name
Last Name
Alan David Jones
Jones
Bob John Smith
Smith
Carol Susan Williams
Williams
=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name


Full Name
Middle Name
Alan David Jones
David
Bob John Smith
John
Carol Susan Williams
Susan
=LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

'