How to Extend Margins in Excel

1. Open the Microsoft Excel 2010 spreadsheet that you want to alter.
2. Click the 'Page Layout' tab at the top of the screen. Locate the 'Page Setup' area on the ribbon and click the 'Margins' button found there. Click 'Custom Margins' from the menu that appears.
3. Click the up and down arrows next to each of the four major margins to adjust the margin for that side of the paper. You can also adjust the margins for the header and footer, making them larger or making them so small that they disappear.
4. Click the 'Print Preview' button once you have extended your margins, to ensure that the final product has the look that you want. If the margins are not correct, click the 'Page Layout' tab again and choose 'Custom Margins' again to go back to the margins screen.

Read more ►

How to Convert Excel Field to Boolean Type

1. Click 'File.'
2. Click 'Options.' This opens the 'Excel Options' window.
3. Click 'Customize ribbon.'
4. Check the box next to 'Developer' in the pane on the right. Click 'OK.'
5. Click 'Developer' from the menu bar.
6. Click 'Insert' from the 'Controls' tab.
7. Click the icon for a check box from the 'form options' section.
8. Click on the cell to which you want the add the boolean value.

Read more ►

How to Set Up a Default Email Program With MS Excel

Internet Explorer
1. Open Internet Explorer.
2. Click 'Tools' then 'Internet Options.'
3. Click the tab labeled 'Programs.'
4. Select your e-mail program under 'E-mail.'
5. Click 'OK.'
Control Pannel
6. Click the 'Start' menu.
7. Select 'Control Panel.'
8. Select 'Network and Internet.'
9. Click 'Internet Options.' Click the tab labeled 'Programs.'
10. Select your e-mail program under 'E-mail.' Click 'OK.'

Read more ►

How to Create a Summary Workbook Excel 2003

1. Type a description of the information that you want to display as a summary on Sheet 1. If you have an income statement on Sheet 2, for example, you can type 'Net Income' in cell A1.
2. Go to the cell you want to show on your summary page. In the above example, you would go to the cell that has net income on Sheet 2.
3. Click 'Copy.'
4. Go back to the summary sheet and click where you want to display the link. In the example, go to cell A2.
5. Click 'Paste' then 'Paste Special.' Choose 'Paste Link.'
6. Repeat these steps for all of the information that you want to include on your summary page.

Read more ►

How to Subtract Dates in Microsoft Excel 2003

1. Start Microsoft Excel 2003 and open the workbook containing the dates you would like to subtract.
2. Click an empty cell where you would like the results to appear.
3. Identify the cells containing the start date (for example, A2) and end date (for example, B2). Type without quotes: '=B2-A2' and substitute your actual cell references for the examples used here. Press Enter.

Read more ►

How to Calculate Weeks Days Between Two Given Dates in Excel 2003

1. Left-click on the cell you want the result to formulate within.
2. Type in the formula in the cell you selected in step 1 to calculate the amount of days. For example, if the two dates are A2 ('6/9/2004') and A3 ('8/21/2004'), you would subtract A3 from A2 to find out the days and weeks in between. The formula would look like this: ' =(A3 - A2) '
3. Press the 'Enter' key on your keyboard to formulate the result. For example, if you enter the formula using the data in step 2, you would end up with 72 days between the two dates.
4. Select another cell where you wish to input your formula to convert the number of days to weeks.
5. Enter the formula into the newly selected cell beginning with the ' = ' sign and clicking on the cell you selected in step 1. Add a '/' sign followed by the number '7.' This will take the result you found in step 1 and divide it by 7 to find the amount of weeks between the two dates.
6. Press the 'Enter' key on your keyboard to find the number of weeks between the two dates.

Read more ►

How to Change From Sum to Count in a Pivot Table

1. Open the PivotTable report in your Microsoft Excel application. Click any cell in the data area so that it’s highlighted.
2. Click the “Field Settings” option on the PivotTable toolbar. The “Sum” option will be selected by default in the PivotTable Field dialog box.
3. Click the “Count” option in the “Summarize by” box. The data will now be summarized by 'count' instead of 'sum.'
4. Click the “Options” button to change any other field settings, such as adding a custom calculation.
5. Click the “OK” button in the PivotTable Field dialog box to save all of your changes.

