How to Create a Consolidated Spreadsheet

1. Log on to your computer and open Microsoft Excel. Create a new spreadsheet and give it a descriptive name like 'Consolidated Budget' or 'Consolidated Spreadsheet.'
2. Add another sheet if you need to. Make sure you have enough sheets to hold the data you will be consolidating. For instance, if you want to use your spreadsheet to add up quarterly data, you would need a total of five sheets—one for each quarter and fifth sheet to hold the consolidated data.To add a sheet in Excel 2003, click on the 'Insert' menu and choose 'Worksheet' from the menu. To add a sheet in Excel 2007, click on the 'Insert Sheet' option.
3. Give each sheet a descriptive name. In Excel 2003, click on the 'Format' menu and choose 'Sheet' from the drop-down box. Choose the 'Rename' option and type a descriptive name. Repeat this process for each of the sheets. In Excel 2007, right-click on the worksheet tab at the bottom of the screen and select 'Rename' and type in the new name.
4. Enter your data into each of the sheets, then move to the sheet that will house your consolidated data.
5. Place your cursor in the field where you want the consolidated data to appear. Type the equal sign in that field, then click your cursor on the sheet containing the first number you want to add. Highlight the cell containing that information then type the plus sign. Move to the next cell you want to add to your consolidated spreadsheet, highlight it and press the plus sign. Continue this process until all cells have been added, then hit the 'Enter' key.

Read more ►

How to Print to 24 X 36 Paper in Excel

1. Open up your spreadsheet in Microsoft Excel. Then click on the 'File' drop down menu and choose 'Page Set-Up' and another menu will open.
2. Select 'Landscape' if you want the document printed horizontally or 'Portrait' if you would the document printed vertically.
3. Choose to have your document fit on '1' page wide by '1' page tall under the 'Scaling' option.
4. Select the 'ARCH D' paper size and then press 'OK.'
5. Click the 'File' drop down menu again. Select 'Print' and another menu will open. Choose to print as an Adobe PDF from the drop down menu.
6. Click on the 'Properties' button. Make sure you are in the 'Adobe PDF Settings' tab and choose ARCH D from 'Adobe PDF Page Size' drop down menu and press 'OK.'
7. Select 'OK' again in the main Print menu. This will save the document to your computer as a PDF.
8. Print the PDF to a hard copy with your printer if it is capable of using 24'x36' paper; otherwise, upload your document to the website of a commercial printing store or company.

Read more ►

How to Protect and Share a Workbook in Microsoft Excel 2003

1. Open the workbook by running your application and then scrolling to the “File” tab and selecting “Open.”
2. Access the security properties menu. Scroll to the “Tools” tab on the command bar and then select “Protection.” A submenu will open.
3. Select “Protect and Share Workbook.”
4. Set protect and share properties. Make sure you check the box labeled “Sharing With Track Changes.” This will allow you to see all changes made by users who work on the book. Finally, enter the desired password in the “Password” field and then click 'OK' to protect and share the workbook.

Read more ►

How to Disable the Functions of a PivotTable

1. Open Excel and then open the workbook that contains the PivotTable you would like to disable.
2. Right-click on any cell within the PivotTable and then click 'PivotTable Options.'
3. Click the 'Display' tab and then uncheck the selections for 'Show expand/collapse buttons,' 'Show contextual tooltips,' ' Display field captions and filter drop downs,' 'Classic PivotTable layout (enables dragging of fields in the grid)' and 'Display item labels when no fields are in the values area.'
4. Click 'OK.'

Read more ►

How to Create Pivot Tables Using Excel 2007

1. Double-click the Excel 2007 file that contains your source data.
2. Edit your source data in Excel 2007 so there are no blank rows or columns in the data that you want to use to make a Pivot Table. Also ensure that the top row contains column headers. If there are no column headers, right-click on the first row of the data and choose 'Insert' to insert a new row. Type in column headers in this row.
3. Click on the top-left cell in the data source and hold down the mouse button. Drag the mouse to the bottom-right cell and release the button.
4. Click the 'Insert' tab on the toolbar and then press the 'Pivot Table' button on the left side of the ribbon across the top of the window. This will launch the 'Create Pivot Table' wizard. Press the 'OK' button to create the Pivot Table on a new worksheet. If you want to use the same worksheet, select the 'Existing Worksheet' option and enter in the top-left cell where the Pivot Table will go.
5. Place checkmarks next to the fields, which correspond to the columns in your source data, that you want to include in the Pivot Table. As you place the checkmarks, you will see the fields appear in one of four boxes on the screen. And you will see the data in those fields populate the Pivot Table.
6. Drag and drop the fields to move them from one Pivot Table area to another. Each area corresponds with a different section of the Pivot Table. You can have each field be a column on the Pivot Table, a row on the Pivot Table, a filter above the Pivot Table or part of the data set that makes up the bulk of the Pivot Table.

