How to Troubleshoot Excel's Autofill

1. Check your formulas for formatting errors. Ensure that all parentheses in your formula are part of a matching pair and enclose all nonalphabetic characters within single quotation marks. Always enter unformatted numbers in your formulas; for example, '$1,000' should be entered as '1000.' The numbers in cells can be formatted at a later time using the 'Format' menu.
2. Avoid losing data in cells when dragging the fill handle by changing your preferences. Under the 'File' tab, select 'Excel' then click 'Options.' Click 'Advanced' then click the checkbox labeled 'Alert before overwriting cells.'
3. Set calculations to 'Automatic' or formulas will not recalculate as you fill cells. To do this, click the 'File' tab, then select 'Excel' and click 'Options.' Select the 'Formulas' category, then under 'Calculation Options' choose the setting labeled 'Workbook Calculation.' Click 'Automatic' to have the formulas automatically recalculate.
4. Disable autofill by selecting 'Excel' under the 'File' tab and clicking 'Options.' Choose 'Advanced', then uncheck the box labeled 'Enable AutoComplete for cell values.'

Read more ►

How to Set a Print Area in Microsoft Excel 2003

1. Highlight the desired print area. Do this by left-clicking with the mouse and holding the button as you drag the cursor over the cells that you wish to highlight. Release the mouse button when you are finished and the area that you selected will remain highlighted.
2. Access the print properties menu. Scroll to the “File” tab on the command bar and select “Print Area.”
3. Set the print area. A submenu will open. Scroll to “Set Print Area” and left-click to set the print area. The print area will be outlined with a dashed border inside of the spreadsheet.
4. Check the print area in the preview screen. Scroll to the “File” tab on the command bar and select “Print Preview” to see how the file will look when it is printed.
5. Adjust the print area. To make any changes, just hover the mouse over a corner of the dashed border surrounding the print area. When the cursor turns to a cross-sectioned arrow, left-click and hold. Drag the print area to the desired size.

Read more ►

How to Activate a Toggle Button in VBA

1. Click the 'Start' button in Windows and select the 'Microsoft Excel' from the 'All Programs' menu to open a new workbook.
2. Click the 'Tools,' 'Macro' and 'Visual Basic Editor'.
3. Click 'Insert,' then 'UserForm' to insert a user form in your workbook.
4. Add the 'ToggleButton' and 'Label' controls on the user form.
5. Double-click the 'ToggleButton' control to open the 'Code' window for the ToggleButton control.
6. Enter the following code for the 'ToggleButton1Click event:Private Sub ToggleButton1_Click()If ToggleButton1.Value = True Then' Set UserForm background to Red.Me.BackColor = RGB(255, 0, 0)Else' Set UserForm background to Blue.Me.BackColor = RGB(0, 0, 255)End IfEnd Sub
7. Click the 'Run Sub/UserForm' on the 'Run' menu to activate the toggle button in your VBA control.

Read more ►

How to Align Column Numbers on the Decimal in Excel 2003

1. Select a column of numbers that you want to align by clicking on the letter of the column. For example, column C.
2. Click the Format menu and select Cells.
3. Select the Number tab in the window.
4. Select Number from the Category list.
5. Select the number of decimal places you want by clicking the up or down arrow next to the box. If you select four decimal places, for example, Excel will round off any number that has more. For example, 5.89237 will become 5.8924. Any number with fewer decimal places will be padded with zeroes. For example, 4.8 will become 4.8000.
6. Click 'OK.' Excel will now align all the numbers on the decimal.

Read more ►

How to Convert Lotus123

The Steps
1. Open the Media-Convert page. Media-Convert is a free-to-use online converter that can read Lotus 1-2-3 file extensions and convert those files into several other spreadsheet file types.
2. Add the Lotus 1-2-3 file to Media-Convert. To add, click on the 'Browse' button beside 'File.' In some browsers, the button may say 'Open File' instead of 'Browse.' When the 'Open' window pops up, find the Lotus 1-2-3 file on the hard drive and click the 'Open' button to upload the file.
3. Double-check the input format. 'Lotus 1-2-3' is one of the available file types in the 'Input format' list. Media-Convert should select this format automatically, but if the detection fails, you can select the file type from the 'Input format' list.
4. Choose an output format of your choice from the 'Output format' list. Available options for Lotus 1-2-3 file conversions include OpenOffice spreadsheets, PDF and Microsoft Excel workbooks. The type of file that you choose depends on the program that you need the file to open in.
5. Convert the Lotus 1-2-3 file. To begin the conversion, click 'OK.' Media-Convert will begin the process and the status of the conversion will be displayed on the screen.
6. Download the converted file. When Media-Convert is done converting the Lotus 1-2-3 file to your selected file type, you will be transferred to a screen with a download link for the converted file. To download, click on the link provided and save the new file to your computer.

