June 2011 - Excel

Browse » Home » Archives for June 2011

Tuesday, June 28, 2011

Tutorial for Solving a System of Linear Equations With Excel

1. Type the equation into a cell, substituting another cell's reference for the equation's variable. For instance, if the equation is '6x 33 = -1,' type '=A2 * 6 33' into cell A1.
2. Click on 'Data' from the menu bar. Click 'Solver' from the 'Analysis' tab.
3. Type the formula's cell reference into the 'Set Objective:' box. With this example, type 'A1.'
4. Enter the equation's target value in the 'Value Of:' box. With this example, type '-1.'
5. Type the cell reference that you substituted in Step 1 into the 'By Changing Variable Cells:' box. With this example, type 'A2.'
6. Click 'Solve.' Excel will change the cells' values to solve the equation. With this example, cell A1 will become '33,' and cell A2 will become '-5.667.'
7. Repeat the process for all your linear equations.

How to Merge Workbooks in Excel

Preparing a Workbook to Be Shared
1. Open the workbook you want to distribute.
2. Open the Tools menu and click Share Workbook, and then click the Editing tab.
3. Select the 'Allow changes by more than one user at the same time' box.
4. Click the Advanced tab.
5. Under 'Track changes,' click 'Keep change history for.'
6. In the Days box, type a few days longer than the number of days reviewers will spend making changes and comments in the shared workbook.
7. Click OK.
8. Save the workbook.
9. Make copies of the workbook to distribute by using the Save As command in the File menu. Give each copy a different name, such as 'Budget - Sulhi copy' and 'Budget - Marina copy.'
Merging the Shared Workbooks Back Together
10. Open your base copy of the workbook you distributed.
11. Open the Tools menu and select Merge Workbooks.
12. Save the shared workbook if you are prompted.
13. In the box that appears, select one of the copies you made of the workbook.
14. Click OK.
15. Repeats steps 2 through 4 until all copies of the shared workbooks are merged.

How to Protect Macros in Excel 2003

1. Access the Excel 2003 workbook that contains the macro you need to protect. Click the 'Tools' menu and select 'Macros.'
2. Click 'Visual Basic Editor.' Click the 'Tools' option and then select 'VBAProject Properties.'
3. Navigate to the 'Protection' tab. Click the check box labeled 'Lock project from viewing.'
4. Type the password you want to use for the macro in the text box. Press 'Tab' and then type the password again in the second text box.
5. Click 'OK' and then save your Excel workbook.

How to Do an Outline in Excel

1. Open the Excel 2010 file that holds the data that you want to outline.
2. Insert summary columns into your data. These are the columns that will summarize the hidden numerical data when you outline is compressed. Right-click the column letter where you want to place the column and select 'Insert.' Select the cell next to the cells that you want to subtotal and enter in '=subtotal(9,X:Y)', where 'X' is the first cell in the range and 'Y' is the last.
3. Click the letter where you want to start your outline. Hold 'Shift' and select the letter where you want end your outline. If you have a summary column at the end of your data, do not include it in your selection.
4. Select the 'Data' tab from the top of the screen. Find the 'Outline' area and click the 'Group' button. A line will appear above the cells, with a '-' on one end. You can click the '-' to close the outline. Just click the ' ' button to open it again. Because your summary column was not included in your selection, it will remain visible when you close the outline.
5. Select additional columns within the overall outline selection. Click the 'Group' button again to group these columns together. Another line will appear, this time just below the first one.
6. Continue to group columns in the same manner until your outline is finished. Your outline can grow up to eight levels deep.

How to Copy Paste Row Height in Excel 2007

1. Open an Excel spreadsheet with data.
2. Hover your mouse on the line between two row numbers until you see two black arrows facing in opposite directions appear. Click and drag up or down to adjust the row height.
3. Hover your mouse over the row number you just adjusted until the black arrow pointing to the right appears. Click that row to highlight it.
4. Click the 'Home' tab, and then click the little paintbrush icon in the Clipboard group. Your highlighted row should now have a moving, dashed line around it.
5. Click on a row number to paste the row height.

Monday, June 27, 2011

How to Make Line Graphs in Excel

1. Enter the values you wish to graph in a single column with no spaces.
2. If you wish to attach a descriptor to each value, enter the descriptor to the left of each number in the column. For example, if you want to see how much weight you've lost over time, list the date on the left and the value on the right for each time you've stepped on a scale.
3. Click and drag to highlight all the information you've just entered, including your descriptors. That should cause a blinking box to surround the data you wish to graph.
4. From the top menu, select 'Insert' and then 'Chart.' Choose 'Line graph' from the list of chart types, then select your preferred style from the thumbnails.
5. Follow the prompts to enter a title for your graph and label the X (horizontal) and Y (vertical) axes. Your descriptors should already appear along the X axis. You will then be allowed to choose whether to create your graph within your spreadsheet or on a new sheet.

How to Import a Macro to Excel 2007

1. Open the Microsoft Excel 2007 file that you want to import the macro code into and then click on the 'Developer' tab.
2. Click on the 'Macro Security' option from the 'Code' group. Click on the 'Macro Settings' option from the 'Macro Settings' category.
3. Click on the 'Enable all macros' option and then click on the 'OK' button. Minimize your workbook.
4. Open the Excel file that contains the macro that you want to copy. Click on the 'Developer' tab and then click on the 'Visual Basic' option from the 'Code' group.
5. Click on the 'View' menu option and then click on the 'Project Explorer' option. Click on the macros module you want to import and drag into the other workbook that you want it copied to. The macro will then be fully added to your workbook.

How to Consolidate Worksheets in Excel

1. Turn on your computer and open the Excel spreadsheets that need to be consolidated.
2. Consolidate spreadsheets by position, category, formula or by using a PivotTable. For this how-to we are going to be consolidating by category. Begin by making sure the format is identical in each worksheet. For example, 'Annual Avg.' and 'Annual Average' are seen as different and won't consolidate together.
3. Next go to the worksheet that you want to consolidate and click 'Data,' then choose 'Consolidate.' This will open the Consolidate box.
4. Specify the range by using the Reference box in the window. Click 'Add' to move the range to the 'All References' box. Continue adding ranges until all are defined.
5. Check the 'Create links to source data' box to add links back to the original data. Then click 'Ok' to save and perform the consolidation.

Sunday, June 26, 2011

How to Add New Data to Excel 2007 Charts

1. Open the Microsoft Excel 2007 application and the worksheet containing the chart that you want to work with.
2. Click on the chart that you want to add new data for to display the Chart Tools. Make sure you also have the new data stored in the same worksheet where the chart is stored.
3. Click on the 'Design' tab and then click on the 'Select Data' option from the 'Data' group.
4. Click on the 'Add' option from the Select Data Source dialog box. The Edit Series dialog box will then appear.
5. Enter the name for the new series of data into the 'Series name' box. Click in the 'Series values' box and then select the group of cells in your worksheet that contain the new data you want to add. The new data will be added to your chart.

How to Set Excel 2003 Print DPI

1. Launch Microsoft Excel 2003 and open the spreadsheet you want to print.
2. Open the 'File' menu at the top of the window and select 'Page Setup' from the drop-down list.
3. Go to the 'Page' tab at the top of the Page Setup window.
4. Click on the drop-down box next to 'Print Quality' and select the new DPI setting you want to use. The available DPI options will differ based on the type of printer you are using.
5. Click 'OK' to save the settings or press 'Print' to go directly to the main printing dialog box.

How to Select All With Excel VBA Macros

