Microsoft Excel: How to Protect My Formulas in Excel Sheets

1. Open Excel and type a formula in cell A1. Right click on the cell and select 'Format Cells.' Select the 'Protection' tab. Check the option for 'Hidden.' Click 'OK.' The hidden option lets you hide the content of the cells but it requires that the worksheet be protected for this to be activated.
2. Select the 'Review' tab and locate the Changes group. Within the Changes group, select 'Protect Sheet.' Make sure the 'Protect Worksheet and contents of lock cells' option is checked. Type a password in the 'Password to Unprotect Sheet' field. Confirm the password at the 'Confirm Password' prompt. Write this password in a secure location so you don't forget it. Click 'OK' to confirm the password protection.
3. Verify the password protection by going to cell A1. Look at the formula bar and notice it is blank. If you send this workbook to other users, they will get the same result. Your formula is protected and not visible unless they have the password. Do this for each cell or range where you want to protect your formulas.

Read more ►

How to Manage Range Names in Excel

1. Start Microsoft Excel and open an existing spreadsheet that contains range names that you would like to manage, filter, edit, delete or create additional range names.
2. Choose the 'Formulas' tab to display the 'Formulas' ribbon. Click the 'Name Manager' button to open the 'Name Manager' dialog box.
3. Click the 'New' button in the 'Name Manager' dialog box to open the 'New Name' dialog box. In this dialog box, you can provide a name, scope, comment, and cell reference for the new range name that you create.
4. Select a range name you would like to edit from the 'Name Manager' list and click 'Edit' to open the 'Edit Name' dialog box. Here you can rename the range, change the scope, edit the comment or change the cell reference.
5. Identify a range name you would like to delete from the 'Name Manager' list and click the 'Delete' button. A dialog box will come up and ask you to confirm that you want to delete the name range. Click the 'OK' button to delete the name range from the 'Name Manager' dialog box.
6. Highlight and click the 'Filter' button on the top right of the 'Name Manager' dialog box to display the filter options available for the name manager. You can filter to find range names that are located in the current workbook, that are located in the current worksheet, that have errors, that don't have any errors, defined names and table names.
7. Complete the process by clicking on the 'Close' button. This will close the 'Name Manager' dialog box after you have finished managing the range names.

Read more ►

How to Create a Money Graph by Using Microsoft Excel 2003

1. Enter the data for your graph in a new or existing spreadsheet in Microsoft Excel 2003. Include a relevant title for each monetary amount as this is the information that will appear on your graph and help you identify what each number is. For example, on a graph comparing your various household expenses, you may list 'groceries, rent and utilities' with the amount for each in the cell beside the title.
2. Format the numbers in your graph as currency. Highlight the numbers and click 'Format' in the menu bar, then select 'Cells.' In the number tab, set the category to 'Currency.' You can then set the number of decimal points you want behind the numbers and the currency symbol that you prefer.
3. Highlight the numbers and titles for the numbers that you want to include in your money graph. Click on the 'Chart Wizard' icon in the toolbar.
4. Select the type of chart that you want to create from the window that will pop up for the chart wizard. You can create all the standard types of graphs, such as column graphs, bar graphs, line graphs and pie charts. Other options include an XY scatter, doughnut graph, bubble graph or stock graph. If you are graphing your expenses over a period of months, a line graph will quickly show you if they've gone up or down. A pie chart is an effective way to see how big of a chunk each individual expense is taking out of the total.
5. Choose the chart sub-type for your selected chart. This determines exactly how the finished chart will look. Most chart types are available flat or with various 3-D effects.
6. Click 'Next' to continue to the next step of creating your graph. Check the data range to make sure you are using the correct information for your chart. Identify whether the series in the selected data runs in the row or column. If the titles for each amount run horizontally along the top of the page with the numbers beneath, your series is in rows. If the labels are in a vertical list with the numbers set next to each label, you have a series in columns.
7. Select 'Next' and enter a title for your chart. You can also label the X and Y axis and determine where the legend for your graph will appear on the finished page.
8. Go to the next page of the set up and determine the page where your graph will appear. Click on the graph to open the chart menu. You can make additional changes to the graph here if you are unsatisfied with your results.

Read more ►

How to Link Sheets Using Visual Basic in Excel

1. Press 'Alt' and 'F11' at the same time to open the Visual Basic editor from your Excel worksheet.
2. Click 'Insert > Module' to open a blank code window.
3. Insert the following code (up to 'End Sub') into the blank window:Sub GetValue()
Range('A1').Value = '=[workbook_name.xls]Sheet1!B1'
End SubThis code fetches the value of A1 from the sheet Sheet1 in the workbook workbook_name.xls, then places that value into cell A1 of the currently active sheet.
4. Press 'F5' to display the Macros dialog. The macro name should already be filled in as 'GetValue.'
5. Click 'Run' to execute the VBA code linking the two workbooks together. You should now see the value from A1 in workbook_name.xls in cell A1 of your currently open worksheet.