Read more ►

How to Create Microsoft Excel PivotTables

1. Open the Excel 2010 document that contains the information you want to use in a PivotTable.
2. Click the Insert tab on the Excel ribbon. Click the PivotTable button at the left end of the ribbon. This opens the Create PivotTable window.
3. Look for the dotted lines on your Excel worksheet. These lines determine what data the PivotTable uses. The PivotTable defaults to include all the data on the sheet, but if you need to choose a different area, select the top-left cell of the area you want to use. Then navigate to the bottom-right cell, hold shift, and select that cell.
4. Choose between placing the PivotTable on a new worksheet or using an existing worksheet. If you choose an existing worksheet, you need to choose the worksheet from a drop-down box. Click OK to create the PivotTable.
5. Click on the fields in the field list, located on the right side of the screen, to add them to the PivotTable. Each column in your original data area will become a field listed here. As you add fields to the PivotTable, Excel automatically places them into one of the four PivotTable areas, located below the field list.
6. Click a field in one of the four PivotTable areas, and then drag it to a different area to give the PivotTable your desired look. The four PivotTable areas correspond to different aspects of the table itself. Fields in the Report Filter area sit above the PivotTable and allow you to filter the entire table based on the values in that field. Fields in the Column Labels and Row Labels areas make up the columns and rows of the PivotTable. Fields in the Values area make up the bulk of the PivotTable.

Read more ►

How to Make a Bubble Graph on Excel

1. Open a new Excel 2010 spreadsheet.
2. Click on cell 'A1' and type in the title for your first set of data. This data will be shown along the 'X' axis on your bubble graph. Once the title is in place, enter the data into the cell in column 'A.'
3. Select cell 'A2' and enter the title for the second set of data. This data will make up the 'Y' axis on your graph. Once the title is in place, enter your data into column 'B,' underneath the title.
4. Click on cell 'C1.' Enter the title for your third set of data. This set will be represented by the size of the bubbles on your graph. Once the title is in place, fill out the rest of column 'C' with your data.
5. Click on cell 'A1' and hold down the mouse button. Drag your mouse down to the last cell in column 'C' and release the button.
6. Click 'Insert' at the top of the screen. Locate the 'Charts' area of the ribbon and click the 'Other Charts' button found there. Select 'Bubble' or 'Bubble with 3D effect,' depending on how you want the chart to look. The bubble chart will appear on your spreadsheet.

Read more ►

How to Change an ActiveSheet Name

Excel
1. Click a sheet tab (near the bottom of your screen) to activate the sheet.
2. Right-click the sheet tab.
3. In Excel 2003 or Excel 2007, select 'Rename.' In Excel 2010, select 'Rename Sheet.'
4. Type the new name over the old name.
Visual Basic for Applications (VBA)
5. Click on a sheet tab to make it the active sheet.
6. Open the Visual Basic Editor. In Excel 2003, select Tools > Macro > Visual Basic Editor from the menu. In Excel 2007 and 2010, click 'Visual Basic' in the 'Code' area of the 'Developer' tab.
7. Insert a new module. Select Insert > Module from the menu.
8. Enter code. Type the following in the new module:Sub RenameSheet()ActiveSheet.Name = 'Graphs'End Sub
9. Execute your code. Select Run > Run Sub/User Form from the menu.
10. Return to your spreadsheet. Click the 'Excel' icon in the toolbar below the menu. On the active sheet's tab, you will see the name 'Graphs.'

Read more ►

How to Make Excel Not Simplify

1. Highlight the group of cells that you plan to enter fractions in. To highlight an entire row or column, click the letter or number on the top or left side of the spreadsheet. Do this only in a spreadsheet that you intend to enter fractions in; if you change the formatting of cells already containing fractions, Excel will change the fractions to decimals.
2. Right-click the highlighted cells, and select 'Format Cells' on the context menu. A new pop-up window titled 'Format Cells' appears.
3. Select the tab labeled 'Number' at the top of the Format Cells window.
4. Click to select 'Text' on the left side of the window.
5. Click 'OK' to close the window, and then enter the desired fraction in one of the cells that you changed. Excel leaves the fraction as you entered it.

Read more ►

How to Make an Excel 2007 Spreadsheet