1. Press 'Alt' and 'F11' together on your keyboard from within Microsoft Excel.
2. Open a new module by clicking on 'Open' and then 'Module.'
3. Copy and paste the following code into the blank window:Sub Macro1()'' Macro1 MacroCells.SelectRange('J1').ActivateEnd Sub
4. Press 'F5' to run the macro on your worksheet.

How to Import a Selected Range of Cells Into Access From Excel 2003

1. Open the Excel 2003 spreadsheet that you want to import. Drag your cursor along the record selection buttons to the left of your cells until you encompass the intended records. Press 'Ctrl' and 'C' to copy the records, select another tab in your spreadsheet and press 'Ctrl' and 'V' to paste.
2. Label the second tab with a description so that you can recognize it from Access. Save your Excel spreadsheet after making all of your changes. Close your Excel spreadsheet and open the Access database.
3. Click the 'External Data' tab of the ribbon at the top of the page and select 'Excel.' If you are using Access 2003, click 'File,' then 'Get External Data' and then 'Import.' Locate the Excel spreadsheet saved on your computer and choose whether to add it to a new table or an existing table.
4. Choose the tab with the selected records out of the worksheet options in the import wizard. This option allows you to import single sheets from the same Excel file. Instead of needing to create a new spreadsheet for certain information, you can simply choose a certain sheet.
5. Specify the fields you want to import and the primary key if you're creating a new table. If your records don't have an appropriate field, Access can create a primary key field for you. Name the table and complete the import wizard.

How to Remove Columns in Microsoft Excel 2003

1. Activate the column that you want to remove by left-clicking on the column letter, located at the top of the spreadsheet. When you left-click on the column letter, it will highlight and activate the entire column.
2. Access the remove column properties box. To access this menu, simply right-click on the highlighted column that you wish to remove. A sub-menu will open.
3. Select the remove column attributes. Scroll to “Delete” in the column properties sub-menu. Left-click on it to remove the column.
4. Save the edited spreadsheet. Make sure to save your file after making any changes by accessing the “File” tab on the command bar and left-clicking on “Save.”

How to Organize in Excel 2007

1. Open your Excel 2007 program. Right click the bottom of the program where is says 'Sheet 1.'
2. Click 'Rename' to give the worksheet a specific name. You can also rename the other available sheets or add more sheets by clicking the icon next to 'Sheet 3.' Multiple sheets allows you to organize different sets of data within the same spreadsheet, such as expenses and payments.
3. Type in the headers for your columns or rows to start adding data. For example, you might have separate columns for various types of expenses. You can add each expense to different rows along with the cost to organize the information more effectively.
4. Move your data into a table for even more organization power. Highlight the data you want in the table. Click 'Format as Table' from Styles. Choose a table layout. Your data will then be moved into a table, which makes sorting much easier.
5. Sum up totals for expenses, payments and other information with numbers or money easily. Highlight all the columns or rows you want to add up. Highlight one extra cell. From 'Formulas,' click 'AutoSum' and the numbers you highlighted will add up on the last cell.

Saturday, June 25, 2011

How to Remove All Hyperlinks in an Excel Spreadsheet

1. Open the spreadsheet you want to remove all the hyperlinks from.
2. Hit the “Alt' and “F11” keys to get to the Visual Basic Editor.
3. Click “Insert” on the task bar. Click “Module” from the drop-down menu. A box opens.
4. Paste the following command into the module box.Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub
5. Click “File” and “Close.” The macro is now ready for use in Excel.
6. Return to the Excel spreadsheet. Click “Tools” on the spreadsheet toolbar. Click “Macros' from the drop-down menu.
7. Click “RemoveAllHyperlinks” and then the “Run” button. The hyperlinks in the document disappear.

How to Extend the Trend Line in Excel

1. Open Microsoft Excel 2010.
2. Click the 'File' tab and select 'Open.' Select your file and click the 'Open' button.
3. Right-click the trend line in your spreadsheet file and select 'Format Trendline' from the menu.
4. Locate the 'Forecast' section under the 'Trendline Options.' Type a value in the 'Forward' field to extend the trend line forward, and type a value in the 'Backward' field to extend it backward.
5. Click the 'Close' button.
6. Click the 'Save' icon at the top of the window to save the changes to the file.

How to Repair an Excel 2003 File

1. Open Excel by clicking on the 'Start' button and then 'All Programs,' 'Microsoft Office' and 'Microsoft Excel 2003.' The program will launch on your screen.
2. Click on 'File,' 'Open' to display the 'Open' dialog box.
3. Browse to the location of the corrupted Excel file and then click on it once to select it.
4. Click on the down arrow next to the 'Open' button at the bottom of the window to view a list of additional options.
5. Click on 'Open and Repair.' A dialog box will display on your screen, prompting you to decide whether you want to attempt to repair the document or extract the data only.
6. Select 'Repair' to attempt to open and repair the document. The system will alert you if the repair fails. In this case, repeat the steps, choosing 'Extract Data' instead of 'Repair' when prompted. This process will extract the data and formulas from your Excel workbook without retaining any of the formatting.

Thursday, June 23, 2011

How to Change Cursor Highlight Cell Colors in Windows Excel 2007

1. Open the MS Excel workbook that you want to edit.
2. Click on the 'downward-pointing arrow' next to 'Fill Color' under the 'Font' group. The 'Font' group is a group of font-editing buttons on the Office Ribbon (the main toolbar). When clicking on the 'downward-pointing arrow' button, a drop-down color menu is displayed.
3. Click any of the colors listed under 'Theme Colors' or click 'More Colors.' Click a color in the hexagonal color diagram and click 'OK.' The color that you select will be set as the new highlighting color when you click the 'Fill Color' button under the 'Font' group. Click any cell and then click the 'Fill Color' button to highlight the cell with the color you selected.
4. Click on a cell with a highlighting color you want to apply to other cells, double-click on 'Format Painter' under the 'Clipboard' group and then click any other cell to apply the same highlighting color to the cell. Continue clicking cells to paint them with the selected highlight color and press the 'Escape' key when you are finished highlighting.

How to Use Auto Complete in a Combo Box in Excel 2003

1. Launch the Microsoft Excel program on your PC computer and open the spreadsheet file in which you want to add a combo box.
2. Enter the values you want to use in the combo box anywhere on your spreadsheet. Use a separate cell for each value, but keep them together in a single column so that you can reference them later.
3. Go to the 'View' menu at the top of the window, expand the 'Toolbars' submenu and select 'Forms.' This will add several new elements to the main Excel toolbar, including the combo box.
4. Select the 'Combo Box' icon in the toolbar and then click on the area of your spreadsheet where you want to place the combo box item.
5. Hold down on the left mouse button and drag your pointer to the right to extend the size of the new combo box.
6. Right-click on the combo box you just created and choose the 'Format Control' option.
7. Fill in the 'Input Range' field with the location of the cells where you entered the desired autocomplete values in Step 2. Use a colon symbol to separate the first cell in the column from the last cell.
8. Press the 'OK' button to save the settings. As you begin to type in the combo box, Excel will autocomplete the text based on the values you have previously configured.

How to Create a Scatter Plot Graph

1. Enter column headers in row 1. Put the variable that you want on the X axis in cell A1, and the variable you want on the Y axis in cell B2. For example, if you are making a scatter plot of height and weight, enter 'Height' in A1 and 'Weight' in B1.
2. Enter data. In our example, enter the height and weight of each person in a row, with height in column A and weight in column B.
3. Click 'insert,' 'scatter,' and then the 'graph' on the upper left. This is a scatter plot, but you may wish to modify it.
4. For a more standard scatter plot layout, choose 'layout 1' in the design menu, then click on the X-axis title and type in 'Height.' Click on the Y-axis title and type in 'Weight.' Delete the little box on the right that says 'weight.' Then click on the title of the graph and enter something appropriate, such as 'Height and weight.'