Read more ►

How to Use Microsoft Excel 2003

1. Open Excel 2003. Click 'Start,' 'All Programs,' 'Microsoft Office' and then 'Microsoft Office Excel 2003.'
2. Select a cell by left-clicking on it. You can select any cell to edit the value on the cell with the left mouse button. After selecting a cell, you can navigate to other cells using the arrow keys.
3. Enter a value into a cell. After selecting a cell, type words, numbers or both and then press the enter key. The value will appear in the cell; you can continue selecting cells and entering data to create large tables of data.
4. Switch and edit worksheets. Click the different worksheet tabs at the bottom of the Excel window to switch to a different worksheet. Each Excel 2003 workbook can have many worksheets with unique sets of data. You can rename worksheets by double-clicking on the worksheet name and then typing a new name. You can add more worksheets by clicking on the 'Insert' menu and then selecting 'Worksheet.'
5. Print spreadsheet data. Click on the tab of the worksheet you want to print, then click 'File,' then 'Print,' select the appropriate option for the print job you want to perform under 'Print what,' (print the active worksheet, selection, the whole workbook, etc.) and click 'OK.'
6. Save your spreadsheet. To save your workbook in Excel at any time, click 'File' and then 'Save.' You can also hold down the 'control' key and then press 'S' to save the file.

Read more ►

How to Make a Frequency Table on Microsoft Excel

1. Open Microsoft Excel by going to 'Start' > 'Programs' > 'Microsoft Office' > 'Microsoft Excel.'
2. Input your data into the spreadsheet by simply typing in each value, or open your existing spreadsheet where you want to make a frequency table.
3. Type in your bins, which help you to organize the data in your Frequency table. You should have at least 5 to 20 bins in order to get a good Frequency table, and the bins must match the range of your sample values. (For more information on bins, please see the link in the Resources section.)
4. Click the 'Office Orb,' located at the top left of the screen, and select 'Excel Options.' Click 'Add-Ins.'
5. Click the 'Go' button located next to 'Manage: Excel Add-ins.' Click the box located next to 'Analysis Toolpak' to select it, and then click 'OK.' Click 'Yes' when prompted to by Excel, and the Analysis pack will begin installing.
6. Click the 'Data' tab in Excel and select 'Data Analysis,' located on the right-hand side of the screen. Select 'Histogram' and click 'OK.'
7. Click the button located next to 'Input Range,' and select all the numbers whose frequency you want to find. Click the button located next to 'Bin Range,' and select the bin numbers you specified in Step 3.
8. Select 'New Worksheet Ply' for the output and then click 'OK'. Your Frequency table will appear on the new worksheet.
9. Click the New Worksheet Ply to make sure the Frequency table has been successfully created. You've now made a Frequency table in Microsoft Excel.

Read more ►

How to Save Word Docs to PDF in VBA

Enable PDF Support in Word
1. Open Microsoft Word 2007. Click the blue help icon in the top, right corner of the program. Type 'pdf and xps file formats' in the Word Help search box and press 'Enter.' Click the 'Print, share and protect files in the PDF and XPS file formats' search result.
2. Click 'Install and use the Save as PDF or XPS add-in from Microsoft' in the 'What do you want to do' section. Click the blue-formatted Internet link that reads 'Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office programs' to be taken to the add-in download page. Close the Microsoft Word program.
3. Click the 'Download' button on the add-in page. Save the file to your computer. Double-click the file to open it. Click the check-box to accept the Microsoft Software License Terms. Click 'Continue' and wait for the installation to complete.
Create and Run VBA Code
4. Open a Microsoft Word document that you want to convert to PDF. Click the Office button and select 'Word Options.' Click the 'Show Developer tab in the Ribbon' check box to enable it. Click 'OK.'
5. Click the 'Developer' tab in the Ribbon. Click 'Macros' in the 'Code' group. Type 'SaveToPDF' or another name of your choice in the 'Macro name' box. Click the 'Create' button.
6. Type the following VBA code lines, or copy and paste them, in the 'New Macros (Code)' window in the line directly above the 'End Sub' line:ActiveDocument.ExportAsFixedFormat OutputFileName:= _ActiveDocument.Path '\' ActiveDocument.Name '.pdf', ExportFormat:= _wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _wdExportOptimizeForPrint, Range:=wdExportAllDocument, _Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _BitmapMissingFonts:=True, UseISO19005_1:=False
7. Click the 'File' menu and click 'Close and Return to Microsoft Word.'
8. Click 'Macros' in the 'Code' group in the 'Developer' tab. Click the name of your macro to highlight it if it is not already selected. Click 'Run' to run the VBA code and automatically save the Word document as a PDF file. Navigate to the folder location on your computer in which your Word document is saved to find the PDF version of the file.
9. Open any other Word documents that you want to save as a PDF. Click the 'Developer' tab, click 'Macros,' select the name of the macro you created and click 'Run.'