Read more ►

How to Use Error Bars in Excel

Excel 2007
1. Click the chart you want you want to add an error bar to.
2. Click the 'Chart Tools > Layout' tab.
3. Click 'Error Bars' in the Analysis section of the Layout tab.
4. Click the type of error bars you want to add. You may choose error bars with Standard Error, Percentages, or Standard Deviation.
5. Select the display type for the error bars in the Format Error Bars dialog.
6. Set the error amount in the appropriate text box.
7. Click 'OK' to exit the Format Error Bars dialog.
Excel 2003
8. Double click the chart series you want to add an error bar to.
9. Choose the 'Y Error Bars' or 'X Error Bars' rab in the Format Data Series dialog, depending on which axis you want to add the bars to.
10. Choose the 'Display' type you want by clicking the appropriate icon, and set the Error Amount values in the appropriate text box.
11. Click 'OK' to the close the Format Data Series dialog.

Read more ►

How to Add a Section Line to the Menu for VBA With Excel

1. Start Excel 2003.
2. Open the Visual Basic Editor. Select 'Tools,' then 'Macro,' then 'Visual Basic Editor' from the menu.
3. Insert a blank module for your code. Select 'Insert,' then 'Module' from the menu.
4. Start a new subprocedure. Type the following:Sub CustomMenu()Excel will automatically add an 'End Sub' statement.
5. Define variables. Type the following between the 'Sub' and 'End Sub' statements:Dim MenuObject As CommandBarPopupDim MenuItem As Object
6. Delete any existing instances of your custom menu. This prevents duplicates if you run the code more than once. Type the following:On Error Resume NextApplication.CommandBars(1).Controls('My Macro').DeleteOn Error GoTo 0
7. Create a new menu option called 'My Macro.' Use a parameter value of 10 in the 'Set' statement to place it before 'Help,' the 10th item on the Excel menu. Type the following:Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=10, temporary:=True)MenuObject.Caption = 'My Macro'
8. Add two items to the menu, with a section line between them. Create the section line by setting the 'BeginGroup' property in the second item to 'True.'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'MacroName'MenuItem.Caption = 'Run'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'About'MenuItem.Caption = 'About Macro'MenuItem.BeginGroup = True
9. Run the code. Select 'Run,' then 'Run Sub/UserForm' from the menu. When you return to the spreadsheet, you will see 'My Macro' on the menu, prior to 'Help.' Click to view the two menu options, separated by a section line.

Read more ►

How to Insert Multiple Rows in Excel 2007

1. Open the appropriate Microsoft Excel 2007 worksheet on your computer.
2. Use your mouse to highlight the number of rows below the area where you want to insert new rows. For example, highlight five rows if you want to insert five more rows above that group.
3. Click on the 'Home' tab and then click on the arrow next to the 'Insert' field in the 'Cells' group.
4. Click on the 'Insert Sheet Rows' option and then multiple rows will be inserted above your highlighted cells.
5. Click on the 'Microsoft Office' button from the top toolbar ribbon menu and then click on the 'Save' option so that your new changes are saved.

Read more ►

How to Adjust Baseline Shift in MS Word

1. Launch Microsoft Word 2007. Click on the 'Home' tab.
2. Click the 'Show Font Dialog' box located in the 'Font' section of the 'Home' tab.
3. Click the 'Character Spacing' tab.
4. Select the type of baseline shift you would like to use from the 'Position' drop-down box. 'Normal' keeps all text aligned and straight. 'Raised' is ideal for creating superscripts, and 'lowered' is ideal for creating subscripts.
5. Click 'OK' to save your changes and exit the window.

Read more ►

How to Display Currency in Words for Microsoft Excel