Read more ►

How to Add a Dial Chart to Excel 2010

1. Go to the Microsoft Office Templates site at office.microsoft.com, type “dial chart” into the search field, click the drop-down arrow next to the search button and select “Excel.” Click the “Search” button.
2. Click one of the dial chart thumbnails and click the “Download” button. The dial chart opens in Excel.
3. Edit the data that the dial chart represents, as necessary. Generally, the “Max Value” field displays the maximum number listed on the gauge, and the “Indicator Line” filed dictates value, which is where the dial line displays.

Read more ►

How to Embed Excel Files into Word

1. Open Microsoft Word. You can either open a blank document or an existing document.
2. Click on the location in your document where you wish to embed your spreadsheet.
3. Click the 'Insert' tab and select 'Object.'
4. Click the 'Create from File' to browse for an existing Excel file. Navigate to and select the Excel spreadsheet. Choose to display the spreadsheet as an icon or link as well as the full object.
5. Click 'OK' to embed your object. If you didn’t choose to display the spreadsheet as an icon or link, you will see the first sheet of the workbook in your Word document. Click the spreadsheet object to view any other sheets and also to change data.

Read more ►

How to Disable Hyperlink Warning Messages in Office 2007

1. Creating a system restore point is recommended (See Resources).
2. Click the 'Start' button.
3. Type 'regedit' in the search box. Press 'Enter.'
4. Click 'Continue' if you get a 'User Account Control' window.
5. Press 'F3' on the keyboard to open the 'Find' window.
6. Type 'HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Common' and click 'Find Next.' If this text is not found, press 'ok,' then F3 again. Type 'HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common' and click 'Find Next.'
7. Look for a sub-key named 'Security'. If this exists, click on it. If not, create it by clicking 'Edit', 'New' and then 'Key.' Type 'Security' and press enter.
8. Click 'Edit,' 'New,' and then 'DWORD' value. Type 'DisableHyperlinkWarning' and click 'Modify'.
9. Select 'Decimal' and type '1' for the value. Click 'OK.'
10. Close the registry editor and reboot the computer.

Read more ►

How to View Excel 2007 Files on Excel 2003

1. Click to download the 'Microsoft Office Compatibility Pack' from the Resources section below. Choose a directory on your local hard drive to save the file and click the 'Save' button.
2. Double-click the downloaded executable to run it. Read the license terms, then click to place a check in the box next to 'Click Here To Accept The Microsoft Software License Terms.' Click the 'Continue' button.
3. Click the 'OK' button when the Compatibility Pack has completed the installation process.
4. Launch the Microsoft Excel 2003 program by clicking the 'Start' menu button. Click 'All Programs', then 'Microsoft Office', and click on 'Microsoft Office Excel 2003' program shortcut.
5. Click the 'File' menu and choose the 'Open' menu option. Click on the Excel 2007 spreadsheet file to select it in the 'Open' dialog box, then click the 'Open' button to open the file.

Read more ►

How to Protect a Single Cell in Excel 2007

1. Click any cell in Excel, hold the 'Ctrl' key and press 'A' to select all cells. Alternatively, click the triangle at the top left of the worksheet, located just above the row numbers and to the left of the column letters.
2. Right-click any selected cell, and click 'Format Cells.'
3. Click the 'Protection' tab, uncheck 'Locked' and click 'OK.'
4. Click any cell to deselect all the other cells.
5. Right-click the single cell you wish to protect, and click 'Format Cells.'
6. Click the 'Protection' tab, check 'Locked' and click 'OK.'
7. Click the top 'Review' tab, and click 'Protect Sheet' in the 'Changes' group.
8. Enter a password in the 'Password to Unprotect Sheet' field of the 'Protect Sheet' dialog window, and click 'OK.'
9. Re-enter the password in the confirmation box and click 'OK.' That single cell is now protected. To unprotect it, click 'Unprotect Sheet' in the 'Changes' group of the 'Review' tab, enter the password and click 'OK.'

Read more ►

How to Put a Signature on Microsoft Excel

