How to Develop & Use Complex Spreadsheetsby John Landers
People working in a variety of fields often need to create complex spreadsheets to illustrate budgets and business proposals, or manage data and provide analysis. Developing complicated worksheets requires specific knowledge and skills for manipulating large quantities of data. Adding flow charts, querying data from external sources or creating PivotTables using Microsoft's Excel software not only makes creating and using complex spreadsheets easier, it also can make the information clear to others.
Create a Flow Chart
Click the "Insert" tab, then click the "Shapes "command. Find "Flowchart" on the menu to see common flow chart symbols. Click on the flow chart symbol and drag it to your spreadsheet. The "Drawing Tools" options will automatically become available.
Click the "Format" tab. Click "Shape Styles" to further define the look of your flow chart symbol. Excel will format the flow chart symbol.
Click on the flow chart symbol to type in your text. Click in the first line of the text and drag to the left to select the text.
Click the "Home" tab. Click the appropriate button to format, align, adjust the size or modify the color of your text. Excel applies the changes to your text.
Click and drag to select the symbol and rotate, adjust the size or move the flow chart symbol.
Query Data from a Website
Import data from a website into a spreadsheet. Excel allows you to filter the information and import only the data you need. The query also allows you to refresh data when required. Open the Excel worksheet and click on the "Data" tab, and "From Web." The New Web Query dialog box appears. Type in the Web address of the site that has the data you want to import to your worksheet. Click the "Go" button and the Web page will appear in the New Web Query dialog box.
Click on the arrow symbol next to the data you want to appear in your query. The arrow turns into a check. To deselect the data, click on the check.
Click the "Import" button. You will see the Import Data dialog box. Click where you want to import the information. Select "Existing worksheet" or "New worksheet."
Click the "OK" button. The data will appear within the Excel worksheet. Analyze or chart the data.
Add a PivotTable
Use PivotTables to answer questions about your data. The rows and columns of the PivotTable contain discrete information. This means the values fall under distinct categories, such as men and women, or sales and quarter. Click and drag your cursor across the information to select the data you want to include in the PivotTable. Make sure you include the row and column headings.
Click the "Insert" tab, click "PivotTable. The "Create PivotTable" dialogue box appears. Choose where you want to get the data. Select "Table/Range" and enter the location of the data, or select "Use an external source."
Choose where you want to place the data. Select "New Worksheet" or "Existing Worksheet" and type in the address of the data's location. Click the "OK" button, Excel opens the "PivotTable Field List" pane to the right. Check the fields you want to add to your report. Excel automatically makes the "PivotTable Tools" available for you to format your report.
Items you will need
- Microsoft Excel software
- Microsoft Office: Excel 2007 - Step-by Step: 2007
- data image by jeancliclac from Fotolia.com