Wednesday, June 22, 2011

How to Add Collapsing Plus Signs in Excel Columns

1. Open the Microsoft Excel 2010 worksheet that holds your data.
2. Click on the column letter above the first column that you want to include in your outline. This will select the entire column. Hold the Shift key, then click on the column letter above the last column you want included in your outline.
3. Select the 'Data' tab at the top of the Excel window. Locate the 'Outline' area of the Ribbon and click the 'Group' button. You will see a line appear above your columns with a minus sign on the end of it.
4. Click on a column letter within your grouped cells, then hold Shift and select another column letter to create a second level of the outline. Click the 'Group' button again and a new line and minus sign will appear under the first one. Continue creating levels until you have created your entire outline, or until you reach eight levels, which is the maximum that Excel will allow.
5. Click the minus signs to hide the levels of columns and all that will remain above the columns headers is a set of plus signs.

Tuesday, June 21, 2011

How to Open Excel 2007 in Excel 2003

1. Click the Windows 'Start' button, select 'All Programs' and then select 'Windows Update' if you use Windows Vista or Windows 7. If you use Windows XP, open a Web browser, navigate to the Microsoft Update website (see Resources) and click 'Express.'
2. Click 'Install' or 'Install Updates' to install all High-Priority or Required updates. Skip this step if your system is already up to date.
3. Open a Web browser window and navigate to the Microsoft Office Compatibility Pack download page on the Microsoft website (see Resources).
4. Click the 'Download' button, and save the installer program to your desktop when prompted by your browser.
5. Close all open programs.
6. Double-click the icon of the installer program you just downloaded. If you use Windows XP, click 'Run.' If you use Windows Vista or Windows 7, confirm your identity, or provide your administrator user name and password and click 'OK,' when prompted to confirm your choice.
7. Check the box labeled 'Click here to accept the Microsoft Software License Terms,' and then click 'Continue.'
8. Click 'OK' to install the Compatibility Pack. If you use Windows Vista or Windows 7, confirm your identity, or provide your administrator user name and password and click 'OK,' when prompted to confirm your choice.
9. Click 'OK' after installation completes to close the installer program.
10. Open Microsoft Excel 2003.
11. Click 'File' in the top menu bar, and select 'Open.' Locate your XLSX file in the resulting window, and then open it by double-clicking its icon.

Monday, June 20, 2011

How to Use a Drop Down Box in Excel 2003

1. Open Excel 2003 and highlight a group of cells. Select 'Data' on the menu bar and select 'Validation.' In the validation criteria tab, change the 'Allow drop down list' to 'List.' In the 'Source' list, type 'Apples, Bananas,Grapes.' Click 'OK.'
2. Click on one of the highlighted cells. Notice the drop down list.
3. Click the drop down list. Select one of the choices in the list.

How to Delete Print Lines in Excel 2007

1. Select the worksheet or worksheets you want to print without the lines. To select one sheet, click on the tab at the bottom of the workbook. Select two or more sheets that are next to each other by clicking the first sheet, holding down the 'Shift' key and then clicking the last sheet. Select two or more sheets that are not next to each other by clicking the first sheet, holding down the 'Control' key and then clicking the tabs of the other worksheets.
2. Click the 'Page Layout Tab' on the ribbon at the top of the window. In the 'Sheet Option' group, click the box in front of the 'Print' option to remove the checkmark. The lines do not print when this is selected. If the option appears dimmed, you might have a spreadsheet or graphic in one of the worksheets selected. You will need to deselect it before you can change this option.
3. Print the gridlines by clicking in the box again if you change your mind about printing the lines. This will cause the lines to print on the spreadsheet.

Sunday, June 19, 2011

How to Delete Macros in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer and the click on the workbook that contains the macro that you want to delete.
2. Click on the 'Tools' option from the top toolbar menu and then scroll over the 'Macro' option. Click on the 'Macros' option.
3. Locate the 'Macros in' list and then click on the 'This Workbook' option. A small dialog box will then appear and show the macro you have in the workbook.
4. Click on the name of the macro you want to delete in the 'Macro name' box. Hold down the 'CTRL' key on your keyboard to select more than one macro.
5. Click on the 'Delete' and your macro will be deleted from your workbook. Close out of the dialog box to return to your workbook.

How to Create Progress Bar in VBA

1. Open Microsoft Office Excel, then press 'Alt' and 'F11' to open the Visual Basic Editor. Right-click 'VBAProject,' then click 'UserForm' to insert a new Form. From the 'Toolbox' menu, click 'Label.' While holding your mouse button, drag on the Form to create a new label control.
2. Right-click the label and select 'Properties,' then change 'Height' to 42 and 'Width' to 12. Change the 'Top' to 12. Create nine more labels using the same process.
3. Click 'CommandButton' on the 'Toolbox' pane and add a new button to your Form. Right-click 'CommandButton' and click 'View Code' to create a new procedure.
4. Type the following to create a new variable and define its value:Dim cnt As Integercnt = 0
5. Type the following to hide all the labels when the Form starts:Me.Label1.Visible = FalseMe.Label2.Visible = FalseMe.Label3.Visible = FalseMe.Label4.Visible = FalseMe.Label5.Visible = FalseMe.Label6.Visible = FalseMe.Label7.Visible = FalseMe.Label8.Visible = FalseMe.Label9.Visible = FalseMe.Label10.Visible = False
6. Type the following to create a while loop and iterate 10 times while pausing the code for 5 seconds:Do While cnt
10Select Case cntCase Is = 1Me.Label1.Visible = TrueMe.Label1.BackColor = vbBlueCase Is = 2Me.Label2.Visible = TrueMe.Label2.BackColor = vbBlueCase Is = 3Me.Label3.Visible = TrueMe.Label3.BackColor = vbBlueCase Is = 4Me.Label4.Visible = TrueMe.Label4.BackColor = vbBlueCase Is = 5Me.Label5.Visible = TrueMe.Label5.BackColor = vbBlueCase Is = 6Me.Label6.Visible = TrueMe.Label6.BackColor = vbBlueCase Is = 7Me.Label7.Visible = TrueMe.Label7.BackColor = vbBlueCase Is = 8Me.Label8.Visible = TrueMe.Label8.BackColor = vbBlueCase Is = 9Me.Label9.Visible = TrueMe.Label9.BackColor = vbBlueCase Is = 10Me.Label10.Visible = TrueMe.Label10.BackColor = vbBlueEnd SelectMe.RepaintApplication.Wait Now TimeValue('00:00:05')cnt = cnt 1Loop
7. Click the 'Insert' menu then click 'Module' to add a new module. Copy and paste the code below to start your 'UserForm1.':Sub showProgressBar()UserForm1.ShowEnd SubPress 'F5' to run your program.

How to Make a Linear Log Graph in Excel