Manual Signature
1. Open Microsoft Excel. Click the 'File' tab and select 'Open,' then browse to the spreadsheet and double click the file.
2. Click the 'Insert' tab. Click 'Shapes' in the 'Illustrations' section.
3. Click the squiggly line 'Scribble' tool in the 'Line' section. The cursor changes to a pencil.
4. Position the cursor on the spreadsheet, then click and hold down the left mouse button and draw a signature of your first name with the Scribble tool. Release the left mouse button when finished. Repeat the process of clicking the 'Shapes' button, selecting 'Scribble' and clicking the cursor to draw your last name next to the first name box.
5. Change the signature color -- depending on your settings Excel's default may be light blue -- by clicking the new orange 'Drawing Tools' tab at the top of the work area. Click the 'Shape Outline' button. Click a small colored square, such as black or dark blue, to instantly change the color. Repeat for the last name and any further designations.
Import Signature
6. Open Microsoft Excel. Click the 'File' tab, select 'Open,' browse to the spreadsheet and double click.
7. Click the 'Insert' tab at the top of the work area. Click the 'Picture' button in the 'Illustrations' section.
8. Browse to the previously-created graphic file containing your signature. Double click the file name to add the signature image to your Excel spreadsheet.

Read more ►

How to Copy Workbooks in VBA

1. Open Microsoft Excel. Create a workbook, and name it 'Book1.xls.' Populate the cells with information, and save it to your local disk C: drive.
2. Create another workbook, and name it 'Book2.xls.' Leave it blank, and save it to your local disk C: drive. Close Excel.
3. Open Microsoft Word. Go to 'Tools,' 'Macro' and then Click 'Visual Basic.' This will open the Visual Basic programming environment.
4. Go up to 'Insert' and choose 'Userform.' This will create a Userform.
5. Double-click on the Userform, and paste the following code: FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'Here is an example of what your code should look like:Private Sub UserForm_Click()FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'End Sub
6. Press the 'F5' key on your keyboard to run the program. The information in workbook 'Book1.xls' should now be copied to 'Book2.xls' on your local C: drive.

Read more ►

How to Add a Trendline With Excel 2007

1. Select the chart to which you want to add the trendline. Click once on the data series for which you want the trendline to appear.
2. Click the 'Add Trendline' option on the 'Chart' menu. This action will open a separate pop up window.
3. Click the 'Type' tab. Select the type of trendline that you want to add from the list of available options. Click the 'OK' button to add the trendline to the chart.

Read more ►

How to Center Worksheets Both Horizontally Vertically in Windows Excel

1. Open your worksheet in Microsoft Excel by clicking the 'Office' button in the upper left corner and selecting 'Open.' Navigate to where your workbook is located and click 'Open' to display the worksheet.
2. Click the 'Page Layout' tab and click 'Margins' in the 'Page Setup' group
3. Click 'Custom Margins' to display the 'Page Setup' settings.
4. Check the boxes located next to 'Horizontally' and 'Vertically' under 'Center on Page' to center your worksheet. Click 'OK' to close the 'Page Setup' box.

Read more ►

How to Do a Cumulative Total on Excel

1. Open the Excel 2010 file in which you want to create a cumulative total.
2. Enter, or move, the numbers you are going to sum into a single column. Ensure that this column has an empty column to the right of it.
3. Select the cell at the top of the empty column to the right of the values. Place an equal sign (=) into the cell to start a formula, then click on the cell at the top of the column with the values to place that cell reference into the formula. Press 'Enter' to complete the formula, and the cell will display the exact same value as the top cell of the original columns.
4. Click the cell directly below the cell where you entered the formula. Press the '=' button to start a new formula. Click the cell at the top of this column to place its coordinates into the formula. Then press the plus sign ( ) sign and click the cell to the left of the cell you are entering a formula in. Press 'Enter' to complete the formula.
5. Move your mouse to the bottom-right corner of the cell you just placed your formula in. When your mouse changes to a plus sign, click and hold the mouse button. Drag your mouse down the column to the last cell where you want to create the cumulative total. Release the mouse button, and the entire column will change to show the cumulative sum for the first column.

Read more ►

How to Merge Documents in Excel 2007

1. Create an Excel document and then click on the 'Review' tab in the 'Changes' group. Click the option 'Share Workbook.' Click the 'Editing' tab and check the box next to the option to 'Allow changes by more than one user at the same time.'
2. Create a folder on your PC's desktop by right-clicking on your desktop. Save the original Excel document to this new folder by clicking 'Save As' and then selecting the new folder on your desktop as the destination for the Excel document. Save the copy of the Excel document you want to merge into the original document in the same folder on your PC's desktop. Make sure the two files have different file names.
3. Open the original Excel document. Click 'Tools' from the Excel window's toolbar. Select 'Compare and Merge Workbooks.' A dialog box will open and ask you to 'select the files to merge into current workbook.' Select the document you would like to merge into the original Excel document. Click 'Ok.'

Read more ►