Read more ►

MS Excel Instructions

Putting In Titles
1. Start up Excel. A blank spreadsheet page will appear. Click on the top left cell (rectangle) and type 'Quarterly Expenses.' This cell is cell A1. To the right is cell B1 and below it is cell A2. Note that when you click on a cell, the column letter and row number are highlighted and the cell number shows in the area above column A.
2. Click on cell B2 to create a column title. Type 'Jan.' Click on cell B3 and type 'Feb.' Click on cell B4 and type 'Mar.' Click on cell B5 and type 'Total.' Click on cell B6 and type 'Monthly Average.'
3. Click on cell A3 to create a row title. Type 'Medical.' Click on cell A4 and type 'Utilities.' Click on cell A5 and type 'Other.' Click on cell A6 and type 'Total.'
Adding Data
4. Click on cell B3 and type in the amount of January medical expenses. You do not have to use a dollar sign, just type in an amount like 123.56. Click on cells C3 and D3 and add medical expenses for February and March.
5. Click on cells B4, C4 and D4 and enter utility expenses for January, February and March.
6. Click on cells B5, C5 and D5 and enter Other expenses for January, February and March.
Adding Formulas
7. Click on cell B6 and type the following: =sum(B3:B5) and hit 'Enter.' The total for the January expenses should now show in the cell. If you click on the cell, the formula will show in the white area above the spreadsheet.
8. Click cell B6 and drag your mouse across to cell D6 and type CTRL-R (copy right). This will copy the formula to the other two cells, adjusting the cell references automatically. The totals for February and March should appear.
9. Click cell E3 and type =sum(B3:D3) and hit 'Enter.' The quarterly sum for medical expenses should appear.
10. Click cell E3 and drag the mouse down to cell E6 and type CTRL-D (copy down). The formula will be copied down to the other cells including cell D6 which now shows total quarterly expenses.
11. Click on cell F3 and type =average(B3:D3) and hit 'Enter.' This cell will now show the average monthly medical expenses for the quarter. Click on cell F3 and drag the mouse down to cell F6 and type CTRL-D to copy the average formula down to the other rows.
Formatting Cells
12. Click on cell A1 and drag the mouse to cell F6. This will highlight the whole spreadsheet. From the Format menu select 'Cells...' On the Number tab select 'Currency.' In the Symbol: box select '$.' Click 'OK.' All your numeric cells will now show with a dollar sign and two decimal places.
13. Click the 'A' at the top of Column A to highlight the entire column. From the Format menu select 'Cells...' Select the Font tab. Select 'Bold' under font style and click 'OK.'
14. Click on the '2' at the left of row 2 to highlight the entire row. From the Format menu select 'Cells...' Select the Font tab. Select 'Bold' under font style and click 'OK.' Now your column and row titles are in bold. You can select cell A1 and change the font size and style for the title if you wish.

Read more ►

How to Lock Objects in an Excel Chart

1. Open Excel and the worksheet that contains the chart you want to lock.
2. Select any cells you don't want locked. In this case, highlight all of the cells because you only want to lock elements in your chart. Click-and-drag your mouse to highlight, or hold down 'Ctrl' on your keyboard while you click on each cell you don't want locked.
3. Click 'Format' in the 'Cells' group on the 'Home' tab and choose 'Format Cells.'
4. Click the 'Protection' tab, click the box next to 'Locked' to clear the check mark and click 'OK.'
5. Hold down 'Ctrl' on your keyboard and click each graphic object, including any parts of your chart, that you don't want locked.
6. Click the 'Format' tab and click the icon for the Dialog Box Launcher, which is located in the 'Size' group next to 'Size.'
7. Select the 'Properties' tab and click the check box next to 'Locked' to clear the check mark, then click 'OK.'
8. Click the 'Review' tab and click 'Protect Sheet' from the 'Changes' group.
9. Click the boxes next to 'Edit objects' under 'Worksheet Elements,' and 'Contents' and 'Objects' under 'Chart Sheet Elements' in the 'Allow all users of this worksheet to.' This prevents anyone from making changes to charts that you did not unlock.
10. Type a password in the box next to 'Password to unprotect sheet' and click 'OK.' Retype the password to confirm your entry.