1. Open Excel 2010 and open your spreadsheet containing the logarithmic data set.
2. Locate an empty space in your spreadsheet to work on your chart. Click somewhere in this area. Click on the 'Insert' tab in the top menu. Click on 'Scatter' in the 'Charts' section and select 'Scatter with Straight Lines and Markers.' Putting your mouse over each icon will give you the title.
3. Right click in the blank chart area that appears in your spreadsheet and click 'Select Data.'
4. Click on the icon at the far right of the 'Chart data range' text box use your mouse to click-drag select the rows and columns containing your data for this chart. Click on the icon again at the far right of 'Chart data range' so that the 'OK' button is displayed. Click 'OK.'
5. Double click on whatever axis (x or y) is your logarithmic data range. For instance if it is the x axis, Double click on the numbers on the x-axis scale. Click on the 'Logarithmic scale' check box which is in the 'Axis Options' section. Click the 'Close' button. You will now see your selected axis changed in scale, for instance increments in base 10 are 1, 10, 100, 1000, etc.
6. Click the 'File' tab in the top menu and select 'Save As' to save this file to keep your work.

Saturday, June 18, 2011

How to Find the Print Area on Excel in Vista

1. Log in to your user account in Windows Vista and launch Microsoft Excel.
2. Press 'Ctrl-O' to browse your computer and open an Excel file.
3. Click the 'Office' button that is located in the top left corner of the Excel window.
4. Select the menu 'Print' and 'Print Preview' to display the printable area on the first page. Use the 'Next page' button to show the rest of the document pages.
5. Click 'Close Print Preview' to quit the preview mode.
6. Select the Excel menu 'Page Layout' to change the print area.
7. Select the desired worksheet cells your left mouse button. Click the menu 'Print Area' and then 'Set Print Area.'

How to Link an Outlook Calendar to Excel

Exporting Outlook Calendar to Excel
1. Open Microsoft Outlook and wait for its main screen to fully load. Click on 'File' from the menu at the top of your page and select 'Import and Export.' Click on 'Export to a file' on the following page before selecting the 'Next' tab at the window's bottom.
2. Select 'Microsoft Excel' from the programs list and click on 'Next.' Click on the 'Calendar' folder from your options list on the following page before clicking on 'Next' at the bottom of your screen.
3. Type your chosen name for the calendar file into the text box. This will be the filename for your calendar in Excel. Click on 'Finish' at the bottom of the screen to start linking your calendar with Excel.
Importing Excel Files to Outlook Calendar
4. Customize Excel by examining your calendar section in Outlook. Customize the fields in Excel to match with all the information on Microsoft Outlook. For instance, when importing appointments, Outlook provides information on the name of the appointment, location, subject and time. Title the columns of your Excel spreadsheet to match the Outlook information.
5. Save the data after you complete your spreadsheet. Choose the rows and columns that contain the exact information you wish to import and include column headings. Type the name referring to your selection at the left side of your address bar, and press 'Enter.' Go to the process of saving the data. Exit Excel after saving.
6. Open Microsoft Outlook by double-clicking on its desktop icon. Import the information by going to the 'File' menu and selecting 'Import and Export.' Click on 'Import from Another Program or File' and select 'Next' before clicking on 'Microsoft Excel 97-2003.' Click 'Next' to start locating your file.
7. Click the 'Browse' button and locate the Excel worksheet that you saved in step 2. Select the spreadsheet and click 'Next.' This will show the available folders that you can import into Outlook.
8. Highlight your Calendar option and choose 'Next.' You will find a summary of what you selected reading 'Import [filename] into folder: calendar.' The [filename] represents the name of the spreadsheet that you made, such as 'meetings' or 'addresses.'

How to Make an Age

1. Type or paste in a spreadsheet the list of ages for males, with one row per age. For example, you might type '10,' '15,' '50' and '24' for the ages of four different people. In practice, however, your list should include at least 30 ages.
2. Write on paper a list of between five and 20 age ranges. For example, write '0 to 5' for the first age range that includes ages zero to five years old.
3. Type in the first cell to the right of the topmost age from Step 1, the maximum age of the first age range on your paper. For the example in the previous step, this age is '5.'
4. Use the previous step's instructions to enter the maximum age of the remaining ranges, below the previous step's cell.
5. Click and drag on the cell immediately to the right of those of the previous step. This action selects those cells.
6. Click the 'fx' symbol to the left of the large text box above the spreadsheet grid, then double-click the 'Frequency' item from the dialog box that appears.
7. Click the small square button for selecting cell ranges, located to the right of the upper text box on the following screen. Drag on the list of raw ages from Step 1.
8. Click the square button to the right of the lower text box, then use the previous step's instructions to select the Step 3 and 4 cells holding the age ranges.
9. Click 'OK' to insert the 'Frequency' function, then click in the formula bar above the spreadsheet grid. Press 'Control,' 'Shift' and 'Enter' simultaneously to fill the cells next to the age range cells with the 'Frequency' function. This action tells you how many males are in each age range.
10. Use Steps 1 through 9 to compute the frequency distribution for females. Enter the raw female data anywhere on the same spreadsheet as the raw male data. However, make sure that the column containing the frequency distribution for females is directly to the right of the male frequency distribution.
11. Drag across both male and female frequency distributions to select them, then click the 'Insert' menu heading. Click the 'Column' button in the 'Chart' panel, then click the top left chart type from the bar chart gallery that appears. Excel now creates the histogram based on your frequency distributions for males and females.

How to Change Minutes to Decimals in Excel

1. Double-click the Microsoft Excel file containing the time value you want to convert.
2. Find the cell location containing the time value that you want to convert to decimal format. For example, if your target cell were in column A, row 4, then the cell location would be 'A4.'
3. Click inside the cell in which you want to display the converted time.
4. Type '=(XX-INT(XX))*24' into the cell, replacing 'XX' with the location of the cell that you want to convert.
5. Press 'Enter' on your keyboard to execute the formula. Note that Excel will take 'AM' and 'PM' values into account, so '10:30 AM' and '10:30 PM' would have decimal values of '10.5' and '22.5,' respectively.

How to Avoid Unauthorized Deletion of Microsoft Excel Files

1. Open your spreadsheet and click on the 'Tools' menu if you are using Microsoft Excel 2003. Choose 'Protection' and then choose 'Protect Sheet' from the menu. This will protect the entire sheet and prevent any changes from being made.
2. Click on the Review tab and then 'Changes' if you are using Office 2007. Click on 'Protect Sheet.' This will protect your Office 2007 spreadsheet from any unauthorized changes.
3. Click on 'Tools' and choose 'Protection' from your Excel 2003 spreadsheet. Choose 'Allow Users to Edit Ranges' from the menu if you want your users to be able to edit specific ranges. Specify the ranges that users will be allowed to change. This is a useful feature if you need to solicit additional information from your users but do not want to risk them accidentally changing any other numbers in your worksheet.
4. Click on the 'Review' tab and choose 'Changes' from the menu if you are working with an Excel 2007' worksheet. Choose 'Allow Users to Edit Ranges' and choose the ranges that users will have permission to change.

How to Create a Password for an Excel File

1. Open Excel and search for a file that you want to protect. Open the file by clicking the 'Open' icon on the Quick Access Toolbar. Browse your computer and locate the file. Click 'Open.' Select a cell to lock. This cell will not be accessible by people who use this spreadsheet. Right click on the cell and select 'Format Cells.' Select the 'Protection' tab and make sure 'locked' is selected. Click 'OK.'
2. Select the 'Review' tab on the ribbon. Select 'Protect Sheet.' Verify that the 'Protect worksheet and contents of locked cells' is selected. Remove the check from the 'Select locked cells' option. This will prevent any users from accessing the locked cells. Add a password. You will be prompted to confirm this password. Write it down in a secure location. Click 'OK.'
3. Add an additional level of security by password-protecting the file from any access without the password. Save the file by clicking the 'Office' button and selecting 'Save As.' Locate the 'Tools' button in the lower left hand corner of the Save As dialog box. Click the 'Tools' button and select 'General Options.'
4. Add your password to allow you to open the file. You will be prompted to confirm this password. The file is password-protected from entry now. Users cannot open this workbook without knowing the password.