1. Launch Microsoft Excel and open a spreadsheet.
2. Create a new module by pressing Alt F11 and selecting “Module” under the “Insert” menu. A new window will appear.
3. Open a web browser and go to ozgrid.com/VBA/CurrencyToWords.htm.
4. Highlight and copy, by pressing Ctrl C, the blue text that begins with “Function ConvertCurrency...” and ends with “End Function” near the very bottom of the web page.
5. Paste the copied text into the module window by pressing Ctrl V. This will create a new function that you can now use in Excel.
6. Go to the cell that you wish to display currency in words.
7. Type in “=ConvertCurrencyToEnglish()” with the numerical value of the currency between the brackets. The currency will be converted to words. For instance, “=ConvertCurrencyToEnglish(12.30)” will display as “Twelve Dollars And Thirty Cents.”

Read more ►

How to Remove Blank Rows in Excel

Deleting A Small Number of Rows
1. Save your spreadsheet.
2. Hold the 'Control' key.
3. Left-click the number of each blank row you want to delete to highlight each one. Make sure you continue holding 'Control' down as you select the rows.
4. Right-click on one of the highlighted rows and select 'Delete.'
Deleting Many Blank Rows
5. Save your spreadsheet.
6. Press the F5 key, then click 'Special.'
7. Select 'Blanks' and press 'OK.'
8. Click 'Home,' then 'Delete' under the cell options. (Or click 'Edit' and 'Delete' for older versions of Excel) Note that using this method will remove all blank cells and move the data up, so if there are different blank fields in some rows it may cause data to become disorganized. Always save your spreadsheet before performing operations so you can revert back to old versions if necessary.
Removing Blanks by Sorting
9. Save your spreadsheet.
10. Highlight all the cells that you want to sort.
11. Click 'Data,' then 'Sort.'
12. Under 'Sort By,' select 'Column A,' then click 'OK.' This will sort the data by the values in column A, leaving any rows without data values for column A at the bottom of the sorted list, essentially deleting them.

Read more ►

How to Compare Files in Microsoft Excel

Compare by Size
1. Save the file in comma separated values format. Use similar file names, such as file1.csv and file2.csv. This method only works for worksheets containing data, not formulas.
2. Go to the start menu and select 'Accessories,' select 'Command Prompt.'
3. Change the directory of the command prompt to the directory where the files are stored. For example, it will start with 'C:\Users\Joe.' Type 'cd Documents' to work within the Documents folder; the directory prompt will then read 'C:\Users\Joe\Documents.'
4. Type 'comp file1.csv file2.csv' using your filenames from the folder where the files are stored. If they are the same size, command prompt will assume they are the same and tell you that 'Files compare OK.' If they are not, command prompt will advise 'Files are different sizes.'
Compare Lists With a Macro
5. Open the Visual Basic Editor and select the primary file from the left menu as VBAProject[file1.xlsm].' Select 'Insert' and 'Module' from the menu at the top.
6. Create a macro in visual basic that will run through an if/then loop comparing the text of each cell in the primary file with a secondary file. Give it a logical name, such as 'CompareFiles().' The code must output a list of data that is different in a new sheet in the primary file. Close the Visual Basic Editor and save the file.
7. Click on the 'View' tab at the top of the main navigation; Select 'Macros' and then 'View Macros.' Select the new macro from the list and click 'Run.'

Read more ►

How to Delete Excel Rows Based on Date

1. Open up your workbook in Excel. If you are using Excel 2007, you need to display the 'Developer' tab if it isn't already showing. To do this, click on the 'Office' button and then 'Excel Options.' Click on 'Show Developer Tab' and click 'OK.'
2. Create a new macro. In Excel 2007, click on the Developer tab and then 'Macro.' In earlier versions, click on 'Tools' in the toolbar and then 'Macro.' Name the macro, 'DeleteRowbyDate' and then click 'Create.' The Visual Basic editor appears.
3. Copy and paste the following code into the Visual Basic editor between the lines, Sub DeleteRowbyDate() and End Sub:Dim x As Long
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(x, 'B').Value
If CDate(Cells(x, 'B'))
Cells(i, 'B').EntireRow.delete
End If
Next iOn the line where it says, ' If CDate(Cells(x, 'B'))
4. Close the Visual Basic editor. It automatically saves to the macro. When you want to run the macro and delete the rows, go back to 'Macros,', select the 'DeleteRowsbyDate' macro and click 'Run.'

Read more ►