Read more ►

How to Succeed in Business Using Microsoft Excel 2007

1. Solve problems with statistical analysis tools. Businesses can identify trends, make forecasts and find out what is required to reach goals by using some of Excel's statistical functions or tools. The Goal Seek tool assists businesses in finding out what is needed to achieve a numerical goal. The regression analysis and histogram are valuable when looking for trends or producing forecasts.
2. Apply logic in decision making. Microsoft Excel makes use of Boolean logical values, such as 'True' and 'False,' to allow businesses to compare data under multiple criteria. These tools allow businesses to analyze complex data by converting it into simple terms for decision making. For instance, the business can create a set the criteria, and then Excel will quickly determine if the data meets all of the criteria, some of the criteria or none of the criteria.
3. Retrieve data for computation, analysis and reference. Most programs used by businesses allows exportation of data in a file format that is recognized by Excel, such as CSV. The data can then be viewed, manipulated and analyzed within the software. A helpful tool in Excel for arranging and sorting data is the Pivot Table, in which columns and rows can be arranged by clicking and dragging from a menu. In addition, the Reference and Lookup functions can expand a business's ability to look at values based on specific criteria or find values that produce a pre-determined result.
4. Use data tables and scenarios to calculate a 'what-if' analysis. A 'what-if' analysis allows businesses to change specific variable to see the possible outcomes. The Scenario Manager within Excel was developed to allow businesses not only to create these scenarios but also conveniently present multiple scenarios on one spreadsheet.
5. Make decisions by using Excel's Solver. Businesses can set up complex business models, then find the optimal sets of decision inputs to meet their objective, such as minimizing costs or maximizing profits. This tool is much more powerful than Goal Seek, as Goal Seek allows you to change only one cell or decision input.

Read more ►

How to Graph a Function in Excel 2007

1. Start Microsoft Excel and type the following:In cell 'A1' type 'X.' In cell 'A2' type '1.'Type '2' in cell 'A3.'
2. Select cells 'A2' and 'A3' by clicking and dragging the bottom right corner then drag down column 'A' until you have the values 1 through 10.In cell 'B1' type 'Y.' In cell 'B2' type '=A2^2.' Press 'Enter.'
3. Select 'B2' and click the lower right corner and drag down column 'B' until you have the values 1 through 100.
4. Highlight 'A1' to 'B11,' click the 'Insert' menu, select 'Charts' and click 'Scatter.' Click 'Scatter with smooth lines' chart.

Read more ►

How to Plot Data Points in Excel 2007

1. Type your data points into two columns. One column will be the x-values and the other column will be the corresponding y-values.
2. Click on a corner of the data plot cells and drag your mouse, so that all the data plot cells are highlighted.
3. Click on the 'Chart Wizard' icon in the toolbar and then select 'XY(Scatter).' Click on the type of graph you want and then you will see your data points graphed.
4. Click 'Next' to label your graph and the x and y values.
5. Click 'Finish' when you are done labeling. Your graph will appear in the spreadsheet.

Read more ►

How to Transfer 2010 Excel Spreadsheet to Access 2010 Using VBA

1. Start Access. Click 'Blank Database' and click the 'Create' button to create a new database. Click the 'Database Tools' tab and click 'Visual Basic' to open the Microsoft Visual Basic Window. Click the 'Insert' menu and click 'Module' to insert a new code module.
2. Copy and paste the following code to create a new sub procedure:Private Sub importExcelSpreadsheet()
3. Press 'Enter' on your keyboard to create the 'End Sub' for the procedure.
4. Copy and paste the following code inside the 'Private Sub importExcelSpreadsheet()' procedure to import an Excel spreadsheet to Access:DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _'Employees', 'C:\Employees.xlsx', True
5. Edit 'Employees ' and type the name of the table for the targeted spreadsheet. Edit 'C:\Employees.xlsx' and type the path and the name of your Excel spreadsheet to be imported.
6. Press 'F5' to run your procedure and import the Excel spreadsheet into Access.

Read more ►

How to Set Up Excel Templates

1. Open Excel and click the 'Office Button' at the top left.
2. Click 'New' in the menu that appears.
3. Click the 'Invoices' category on the left side of the window that appears.
4. Click the template you want to use, then click 'Download' to open it.

Read more ►