How to Round a Number Using VBA

VBA Round Function
1. Open the Excel 2010 worksheet that contains the VBA code you want to work with. Press 'Alt' and 'F11' to launch the VBA console.
2. Click the module on the left side of the VBA console that holds the Excel macro you want to edit. Place your cursor on the code on the right side of the window, where you want to round a number.
3. Enter the following code into the VBA console:x = round(y)Where 'x' is a variable or function name in your code and 'y' is a numeric variable or an actual number. You can also place a comma after 'y' and input the number of decimal places you want to round to, so 'round(223.446, 2)' will return a value of '223.45.'
Excel Worksheet Function
4. Open the Excel 2010 worksheet that you want to work with. Press 'Alt' and 'F11' to open the VBA console.
5. Click the module that you want to work with from the left side of the VBA console. Place your cursor into the code on the right side, wherever you want to round your number.
6. Enter the following code into your VBA console:x = Application.WorksheetFunction.RoundUp(y, 0)In this code, 'x' is equal to a variable or function name and 'y' is a numeric variable or actual number. Unlike the VBA 'Round' function, the number after the 'y,' which signals the number of decimal places to round to, is not optional. Finally, you can replace 'RoundUp' with 'RoundDown' if you need to round the number down.

Friday, June 17, 2011

How to Create Online Forms in Excel

1. Download a utility to convert Microsoft Excel spreadsheets into online HTML forms. Excel does not offer native support for interactive forms. Spreadsheets may easily be saved as HTML files, but the resulting web pages are static and do not allow data entry. A good utility program is not free, but free trials are available. Spreadsheet Converter is an excellent option for making online forms out of Excel. The program may be tried for 30 days with no charge.
2. Install the spreadsheet utility from the downloaded file. Spreadsheet Converter downloads as an '.exe' file. Double-click the file and follow the onscreen instructions until installation is complete.
3. Open Excel. Note the new menu at the top of the program window called 'SpreadsheetConverter.' All of the features of this utility are available through this menu.
4. Build the online form in Excel as you would like it to appear on the web page. For example, type a label of an input field, and leave an empty adjacent cell where the user will enter the data. Cell A1 could be labeled as 'First Name' and cell A2 as 'Last Name.' Cells B1 and B2 would be kept empty for the user response.
5. Code the user data entry cells in Spreadsheet Converter. This is necessary so the utility knows how the resulting online form should function. The program must be told which cells are for data entry. Click on a data entry cell and select the SpreadsheetConverter menu. Choose the appropriate option. The 'Mark Input Cell' command will identify the selected cell as a form field for data input.
6. Create a dropdown list menu for the online form, if desired. Many online forms offer pre-set choices for users to select. Pick a cell for this menu and choose the 'Dropdown List' item from the SpreadsheetConverter menu. A pop-up window will appear. Type in the name of each menu item and press 'OK.'
7. Convert the Excel spreadsheet to an online form. Choose the 'Convert' command from the SpreadsheetConverter menu. Press the 'Next' button on each screen until conversion is complete.
8. View the online form. SpreadsheetConverter automatically opens the HTML version of the Excel spreadsheet in Internet Explorer when the conversion is complete.

How to Create Summary Workbooks in Excel 2003

1. Launch Excel, and open all of the workbooks you want to summarize.
2. Click the 'New' icon to start your summary workbook. Format the cells as you would like them to appear before importing your data.
3. Go to the first workbook you want to include in the summary, and click the cells you want. Click 'Ctrl C' to copy them.
4. Return to your summary workbook, and click the empty cell where you want to include the data. Click the 'Home' button and select 'Paste' and then 'Paste Link.' Repeat Steps 3 and 4 with the data in all of your workbooks until your summary is complete.

How to Track History on Microsoft Excel

1. Click on the 'Review' tab in Excel 2007 or the 'Tools' tab in Excel 2003.
2. Click on 'Track Changes.'
3. Click on the 'Track changes while editing. This also shares your workbook' check box.
4. Click on any feature's check box that you want to track. For example, if you want to track who made changes, click on the 'Who' check box.
5. Click on the drop-down box to the right of the feature you checked in Step 4 to select what kind of changes you want to track. For example, click on 'All' changes.
6. Make sure the default option 'Highlight changes on screen' box is checked, then click on 'OK.' Any changes made to the document will be highlighted on your Excel spreadsheet.

How to Remove All White Spaces to the Right of a Word in Excel

1. Launch Microsoft Excel 2010. Open your spreadsheet by choosing 'Open...' from the 'File' menu or pressing and holding 'Control' ('Command' for Mac) and then pressing 'O.' Navigate to your Excel spreadsheet file and either double-click its name or click on it once and press the 'Open' button.
2. Click on the header of the first column of cells that contains text you need to edit. Go to the 'Home' tab and choose the 'Cells' group. Click on the arrow alongside 'Insert.' Click on 'Insert Sheet Columns' to create a new column to the left of the column you selected.
3. Click in the first cell of the column you just inserted. Type an equal sign and then the letter 'T.' Scroll down the list of functions that comes up till you reach 'Trim' and click on it. Click on the first cell in the column you need to edit. Your cell contents should say '=TRIM([cellnumber]),' where [cellnumber] is the column/row reference to the first cell you need to edit. Press 'Enter' to confirm your formula.
4. Click in the cell in which you just added the 'TRIM' function and place your pointing device cursor at the bottom right corner of the cell. Your cursor will change from an outlined plus sign to a black plus sign. Pull down on the lower right corner of the cell until you drag down to the bottom of the column of data from which you need to remove extra spaces. Let go of the pointing device when you have dragged all the way down. The data column you inserted should be filled with the same information that's in the original column to its right, but without the extra spaces.

How to Remove All Carriage Returns

Removing carriage returns manually - documents
1. Open your computer's word processing or text editor.
2. Open the document you want to remove carriage returns from by navigating to the directory containing the document and double clicking on the file's name.
3. Place your cursor at the end of the line of characters.
4. Press the 'Delete' key on your computer's keyboard until the line below the one you are on has moved up next to your cursor.
5. Repeat Steps 3 and 4 for each carriage return code you want to remove.
Removing carriage returns using automation - documents
6. Open your computer's word processing or text editor.
7. Open the document you want to remove carriage returns from by navigating to the directory containing the document and double clicking on the file's name.
8. Place your cursor at the end of the line of characters. Highlight the area with your mouse to the beginning of the next line of characters.
9. Press and hold the 'CTRL' key on your computer's keyboard. Press the 'C' key to copy the contents highlighted, if using Windows. If using a Mac, press and hold the 'Apple' key and press 'C.'
10. Click 'Edit,' 'Find Replace' to open the Find Replace utility.
11. Click in the box under the heading 'Find What' or 'Search For.' Press and hold the 'CTRL' key. Press the 'C' key to paste the contents highlighted if using Windows. If using a Mac, press and hold the 'Apple' key and press 'V.'If using Microsoft Word or OpenOffice, place '^p' in the 'Find What' or 'Search For.' box.
12. Leave the 'Replace With' box blank. Click on 'Replace All.' All carriage return codes are now removed from your document.
Removing carriage returns using automation -- spreadsheets
13. Open the spreadsheet you want to remove carriage returns from by navigating to the directory containing the spreadsheet and double clicking on the file's name.
14. Enter '=SUBSTITUTE(XX,CHAR(13),'')' in any blank cell of your spreadsheet. Replace the 'XX' with the 'Column' letter and 'Row' number of the cell containing the carriage return you want to remove. Note, the '13' is the ASCII code for a carriage return on both Windows and Mac systems.
15. Press the 'Enter' or 'Return' key on your computer's keyboard to execute the formula.
16. Change the 'Column' and 'Row' values for each cell you want to remove carriage returns from. Press the 'Enter' or 'Return' key to execute the formula.

