This post contains Electronic Spreadsheet Class 10 Notes including Goal Seek and Scenario, Link Data and Spreadsheets, Share and review a spreadsheet, Create and Use Macros. All the topics are well organized and prepared by experts in easy to read and understandable language, so that students can get benefited more and secure high marks.
SESSION 1: Analyze data using Scenario and Goal Seek
Consolidating Data
- Consolidating data refers to Collecting and aggregating data from different worksheets or workbooks into a master worksheet.
- Data updation becomes very easy by consolidating data into a single worksheet
How to consolidate data in spreadsheet?
- Open all worksheets that contains the cell ranges to be consolidated
- Select Consolidate option from Data menu in the worksheet where data to be aggregated.
- Under ‘source data range’ label, click in field to the right and select source data range and press enter.
- Click Add. The selected data range will be added in Consolidate ranges box.
- Repeat step 3 and 4 to add other source data ranges to be consolidated.
- After adding source data, Under the ‘Copy result to’ label click in field to the right and select target data range and press enter.
- Select a function from ‘function’ list to specify how data will be consolidated.
- Under ‘Consolidate by’ label, check ‘row label’ and ‘link to source data’. You can also check ‘column label’ if required.
- Click OK
Subtotal
- The spreadsheet SUBTOTAL function is used to create group of data and perform various aggregate functions such as sum, average, count, product, max etc. and helps in analyzing data provided.
- It can include or exclude rows hidden by a filter or manually in given data range during calculation.
- It can be applied to max of three groups of data to perform calculation.
How to create subtotals?
- Before performing subtotal make sure columns have labels.
- Select the range of cells that you want to calculate subtotal for.
- Select ‘Subtotal’ option from ‘Data’ menu
- In the subtotal dialogbox, select the column that you want to add subtotal to from ‘calculate subtotal for’.
- Select the function (sum, count, average etc.) from ‘Use function’ list you want to calculate the subtotal.
- Click OK