Video: What's New in Lotus Symphony Spreadsheets 3 

---

Welcome to IBM® Lotus® Symphony^TM 3 Spreadsheets. This video highlights and demonstrates the features available in this release, including:

• Sheet-specific zoom
• The Detective, which is useful when your spreadsheet contains many formulas
• The ability to solve equations using a variable
• The Solver, which let's you solve equations that have multiple variables
• Scenarios that let you create different versions of a range of cells
• A new chart wizard
• Flexible data labels
• DataPilot function
  

Welcome

To read a text version of this video, scroll down to the Welcome section in this wiki article.

To watch the video, click the play button.



Watch the demonstration full screen in a new window

Table 1. Welcome

Step

Caption

1.1

Welcome to IBM Lotus Symphony 3 Spreadsheets. This video highlights and demonstrates the features available in this release, including: Sheet-specific zoom The Detective, which is useful when your spreadsheet contains many formulas The ability to solve equations using a variable The Solver, which let's you solve equations that have multiple variables Scenarios that let you create different versions of a range of cells A new chart wizard Flexible data labels DataPilot function © Copyright IBM Corporation 2010

Sheet-specific zoom

Table 2. Sheet-specific zoom

You can reduce or enlarge the display of the spreadsheet by setting specific zoom options on the toolbar.

Step	Caption
2.1	Let's set the zoom options by clicking the arrow on the Zoom button on the editing toolbar.
2.2	We'll try out some of the zoom options that are available, like Optimal which resizes the display to fit the width of the selected cells, Page Width for the complete width of the spreadsheet page, and Entire Page.
2.3	You can also directly type your own size in the zoom field. Let's try 60%. The spreadsheet is now displayed at sixty percent of its actual size.

The Detective

Table 3. The Detective

With the Detective function, you can detect relationships between cells, such as tracing the data source for the current formula cell. This is helpful especially when your spreadsheet contains many formulas.

Step	Caption
3.1	In this payment summary spreadsheet, we want to know which cells have relationships with Linda's income.
3.2	We click Trace Precedents and a blue arrow shows which cells are data sources that are used together in a formula for E8.
3.3	We click Trace Dependents and a blue arrow and a blue frame show the cells that are used together in the formula for E13.
3.4	We can click Remove Precedents to remove precedents traces, click Remove Dependents to remove the dependents traces, or we can also click Remove All Traces.
3.5	When we click Trace errors, tracer arrows show all the precedent cells which cause an error in the selected cell.
3.6	Now, let's click Mark Invalid Data to see which cells that contain values outside our validation rules.
3.7	If we use the Fill Mode, when you click any cell you'll automatically see a trace to the precedent cell. We can exit fill mode by pressing Esc.

Solve equations

Table 4. Solve equations

The Solve equations function can help you to solve an equation with a variable.

Step	Caption
4.1	In this payment summary spreadsheet, we want to increase Linda's income to $3000 by increasing her bonus. Let's use the Solve Equations window to get the exact amount.
4.2	We enter 3000 in the Target Value field and select D8 as the variable cell.
4.3	We click OK to see our result.
4.4	When we click Yes, we insert the result into the variable cell.

The Solver

Table 5. The Solver

You can use the solver function to quickly solve equations that have multiple unknown variables.

Step	Caption
5.1	In this example we'll look at two of our products, T-shirts and Pants. The sheet lists the purchase price, transportation cost, saving cost, and sales price for each product. To maximize the profit, we want to calculate the most appropriate sales amount for each product. But there are some limitations, such as the minimum sales amount for each product, and the total savings and transportation costs cannot exceed the cost control per month.
5.2	Let's first have a look at the formulas used in the Profit predicting section.
5.3	The sales amount is the value that we need to calculate.
5.4	In the Profit row, B13 uses this formula to calculate the total profit.
5.5	In the Transportation cost row, B14 uses this formula to calculate the total transportation cost.
5.6	In the Saving cost row, B15 uses this formula: [shown in caption: =SUMPRODUCT(B5:C5;B12:C12)] to calculate the total saving cost.
5.7	Now let's use the solver to calculate the sales amount for maximum profit.
5.8	We'll start by clicking on the Profit cell and open the Solver window.
5.9	We select the sales amount cells for T-shirts and Pants, since we want to figure what to charge for these items.
5.10	Now we'll select the limiting conditions, such as transportation costs and include them in the formula.
5.11	Now that all limiting factors have been entered, we'll click Solve. The results are inserted into the spreadsheet. We'll click to keep the results.

Scenarios

Table 6. Scenarios

Scenarios let you create different versions of a range of cells. When you switch to different versions, the values in the cells are automatically replaced and you can review the values of other relevant cells.

Step	Caption
6.1	In the Payment summary spreadsheet, we want to adjust the payment for Amanda and there are two options we want to compare. We can use the scenario to check the impact to her total income and the overall payment expense of each option.
6.2	Select C12 and D12, and then click Scenarios to open the Create Scenario window.
6.3	We'll enter a name and specify settings for the scenario and click OK.
6.4	We'll use the same method to create a second scenario.
6.5	Now, we'll enter a base salary and bonus values for each scenario.
6.6	After we enter these values, when we select each scenario, we can see Amanda's income and the total expense values change automatically.

The new chart wizard

Table 7. The new chart wizard

In spreadsheets, a chart can help you to show data details more visually, and the new chart wizard allows you to create a chart quickly and easily.

Step	Caption
7.1	We want to create a chart to get an overview of the data. Let's click the Create Chart button on the toolbar.
7.2	We select a chart type we want to create.
7.3	Now we click Next and select a data range for the chart.
7.4	Let's customize the data ranges for individual data series. The Job title, Base salary, Bonus, and Income columns are taken as a data series by default. But we do not want to include the job title in the chart. So, let's select Series 1 and click Remove.
7.5	We'll define the range name for each data series.
7.6	We'll also define each chart elements with real names instead of X and Y.
7.7	When we click Finish a chart is created. Let's drag the chart to adjust its size.

Flexible data labels

Table 8. Flexible data labels

After you create a chart, you can define your chart labels. You can select a number format for data labels or select the placement of data labels relative to the objects.

Step	Caption
8.1	We start by selecting the chart and the Base Salary data series.
8.2	Now we right-click, and select Object Properties.
8.3	Let's specify data label settings on the Data Labels tab.
8.4	Click the Data Labels tab and select Show value as number and click Number format to open the Number Format window.
8.5	By default, Source format is selected. If you want to define other number formats, clear the Source format check box. In our example, we want to keep source format. So we'll just click OK.
8.6	After finishing with the settings, we'll click OK. We'll use the same method to define data labels for Bonus and Income.

DataPilot

Table 9. DataPilot

You can create a DataPilot table to organize and summarize the data from different points of view.

Step	Caption
9.1	Let's select the entire area of data, including the row and column labels, and click Data > DataPilot > Create.
9.2	When we click OK, a DataPilot table is created.
9.3	For more details about managing DataPilot tables, visit the spreadsheet tutorial at http://www.ibm.com/software/lotus/symphony/idcontents/tutorial/en/spreadsheets_tutorial/frameset.html.

Thank you

Step: 10.1

Caption: Thank you for watching this video. For more information visit the Lotus Symphony wiki.


IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at Copyright and trademark information at www.ibm.com/legal/copytrade.shtml.

Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.