How to Find and Replace Space Strings in Excel 2007

1. Open your Excel spreadsheet in Microsoft Excel 2007.
2. Hold the 'Ctrl' key and press 'H' to open the 'Find and Replace' window.
3. Click the 'Find what' field and press the space bar to enter a space in the field.
4. Enter the replacement text in the 'Replace with' field. This is the text that appears in place of the spaces. If you prefer to simply remove the spaces, then leave this field blank.
5. Click 'Replace all' to automatically replace all spaces in your spreadsheet. Alternatively, click 'Find next' to locate each occurrence, one at a time, and click 'Replace' to selective replace the spaces.
6. Click 'Close' to close the Find and Replace window.

Thursday, June 16, 2011

How to Encrypt a Spreadsheet in Excel 2003

1. Create a strong document password. After opening the file you want to encrypt in Excel, click the 'Tools' item in the menu bar at the top of the screen, then select 'Options.' In the dialog box, click the 'Security' tab. Enter a strong password in the field labeled 'Password to open.' Strong passwords are at least 10 characters in length, not easy to guess, and include a combination of letters, numbers and symbols.
2. Set the document encryption type. Click the 'Advanced' button next to the password field you just completed. Excel presents you a list of cryptographic algorithm collections used to secure spreadsheets. Encryption of 128 bits or more is considered strong, but some encryption types only support 40 or 56 bits. Scroll down the list and click the 'Microsoft Strong Cryptographic Provider' option, then enter a value of 128 in the field labeled 'Choose a key length.' Click 'OK' to close the dialog box. Click 'OK' a second time to close the Options box.
3. Save your document. Click the 'File' menu at the top of your screen, then select 'Save.' Your Excel spreadsheet is now protected with a strong password and 128-bit encryption.

How to Use a Cell in Excel As a Checkbox

1. In Excel, click 'File' from the menu bar.
2. Click 'Options' from the left pane. The 'Excel Options' window will open.
3. Click 'Customize Ribbon' from the left pane.
4. Check the box next to 'Developer' in the 'Customize the Ribbon' section on the screen's right side. Click 'OK.'
5. Switch to the 'Developer' ribbon.
6. Click 'Insert' from the 'Controls' tab.
7. Click the check box icon under 'form controls.' You cursor will turn into cross-hairs.
8. Click on the cell you want to change into a check box. A box will appear there.
9. Drag the check box to align it with the cell. Change the box's label, which is 'Check Box 1' by default.
10. With the check box still selected, click 'Properties' from the Developer ribbon's 'Controls' tab. The 'Format Control' window will open, with the 'Control' tab selected.
11. Enter the cell address in the 'Cell link' box. Click 'OK.'

How to Reduce a Worksheet Size in Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that contains the worksheet for which you want to reduce the size.
2. Click the 'Page Layout' button from the bottom of the application to switch to the Page Layout view.
3. Select the 'Page Layout' tab and then select the '1 page' option from the 'Width' drop-down menu in the 'Scale to Fit' group.
4. Select the 'Automatic' option from the 'Height' drop-down menu.
5. Click the 'Scale' drop-down menu to select a smaller scale size if you want to reduce the worksheet size even more. Click 'OK' to save your changes.

Wednesday, June 15, 2011

How to Display All Help Topics for Excel

Microsoft Office Website
1. Navigate to the Microsoft Office support website.
2. Locate and click 'Excel' in the list of applications under the 'Current Product Help' box.
3. Click 'All Categories' at the end of the list of Excel 2010 help topics.
Embedded Office Help
4. Launch a Microsoft Excel window. Click 'File.'
5. Click 'Help.'
6. Click 'Microsoft Office Help' to launch the Excel help menu.

How to Make a Duplicate Copy of a Word File

1. Launch Word and open the file you wish to duplicate.
2. Click 'File' to view the saving options.
3. Click 'Save as.' A box will open allowing you to choose a location for the duplicate file.
4. Locate the folder to which you want to save the duplicate document. You can browse your computer directory by clicking the folders and drives in the left or right pane.
5. Click the 'File Name' text box and type a new name for the duplicate file. You can also leave the file name the same so long as you save the file in a new folder.
6. Click 'Save' to create the duplicate document.

How to do a Fast Fourier Transform (FFT) in Microsoft Excel

1. Enable the ToolPak if you have not already done so. Click the 'Office' button, and then choose 'Options.' Click 'Add-In Options,' highlight 'Analysis ToolPak,' and press 'Go.' Highlight 'Analysis ToolPak' a second time, and then press the 'OK' button.
2. Open Excel and create a new spreadsheet file. Add the title 'Time' to the A column, followed by the titles 'Data,' 'FFT Frequency,' 'FFT Complex' and 'FFT Magnitude' to columns B through E respectively.
3. Input the data from your samples into the Data column. Make a note of the number of data points and the sampling rate used.
4. Write the time at which each data point was taken in the Time column. Determine this by dividing the total time by the number of data points.
5. Open the 'Data' tab, and then select 'Data Analysis.' Select the 'Fourier Analysis' option and press the 'OK' button. Set the input range as the information in the Data column and the output as the FFT Complex column.
6. Type the equation '=IMABS (E2)' into the first cell of the FTT Magnitude column. Drag the equation downward to fill every cell of the column. This equation creates real numbers, instead of complex numbers, in the previous column.
7. Fill column F with the corresponding data, from column A, point minus one. Create a separate cell with the equation '=(S/2)/(N/2),' replacing 'S' with the sampling rate and 'N' with the number of samples.
8. Enter the equation '=F2*SG$4' in the first cell of the FTT Frequency column. This time, drag the equation only to the halfway point in the column.
9. Create a graph, using the FTT Magnitude column for the y-axis and the FTT Frequency column for the x-axis. The graph displays the dominant frequencies as peaks.

How to Do Combination Charts in Excel 2007

1. Launch Microsoft Excel and open the spreadsheet containing data by selecting 'Open' under the Office ribbon, selecting the desired file and clicking the 'Open' button.
2. Check that the data is organized in columns, with the first column containing the categories and the second and third columns containing values.
3. Highlight all three columns by selecting the top of the first column and the bottom of the third column by clicking the mouse button and holding it down to highlight before releasing the mouse button.
4. Click the 'Insert' tab in the top toolbar and click the button for the desired chart type in the 'Charts' group (such as 'Column'). Note: additional options will be displayed for that chart type; select a thumbnail image for the desired presentation of the first data series (the second column).
5. Check that the chart is inserted on the same sheet as the data.
6. Click the second set of data on the chart from the third column (the second colored set of data).
7. Right-click the data series and select 'Format Data Series.'
8. Select the category in the left-hand frame for 'Series Options.'
9. Click the option for 'Secondary Axis' in the section, 'Plot Series On' and click the 'Close' button.
10. Click the 'Insert' tab in the top toolbar.
11. Select a chart type in the 'Charts' group for the selected data series (such as line). Note: additional options for the selected chart type will be displayed; select the thumbnail to present the data series in the third column.

How to Change the Default Font in Microsoft Excel 2007

