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 ►

How to Add Labels in Excel 2007

1. Open Microsoft Excel 2007 and the worksheet that contains the data you want to chart.
2. Select the data that will be charted and create a chart for it by clicking on the 'Insert' tab. Choose the type of chart you want to create from the 'Charts' group---column, line, pie, bar, area, scatter or other. Your chart will be placed onto the worksheet.
3. Make sure graph is selected, then click on the 'Layout' tab in the Ribbon, so you can add your data labels. Go to the 'Labels' group and click on the arrow by 'Data Labels.' Choose 'More Data Label Options' from the list of choices.
4. Select 'Label Options' in the 'Format Data Labels' dialog box.
5. Place a check mark by the options you want the data label to contain---'Series name,' 'Category Name' and/or 'Value' and remove any check marks you don't want.
6. Pick where you'd like the labels to appear---'Center,' 'Inside End' or 'Inside Base,' then click on 'Close.' The labels will be placed onto your chart.
7. Drag the labels manually on your chart if you'd prefer to place them in a different location.

Read more ►

How to Create a Formula in Excel 2003

1. Open Excel by double-clicking the Excel icon on your desktop. If you already have a spreadsheet made, you can create a formula using your existing data. If you do not have any data, just plug some numbers into the cells so you can try out these formulas.
2. Decide what your formula needs to calculate. Do you need to add up a column or row of figures? Do you need to divide one cell's value by another cell's value?
3. Start with an '=' (equal sign). Every formula will start with an equal sign. If you don't begin with this symbol, your formula will not work.
4. Put the first cell's name next. Each cell has a name assigned to it. 'A1' is the first cell in the worksheet. Find the cell name by looking at the row and column that it is in.
5. Use the correct function key: '*' for multiplication, '/' for division, ' ' is for addition and '-' for subtraction.
6. Insert the last cell name. This is just like a math problem. Use the same rules as a simple math problem and your formula will work for you.
7. Hit 'Enter.' Does your formula work? Double-check to make sure the components of the formula are correct.

Read more ►

How to Use Excel's VLOOKUP Function

1. Learn the syntax for VLOOKUP. It is VLOOKUP(search_value,table_array,column_index_number,range_lookup).
2. Specify search_value as the value to search for in table_array. Search_value may be a reference or a value. VLOOKUP will return the #N/A error value if search_value is smaller than any value in the first column of table_array.
3. Use table_array to provide the data to search. It must represent at least two columns and a reference to a range or range name may be used. The values in the first column may be logical values, numbers or text. The search is not case sensitive.
4. Provide the column_index_number. This is the column number of the value to be returned. For example, a 2 in this field will return the value in the second column. If column_index_number is less than one, VLOOKUP will return #VALUE!. If it is greater than the number of columns in the table being searched, VLOOKUP will return #REF!.
5. Enter a value for range_lookup if needed. This is a logical value that instructs VLOOKUP to perform an approximate or exact search. If range_lookup is TRUE or omitted, an exact match or the largest value less than search_value will be found. If range_lookup if FALSE, only an exact match will be found.

Read more ►

How to Select Visible Cells Only in Excel 2007

Using the 'Select Visible Cells Only' Button
1. Launch Excel and open the file that you'd like to work on.
2. Right-click on the menu bar and then click on 'Customize Quick Access Toolbar.'
3. Click the drop-down menu under 'Choose commands from' and then choose 'Commands Not in the Ribbon.'
4. Choose 'Select Visible Cells' from the list and then click the 'Add' button. Click 'OK.' The 'Select Visible Cells Only' button should now be visible on your Quick Access Toolbar.
5. Select a range of cells that contains hidden rows or columns. Click the 'Select Visible Cells Only' button. This removes all hidden cells from your selection.
Using the 'Go to Special' Window
6. Launch Excel and open the file that you'd like to work on.
7. Select a range of cells that contains hidden rows or columns.
8. Press 'F5.' Click on the 'Special' button. You may also do this by holding down the 'Alt' key and then pressing 'S.' This opens the 'Go to Special' window.
9. Press 'Y' to select 'Visible cells only.' Click 'OK,' or press 'Enter' to close the window. This removes all hidden cells from your selection.

Read more ►