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 ►

How to Calculate Ranges in Excel 2007

1. Open Excel. Click the 'Office' button. Select 'Open' from the menu.
2. Highlight the file you want to open. Click the 'Open' button.
3. Highlight the cells that you want to put in the range. Click in the 'Name Box' to the left of the 'Formula Bar.' Type a name for the range. Repeat this step until you have created all the needed ranges.
4. Click in the cell where you want to perform the calculations. Hold the 'Shift' key and tap the 'F3' key on the keyboard. Select the type of calculation from the 'Select a function' pane in the 'Insert Function' window.
5. Type the name of the range in the 'Number1' field of the 'Function Arguments' window. Type the name of the next range for this calculation in the 'Number2' field (This is optional if the 'Number1' field range contains more than one cell). Click the 'OK' button.

Read more ►

How to Create User Forms With Excel 2003

1. In the 'Tools' menu, point to 'Macro' and then click 'Visual Basic Editor' or press 'ALT' 'F11' to open the Visual Basic Editor. Open the 'Main' menu, select 'Insert' and click 'User Form' to create a new UserForm object.
2. Place a TextBox control and a Label control for each column in your worksheet onto the form by double-clicking the control or by dragging them from the Toolbox onto the form. Replace any TextBox and Label combination with a ComboBox control if you wish to present the user with a list of options to select from for the field rather than a text box.
3. Assign the correct field name properties to the text and combo boxes by typing the corresponding database field names into the Name Property line in its corresponding property window. Change Caption Properties to a user-friendly display name at the same time.
4. Double-click the 'CommandButton' control tool four times to add the First, Previous, Next and Last buttons to your form. Type their names into the Caption Property line in their corresponding property windows.
5. Drag a TextBox control onto the form in between the Previous and Next controls. Type 'RowNumber' in its Name Property line. Type '2' on the Text Property line.
6. Click or drag three additional CommandButton controls onto the form. Type 'Save,' 'Cancel' and 'Add' onto their corresponding Name Property lines. Set the Enabled Property to False on the Save and Cancel command buttons when you type the Name Properties.
7. Arrange your controls and labels, then adjust the size of the user form with the click and drag method until you are sure that the form is easy to use and pleasing to a user.
8. Add any additional text boxes and set their properties as needed for additional functionality and appeal. A form title box is recommended.
9. Type this GetData routine into the VBA code window replacing your column names (field name properties) and data types with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:'Private Sub GetData()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseClearDataMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
10. Type this ClearData routine into the VBA code window. Replace your column names (field name properties) with the sample CustomerId, CustomerName, City, State, Zip and DateAdded column names:'Private Sub ClearData()CustomerId.Text = ''CustomerName.Text = ''City.Text = ''State.Text = 'AK'Zip.Text = ''DateAdded.Text = ''End Sub'
11. Type this constant LastRow command into the VBA code window:'Const LastRow = 20'
12. Type this DisableSave routine into the VBA code window:'Private Sub DisableSave()CommandButton5.Enabled = FalseCommandButton6.Enabled = FalseEnd Sub'
13. Type this routine named RowNumber_Change into the VBA code window:'Private Sub RowNumber_Change()GetDataEnd Sub'
14. Set the appropriate event command by typing this into the VBA code window:'RowNumber.Text = '2''
15. Set the Previous and Next buttons codes by typing this into the VBA code window:'Private Sub CommandButton2_Click()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)r = r ? 1If r > 1 And r
16. Type this LastRow constant variable code into the VBA code window:'Private Sub UserForm_Initialize()GetDataEnd Sub'
17. Type this FindLastRow() routine into the VBA code window:'Private Function FindLastRow()Dim r As Longr = 2Do While r
0r = r 1LoopFindLastRow = rEnd Function'
18. Type these UserForm_Initialize events into the VBA code window:'LastRow = FindLastRowPrivate Sub CommandButton4_Click()LastRow = FindLastRow - 1RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
19. Type the PutData routine into the code window changing the sample column names and locations to match your worksheet:'Private Sub PutData()Dim r As LongIf IsNumeric(RowNumber.Text) Thenr = CLng(RowNumber.Text)ElseMsgBox 'Illegal row number'Exit SubEnd IfIf r > 1 And r
20. Type this Adding data routine into the code window:'Private Sub CommandButton7_Click()RowNumber.Text = FormatNumber(LastRow, 0)End Sub'
21. Type this Validating Data routine into the code window:'Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If KeyAscii
Asc('9') ThenKeyAscii = 0End IfEnd Sub'
22. Type this Exit event code into the VBA code window:'Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)If Not IsDate(DateAdded.Text) ThenDateAdded.BackColor = HFFMsgBox 'Illegal date value'Cancel = TrueElseDateAdded.BackColor = H80000005End IfEnd Sub'
23. Type these combo box list details into the code window:'Private Sub AddStates()State.AddItem 'AK'State.AddItem 'AL'State.AddItem 'AR'State.AddItem 'AZ'End Sub'
24. Type these Displaying the User Form commands into the VBA code window:'Public Sub ShowForm()UserForm1.Show vbModalEnd Sub'
25. Review and text your form instructions and coding by selecting 'Run' from the 'Main Visual Basic for Applications Menu.'

Read more ►

How to Open XML in Excel 2007

1. Enable the 'Developer' tab in MS Excel 2007. Click on the Windows icon on the top left hand side of the screen and click on the 'Excel Options' button. Click on the 'Popular' category to bring up popular Excel 2007 choices. Then click on the box next to 'Show Developer Tab in Ribbon.' Click on 'OK' once this box is checked.
2. Click on the 'Developer' tab on the top of the screen to show the Developer commands. Click on the 'Import' button in the 'XML' box.
3. Browse through your files until you find the file you want. Click on the file to select it and then click 'Open.'
4. Select the import option you want in the dialog box that opens. 'XML Table in Existing Worksheet' will open the table beginning in the cell location you are currently in. 'XML Table in New Worksheet' will open up a new Excel file and import the file starting in field A1. Click 'Import' once you have selected the correct option to begin the import process.

Read more ►