1.
Click on the 'Office' button and choose the 'Excel Options' button toward the bottom of the menu.
2.
When the 'Excel Options' dialog box opens, make sure that the 'Popular' tab on the left is selected. In the center of the window you will see the font options. You can select the options you would like to become your new defaults.
3. When you have finished making your selections, click the 'OK' button on the bottom of the dialog box. Your choices will now be the new default font.

How to Change Text Based on CheckBox Value in Excel

1. Open the Excel 2010 workbook that contains the checkbox that you want to manipulate.
2. Click the 'Developer' tab at the top of the screen. Click the 'Design Mode' button in the middle of the ribbon. This lets you interact with your checkbox.
3. Click on the checkbox to select it. Then, click the 'Properties' button in the 'Controls' area of the ribbon. A small Properties window will open up. Take note of the entry in the 'Name' field at the top of this window, which is usually 'CheckBox1,' or something similar. This is the name of the object, and you will need to know it to write your code. Click the 'X' to close the Properties window.
4. Right-click the checkbox and choose 'View Code' from the pop-up menu. The VBA editor window will appear, with the line 'Private Sub CheckBox1_Click()' already entered into the editor. Place your cursor on the line directly beneath this first one.
5. Enter the following code into the cell:If CheckBox1.Value Then[A1] = 'XXX'Else[A1] = 'YYY'End IfChange 'CheckBox1' to your checkbox's name. Change 'A1' to whatever cell you want the checkbox to manipulate. Change 'XXX' to whatever text you want in the cell when the checkbox has a check in it, and 'YYY' to whatever text you want in the cell when the checkbox is blank. To have one of the options leave a blank cell, just remove the letters but leave the quotes in place.
6. Click the 'X' to close the VBA editor. Click the 'Design Mode' button to turn it off. You can now add or remove a check mark from the checkbox, and your desired cell's text will change.

How to Break the Y Axis on Excel

1. Launch Excel and open a new workbook. For illustration purposes, type “10,” “12,” “13,” “500” in the first column from cells A1 through A4. If you created a chart from these values, the last value would overwhelm the chart, making the smaller numbers nearly indistinguishable.
2. Highlight the column of numbers by dragging the cursor over them. Press “Ctrl-C” to copy them. Click cell “B1” and press “Ctrl-V” to paste them in the second column. Click cell “B4” and change the value from “500” to “50.” Adding this column allows you to customize the chart without changing the original data in your worksheet.
3. Drag the cursor across cells “B1” to “B4” to highlight them. Click the “Insert” menu and select “Chart.” Select a “Column” chart and click “Next.” Continue clicking “Next until you get to the “Chart Location” menu. Select “As a New Sheet” and click “Finish.”
4. Click on the background to open the Format Plot Area dialog box. Click the “Color” menu and change it to white. Click 'OK.'
5. Click the “Insert” menu and select “Autoshapes.” Click the “Line” tool. Drag the tool diagonally across the middle of the last bar. Double-click the line. Click the “Color” menu and select white. Type “20” in the Weight text field. Click “OK.”
6. Click the “Text Box” tool. Drag it beside “10” on the y-axis and type “10”. Draw text boxes beside each of the other numbers in the y-axis, typing “500” beside “50.”
7. Double-click a number in the y-axis. Click the “Color and Lines” tab. Select “None” in the “Tick Mark Labels” section. Click “OK.” The numbers in the y-axis disappear, leaving the numbers you typed in the text boxes as the y-axis values.

How to Convert Multiple Excel Sheets to PDF

1. Create your Excel file sheets. Save all the sheets as a PDF by clicking the first sheet and holding down the 'shift' key and clicking on the last sheet. All sheets in between will be selected.To select some but not all sheets, click the first sheet you want to convert and hold down the 'ctrl' key and click the other sheets to convert.
2. Click 'File' at the top of the Excel screen. Click 'Save As.'
3. Name the file. Choose 'PDF' from the document type drop down menu.
4. Click 'Save.' The Excel sheets have been saved as a PDF.

Tuesday, June 14, 2011

How to Print Avery Labels in Excel 2007

1. Navigate to Avery's Avery Wizard website.
2. Click the 'Free Download' link in the center of the page. The download should start automatically. If it doesn't click the 'click here' link.
3. Install Avery Wizard by following the on screen instructions.
4. Open Word 2007, then click the 'Avery' tab.
5. Click the 'Avery Wizard.'
6. Follow the instructions in the wizard to select your Avery product. The wizard will prompt you to tell it where the data is saved. Click 'Merge data from existing file' and select the location of your Excel file.
7. Preview your file and print the labels by completing the rest of the wizard.

How to Use Indirect in Excel 2007 to Link to Another Worksheet

1. Create a tab for links to external sources by right clicking on the tab named Sheet1 and selecting 'Create New Tab.' Name it 'Links.'
2. Enter an '=' in the formula bar in the cell you want the data imported into, and then switch to the spreadsheet you're pulling data from, and click on the cell the data is in. This will create a formula in the form of ='[Accounting.xls]Sheet1'!$A$1 where '[Accounting.xls]' is replaced with the name of the spreadsheet, 'Sheet1' is replaced with the name of the tab in the work sheet you're pulling data from, and '$A$1' is replaced with the reference to the cell the data resides in.
3. Select the cell you just made the reference in. Click on the Formulas tab in Excel 2007 and choose Name Manager. In earlier versions of Excel, go to the Formulas menu and select 'Names.' Assign a name to Links!A1 that will be easy to remember. For this example, choose 'Accounting.'
4. Enter the following formula to reference the data: '=INDIRECT(Accounting)'. This will display the information in the cell with the name Accounting.

How to Hide the X Axis on MS Excel

1. Click on the chart you want to edit in Excel.
2. Click 'Axes' on the 'Layout' tab at the top of the Excel application.
3. Click 'Primary Horizontal Axis,' then 'None.'

How to Delete the VBA Project

1. Launch any program which is part of the Microsoft Office suite. Common Microsoft Office programs include Word, Excel, PowerPoint, Access and Outlook.
2. Press the ALT and F11 keys simultaneously on your keyboard. This will open the VBA editor.
3. Right-click in the left pane on the name of the VBA project you would like to delete and select 'Remove.' The VBA editor will now delete that project from your system.

Monday, June 13, 2011

How to Create a ZIP File for Excel Spreadsheet

1. Right-click 'Start' and then click 'Explore.'
2. Right-click on the Excel file and then point to 'Send To.'
3. Click 'Compressed (ZIP) Folder.'
4. Double-click the ZIP folder and then click 'Extract all files' in order to open it as a 'Read-Only' file. (Note: If you would like to extract the ZIP folder and make a copy of it, click 'Extract all files' after you double-click it. Follow the steps in the wizard to complete the process.)

Sunday, June 12, 2011

How to Insert a Watermark Into Excel 2007

1. Open Excel 2007 by clicking the Windows Start icon, selecting the 'All Programs' link and clicking the 'Microsoft Office' folder. Click 'Microsoft Excel 2007' to open the program. Click the Microsoft Office icon and select 'Open' to open an existing spreadsheet, or click 'New' to create a new spreadsheet.
2. Click the 'Insert' tab on the top navigation bar, located above the navigation ribbon.
3. Select the 'WordArt' menu from the Text group.
4. Click a style to use for the word art. You can choose any style that appeals to you or is appropriate for the type of document you are watermarking.
5. Type the word you wish to use for the watermark. For example, type 'Draft.'
6. Click the 'Format' tab on the navigation ribbon if it is not already visible, and then click 'Text Fill' in the WordArt group. Select 'No Fill' from the Text Fill drop-down list.
7. Select 'Text Outline' in the WordArt group on the navigation ribbon. Then select the 'Automatic' option from the drop-down options.
8. Click 'Send to Back' in the Format tab's Arrange group on the navigation ribbon. The watermark appears behind the text and characters in the Excel 2007 spreadsheet.