1. Open Microsoft Office Excel 2007.
2. Create an Excel spreadsheet from scratch by selecting the Microsoft Office Button. Click on 'New' and press the 'Enter' key. A blank worksheet will be placed on your screen. The first cell is column A, row 1.
3. Add a heading to the first cell, such as 'Department' and press the 'Tab' key. You can use the 'Enter' key to go down one line or the 'Tab' key to move across your cells.
4. Add another heading to the right of 'Department' (in column B, row 1) called 'Sales' and press the 'Tab' key. Continue adding more information to your heading (in column C, row 1) as needed.
5. Place your cursor in the second row (column A, row 2) and type in a department name below the 'Department' heading, such as 'Marketing' and press the 'Enter' key. Repeat this step to add additional data to your rows (column A, row 3; column A, row 4) until you are finished.
6. Format the numbers to display in a specific format (such as $0.00) for the 'January Sales' column. Select the column heading (B), just above January Sales, to select the entire column.
7. Select the 'Home' tab, go to the 'Cells' group and choose 'Format.'
8. Make sure that the 'Number' tab is selected in the 'Format Cells' dialog box and choose 'Currency' under 'Category.'
9. Accept '2' for the number of decimal places. Then click on 'OK.' Once you have all the information you need, save your Excel 2007 spreadsheet by clicking on the 'Save' button in the 'Quick Access Toolbar' (shortcut: Ctrl S). Name your file and click on 'Save.'

Read more ►

How to Export Excel Graphs as Pictures in Words

Import the Excel File into a Word Document
1. Open the Word 2010 document.
2. Click the “Insert” tab on the command ribbon.
3. Click “Object” in the “Text” group. The “Object” dialog window opens.
4. Click the “Create from File” tab.
5. Type the Excel file name or click “Browse” to locate the Excel chart file.
6. Click “Insert.”
7. Click “OK.” The Excel file opens on the Word document.
8. Click in the chart area. If the chart appears huge, click and drag the sizing handles to re-size. You can also position the chart over the worksheet to minimize the worksheet from view.
9. Click outside the chart to set the Excel chart in the Word document.
Copy the Excel Chart into the Word Document
10. Open the Excel file that contains the chart.
11. Click in the chart area. Click “Copy.”
12. Open the Word document.
13. Click in the Word document where you wish to insert the Excel chart.
14. Press 'Ctrl' and 'V' to paste the Excel chart in the Word document.

Read more ►

How to Merge Data in Duplicate Rows in Excel

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click the 'Microsoft Excel' shortcut to open the software.
2. Click the 'File' ribbon and then click 'Open.' Click the Excel file you want to edit and click 'Open.' Click the cell where you want to display the merged results.
3. Type the following function in the cell:=concatenate(a1, b1)
4. Press 'Enter' to save the changes. The cells 'a1' and 'b1' are merged and displayed in the selected cell.

Read more ►

How to Draw Data Trees in Excel

1. Launch Excel 2010 and begin working on a new spreadsheet. Click the cell where you want the top item of the data tree to appear. Begin around cell J1 for a vertical tree or cell A10 for a horizontal tree to allow sufficient room for branches.
2. Click the 'Home' tab at the top of the window. Click the arrow next to the Border button in the 'Font' section of the toolbar and select 'Thick Box Border' to draw a border around the tree item.
3. Click a cell a short diagonal distance from the first cell. For example, if you put the first tree item in cell J1, click cell 'H4.' Type the first item that you want to connect to the item you entered in the first cell and then place a border around it. Repeat this step using different cells to add additional branches below the first item on the tree.
4. Click the 'Insert' tab at the top of the window and then click the 'Shapes' button in the 'Illustrations' section of the toolbar. Select an appropriate shape to use for the branches of the data tree such as a straight line or arrow. Click and drag to draw a line from the first item on the tree to one of the branches and then repeat this process to connect the remaining items.
5. Repeat Steps 3 and 4 to add additional items and branches to the tree.

Read more ►

How to Copy Paste Conditional Formatting

1. Open the worksheet that contains the conditional formatting you want to copy and paste.
2. Select the range you want to copy. Make sure the cell that contains the conditional formatting is included in the range.
3. Go to the Format menu and select Conditional Format. The conditional formatting from the highlighted range will be already defined in the dialog box.
4. Click 'OK.' This will copy the formatting to the rest of the cells in the highlighted range.

Read more ►

How to Disable the Office 2007 Clipboard

1. Start Microsoft Word 2007 from the 'Start' menu or shortcut. If you do not have this application installed, you can load Excel 2007, Access 2007, Outlook 2007 or PowerPoint 2007 and follow the same steps.
2. Click on the 'Home' tab from the top toolbar so you can see the large 'Paste' icon. Below the 'Paste' icon is a caption labeled 'Clipboard.' Click on the small icon at the right of the 'Clipboard' text to bring up the clipboard task pane.
3. Click the 'Options' button on the bottom left and click on any items that are checked. Doing this will uncheck the item and disable the corresponding clipboard feature. Click on the 'X' icon on the upper right of the clipboard task pane to close it.

Read more ►