How To Remove Data From the External Data Range in Excel 2007

1. Select the range of data to be removed from the external data range within the spreadsheet.
2. Click on the 'Data' tab. Click on the arrow next to the 'Refresh' field in the 'Connection' group.
3. Click on the 'Connection Properties' option and then click on the 'Usage' tab. Click on the box next to the 'Remove data from the external data range before saving the workbook' field.
4. Close out of the dialog box and click on the 'Refresh Data' option from the 'Data' tab. The data will be fully removed from the external data range.

How to Rename an Excel Worksheet

1. Open Microsoft Excel and the file you want to change.
2. Double-click on the tab for the worksheet that you want to rename. (The tabs are at the bottom of the Excel window.) The sheet's name becomes highlighted.
3. Type in the new worksheet name.
4. Press Enter to accept the changes.

How to View Two Excel 2007 Spreadsheets on Separate Screens

Excel's 'Side-by-Side' View
1. Open the first Microsoft Excel 2007 file with which you want to work.
2. Open a second instance of Excel 2007. Go to the 'Start' menu in the lower left-hand corner of the computer desktop, choose the 'All Programs' list and click on Microsoft Office Excel 2007. Click the 'Office' button in the program, select 'Open,' and double-click on the second file that you want to open and view.
3. Click the 'View' Ribbon in Excel 2007. Click the 'View Side by Side' option in the 'Window' group to view the spreadsheets stacked vertically side-by-side. Click the 'Synchronous Scrolling' button in the 'Window' group to control the scrollbar separately for each spreadsheet file. Click the 'Full Screen' option in the 'Workbook Views' group to expand the viewing area. Right-click anywhere on the screen and choose 'Close Full Screen' to close this view.
4. Click 'Save Workspace' in the 'Window' group from the 'View' Ribbon. Type a name for the file and a directory location and click 'Save.' Re-opening this file saves the side-by-side view of the two files.
Split Screen Option (Windows 7)
5. Open the first Excel 2007 file which you want to view. Click the 'Maximize' button in the top-right corner of the Excel screen.
6. Open the second Excel 2007 file you want to view in a new instance of the Excel program. To open a new instance of the program, either click the Excel application from the 'All Programs' list within the 'Start' menu or right-click the Excel 2007 shortcut on your computer desktop or Taskbar and choose 'Microsoft Office Excel 2007.' In the new instance of the program, open the second file you want to view. Click the 'Maximize' button in the program to maximize the file's view.
7. View the files in split screen mode. With the second Excel 2007 file open, maximized and selected, press first the Windows logo key in the lower left corner of the keyboard. Hold the Windows logo key down and then press the 'right' arrow key to shift the file to the right side of the screen. Click once on the first Excel 2007 file to select it. Repeat the process of holding the Windows logo and the 'left' arrow keyboard keys to place the file on the left side of the screen.

Saturday, June 11, 2011

How to Fix an Excel 2007 File That Crashes When Saving

1. Open Excel 2007 and click the 'Microsoft Office Button.'
2. Select 'Open,' and browse to the file you would like to repair.
3. Select the drop-down arrow next to the Open button to view the open options.
4. Select 'Open and Repair' and then 'Repair' if you want to repair as much of the file as possible. Select 'Extract Data' if repairing the file does not work.
5. Click the 'Microsoft Office Button' and then choose 'Excel Options.' Select the 'Save' option and make sure the 'Save AutoRecover Information' box is checked as a pro-active measure against file corruption.
6. Select the 'Microsoft Office Button' and then 'Save As.' Select 'Tools' and then 'General Options.' Check the box 'Automatically Create Backup' as a pro-active measure in the event that your workbook becomes unrepairable.

How to Use Multiple Logical Functions in MS Excel

1. Make a list of all conditions to test for in the correct order. For example, in a scenario where a customer who spends $100 or more receives a 20 percent discount if they are a member of your store, but only 10 percent if they are not a member, the conditions to test for are has the customer spent $100 or more and is the customer a member?
2. Decide which of the six functions you will need to use. See the link in the Resources section for a description of each function and what it returns. In our example, we need to use the IF function to perform a logical test. The AND function will be nested in the IF function to test whether the customer has spent at least $100 and if he is a member. The OR function will determine if the customer has met one of the conditions to qualify for a discount.
3. Structure the formula beginning with the IF statement and nest the other functions. In the example above, set up the worksheet as follows: enter 20 percent in cell B1 for membership discount, 10 percent in cell B2 for non-membership discount, and $100 for the minimum purchase in cell B3. List the names of the customers starting in cell A6, each customer's purchase amount in B6 and Membership status ('Yes' or 'No') in C6.
4. Enter the following formula in D6: =IF((AND(C6='Yes',B6>=$B$3)),B6*$B$1, IF((OR(B6>=$B$3)),B6*$B$2,'No Discount')))
This AND function tests whether the customer is a member and if she has spent at least $100. If both conditions are true, multiply the purchase amount by 20 percent to determine what the discount is. The OR function tests whether the customer has spent at least $100 even if she is not a member. If she meets this condition, multiply the purchase amount by 10 percent. If none of the conditions are true, the cell will display 'No Discount.'

How to Sort Alphabetically in Excel

1. Create your spreadsheet using the Microsoft Excel columns. Place the names you want organized in one column, with one name in each cell. In the soccer example, you could list them 'Last name, First name,' such as in this example: 'Robbins, Johnny,' 'Ferguson, Anne,' and so on.
2. Select a cell from the column you want to organize alphabetically by left-clicking. The soccer spreadsheet will be organized by last names, and you could click 'Robbins, Johnny,' 'Ferguson, Anne,' or any of the listed names.
3. Choose the 'Sort A to Z' button to sort in ascending alphanumeric order or the 'Sort Z to A' button to sort in descending alphanumeric order in the top task bar. If you chose 'Robbins, Johnny' in the soccer example and clicked the 'Sort A to Z button', it would sort: 'Adams, Douglas,' as the first name and 'Zahn, Monty' as the last name and 'Robbins, Johnny,' somewhere in between.

How to Print Comments in Excel

1. Start Microsoft Excel and open a spreadsheet that contains comments that you would like to print along with your spreadsheet.
2. Choose the 'File' menu and click 'Page Setup' to bring up the 'Page Setup' dialog box. Within the 'Page Setup' dialog box, you can make different changes that will affect the pages of your spreadsheet.
3. Click on the 'Sheet' tab at the top of the 'Page Setup' dialog box to show the sheet page setup options.
4. Locate the 'Print' section of the 'Sheet' options, in the center of the 'Page Setup' dialog box.
5. Select either 'At end of sheet' or 'As displayed on sheet' options from the 'Comments' drop-down menu. The 'At end of sheet' option will print the comments from the spreadsheet at the end of each sheet, and the 'As displayed on sheet' option will print the comments just as they appear on the spreadsheet while it's open on your screen.
6. Press the 'OK' button with your mouse to close the 'Page Setup' dialog box and set Excel to print comments that appear on your spreadsheets.
7. Print the spreadsheet by choosing the 'File' menu, clicking on 'Print' and then printing from the 'Print' dialog box.

Subscribe to: Posts (Atom)