October 2013 - Excel

Browse » Home » Archives for October 2013

Monday, October 28, 2013

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.

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.

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.

Sunday, October 27, 2013

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.

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.'

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.

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.'

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.

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.

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.

Saturday, October 26, 2013

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.

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.

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.

Thursday, October 24, 2013

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.'

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.

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.

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.

Wednesday, October 23, 2013

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.

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.

Tuesday, October 22, 2013

How to Use Random Function in Microsoft Excel

1. Open Microsoft Excel.
2. Create two values between which you want a random number generated. For this example, enter '1' in cell A1 and '10' in cell A2, omitting the quotation marks in both cases.
3. Type the implementation of the random number function show below into cell A3:=RAND()*(A2-A1) A1The random function is designed to return a random value greater than or equal to 0 and less than 1. By taking the difference between the upper limit of the range (in cell A2) and the lower limit of the range (in cell A1) and then adding the lower range value, you generate a random number between those values.
4. Press the 'Enter' key to confirm the function entered in the previous step and return a random number within the specified range. To return only whole numbers, right-click the cell containing the function and select the 'Format Cells' option. Select the first tab on the resulting screen, choose 'Number' from the category list, specify a decimal place value of 0 and then click 'OK.'

How to Convert Word Files to Excel Files

Copy and Paste
1. Open the Word file in Microsoft Word.
2. Click and drag to highlight the information you wish to bring into Excel, press 'Ctrl-C' to copy.
3. Open Microsoft Excel and click into a blank cell.
4. Press 'Ctrl-V' to paste' the Word content into Excel. Table data from Word transfers the best. Paragraph text is pasted into the first cell on each row, with one paragraph per row.
Save the Word file as HTML First
5. Open the Word file in Microsoft Word.
6. Select Click the 'File' menu and select 'Save As.' Choose 'HTML' or 'Web page' in the Save As Type drop-down menu.
7. Click 'Save' and you will have a file with the .htm extension.
8. Open Excel to a blank spreadsheet, select the 'File' menu and choose 'Open.'
9. Select the HTML file you just saved from Word and click 'Open.' Excel will read this file, placing any table data in separate cells and individual paragraphs on new rows, but the text is formatted a little better than with the copy-and-paste method.
10. Click in cells containing numerical table data and you'll see that they are completely editable in Excel. Excel maintains the format from the Word HTML document very well.
Insert Word File as an Object
11. Open Excel to a blank spreadsheet.
12. Select the 'Insert' menu, choose 'Object...' and choose Microsoft Word document as the type.
13. Click the 'From File' button, navigate to the Word file you want to bring into Excel and click 'OK.'
14. Notice that this object is not editable in Excel. It appears as an image on the spreadsheet. If you double click the object, however, Microsoft Word will open and allow you to edit the file. Any changes made to the file in Word are automatically transferred to the object in Excel when you save and exit Word.

Monday, October 21, 2013

How to Recover a Lost Document in Microsoft Excel

Closed Without Saving
1. Click 'File' and then click 'Recent.'
2. Click 'Recover Unsaved Workbooks.' A folder of drafts will open in another window.
3. Double-click the file to open it.
4. Click 'File' and 'Save As' to save the file so you don't lose it again.
Previously Saved
5. Open the file on which you were working before you lost your changes.
6. Click 'File' and 'Info.'
7. Click the version that says '(when I closed without saving)' under Versions.
8. Click 'Restore' at the top of the window to save the version you lost.

How to Create Microsoft Excel Forms

Add Data Forms Command to Quick Access Toolbar
1. Open Excel to a default, blank worksheet. Click the 'Microsoft Office' button at the top left of the screen to open the 'File' menu and then select the “Excel Options” to open a new “Options” window.
2. Select “Customization” from the list on the left side of the window to view commands you can add to the Quick Access toolbar. To make locating the 'Data' Forms command easier, click the drop-down list next to “Choose Commands From” and select “Commands Not on the Ribbon.”
3. Select the “Forms” command from the drop-down list and press the “Add” button to add the command. Click “OK” to commit and return to the blank worksheet. The “Forms” command icon will be on the far right of the Quick Access toolbar.
Prepare the Worksheet
4. Enter column titles. Type “January” in cell A1, and “February” in cell B1, and use AutoFill to fill in the remaining months by selecting cells A1 and B1, then placing the mouse cursor in the lower-right corner of cell B1 until it changes to a “cross” shape. Hold the mouse button and drag across the spreadsheet to cell L1.
5. Set column width by clicking on cell A1 and dragging across to cell L1 to select and highlight cells. Locate the “Cells” section on the “Home” tab of the main menu and click the “Format” drop-down box. Select “Column Width” and set the width to “20” (or whatever width you want the columns to be) and then click “OK” to return to the spreadsheet.
6. Type the number “12” in cell A2 and “4” in cell B2. This step is necessary so Excel can distinguish column headings from the data.
Create the Data Entry Form
7. Place your cursor in the “A” column heading, click to select and drag across the spreadsheet to select all columns.
8. Click the “Form” icon you added to the Quick Access toolbar to create a data entry form that displays as a new window. The data entry form displays the months you entered as column titles.
9. Enter information as necessary to complete the worksheet. To start a new row, click the “New” button and continue entering information. Click “Close” when data entry is complete to return to the completed worksheet.

How to Find and Replace Text in Microsoft Excel 2003

1. Access the 'Find and Replace' menu. Scroll to the “Edit” tab on the command bar and select “Find.” A “Find and Replace” properties menu will open.
2. Type in the text you want to find. Under the “Find” tab, you can type in the text that you wish to find.
3. Type in the text that you wish to replace. Under the “Replace” tab, you can type in the text that you wish to replace.
4. Find and replace desired text. You can do this in two different ways. One way is by pressing the “Find All” button, which will find all occurences of the text you have typed, and another method is by using the “Find Next” button, which will find each separate occurrence of the text. Choose the desired option and, when you find text to replace, click on the “Replace” button to replace it. You can also click on “Replace All” to replace all occurrences of the text inside of the spreadsheet.

How to Override an Excel Password

1. Make a new spreadsheet. Leave it entirely blank. If it is a protected workbook, instead of just one sheet, make a new workbook.
2. Click on the top left corner of the protected spreadsheet's header bars (above the 1, left of the A) to highlight the whole sheet.
3. Copy the spreadsheet, either by pushing 'Ctrl' 'C' or by selecting 'Edit' from the menu bar and clicking on 'Copy.'
4. Click on your new blank spreadsheet. Click the top left corner to highlight the whole sheet, as in Step 2.
5. Paste the copied spreadsheet by pushing 'Ctrl' 'V' or by selecting 'Edit' from the menu bar and clicking on 'Paste.' You now have an identical copy of the protected spreadsheet---only it's not protected.
6. Rename the new spreadsheet to match the name of the old one (you may have to first rename or delete the old one to do this, if they are in the same workbook).
7. Manually copy all of the page setup settings, if you need to print this spreadsheet just like the old one. With the old, protected spreadsheet pulled up, select 'File' from the menu bar and click on 'Page Setup.' Go through the tabs and write down all the settings. Return to the new spreadsheet. Open the page setup box and enter the settings.
8. Repeat Steps 1 through 7 for each spreadsheet, if it is a whole workbook being copied. Rename the new workbook to match the old one when you are finished.

Sunday, October 20, 2013

How to Make Pie Graphs in Excel

1.
Enter the data that you want your pie graph to represent into a spreadsheet in Microsoft Excel. Make sure to include column headings that explain what the data signifies. Try to be as descriptive with your data entry as possible because these elements will automatically feed into the pie graph. To help explain, we will construct an example in which we create a pie graph that breaks down a favorite color list by percentage.
2.
Click on the cell that contains your first column heading. Then, from the “Insert” menu, click on the arrow underneath the word “Pie” and the picture of a sample pie graph. Another menu will open.
3.
Choose the type of pie graph that you want to create with your data. For this example, we will choose the simplest of the two-dimensional options. Once you have made this selection, the pie graph will appear in your spreadsheet.
4.
Right-click on the pie graph and another menu will appear. Use the options in this menu to make any modifications that you wish to the format. For our example, we will change the wording in the title and apply labels to the pie graph.
5.
Save your Excel file so that you do not lose your work.

How to Make Address Labels From Excel 2007

1. Open Excel by clicking 'Start,' then 'All Programs,' then 'Microsoft Office,' and finally 'Microsoft Office Excel 2007.'
2. Click the 'Microsoft Office Button,' then select and click 'New,' and under the Template > Microsoft Office Online section click 'Labels.'
3. Click 'Mail and shipping,' then select and click 'Business.'
4. Select and double click the icon 'Avery 8160 Template' and the label template will begin downloading onto your computer system. Once it has completely downloaded, 30 labels will appear on a letter-size, 8.5-inch by 11-inch, Excel worksheet window.
5. Type the address information onto the address labels.
6. Print out the typed labels using the Avery 8160 labels.

How to Compare Two Excel Spreadsheets for Duplicate Rows

1. Open your spreadsheet and make sure you know what columns the data you want compared are in. This example will assume the data is in column A, and that the two lists to be compared are in two spreadsheet tabs, labeled Sheet1 and Sheet2 of the same workbook.
2. Enter the following formula in cell B1 of Sheet1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.
3. Copy the formula from cell B1 through as many rows as you need on Sheet1.
4. Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a '0' in column B to the top, putting all your duplicate records at the bottom of the list.

How to Draw in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Insert' tab. This is located near the top right-hand corner of the screen.
3. Click on the 'Line' drawing tool. The 'Line' tool is a good place to start, because you can use basic line shapes to draw a plethora of other custom-drawn shapes.
4. Click 'Lock Drawing Mode,' located underneath the main 'Line' heading. This option lets you connect several lines to predetermined nodes.
5. Click on the document where you want the line to start.
6. Drag your cursor across the spreadsheet to form a line and double-click when the line is long enough. Repeat this process as needed, and click on existing lines to create connection nodes. If you want to move a line or a node, click and drag the entire line or one of the circular nodes.
7. Press the 'Esc' key when you are finished connecting lines. This takes you out of the line editing mode.
8. Click on the 'Shapes' tool if you need to make a simple, pre-drawn shape.
9. Select the desired shape from the list of icons. The 'Shapes' tool gives you access to squares, circles, triangles and oblong shapes, each indicated by different icons underneath the 'Shapes' tool heading.
10. Click and drag the selected shape across your spreadsheet.
11. Click on the 'Home' tab when you are finished. This returns you to the main Excel window, letting you type text, adjust fonts and use Excel macros.

How to Make a Checkable Survey in Excel

1. Open a new Microsoft Excel 2010 worksheet.
2. Enter your desired text into the cells on the spreadsheet. You can click and drag the sides of the letters at the top of each column or the numbers at the left side of each row to resize the cells as you need to. This text is what the survey taker will see when they are using the survey. Leave spaces next to the text so that you can add your checkboxes.
3. Click the 'File' tab at the top of the screen and choose 'Options' from the list that appears on the left side of the window. Select 'Customize Ribbon' from the list on the Excel Options window. Look at the column that appears on the right side of the window and place a check mark next to 'Developer.' This will allow you to use the Developer tools with your spreadsheet. Click 'OK' to go back to your spreadsheet.
4. Select the 'Developer' tab at the top of the spreadsheet. Click the 'Insert' button on the ribbon and choose the small checkbox under 'ActiveX Controls.' Click and hold the mouse button anywhere on the spreadsheet, then drag the mouse down and to the right to create a box that will become your checkbox. Release the mouse button and the checkbox will appear.
5. Click on the checkbox to select it. Press 'Ctrl' and 'C' to copy it to your clipboard, then press 'Ctrl' and 'V' to paste a second copy of the checkbox. Press 'Ctrl' and 'V' repeatedly until you have created all the checkboxes that your survey needs.
6. Move your mouse over a checkbox until the pointer turns into a set of four arrows. Click and hold the mouse button down, then drag the checkbox to wherever you want it located on the spreadsheet. ActiveX objects exist above the spreadsheet level, so you do not have to place the checkbox within any particular cell or set of cells. Move each checkbox to your desired location.
7. Right-click a checkbox, move your mouse over 'Checkbox Object' and choose 'Edit.' You can now change the default text in the checkbox to whatever you desire. Repeat this process for every checkbox. If you don't want to use any text, click and hold the mouse button over either of the corners on the right side of the box. Drag the mouse to make the checkbox smaller until just the actual checkbox is visible, essentially hiding the text. Repeat this process for every checkbox.
8. Click the 'Design Mode' button in the ribbon to exit design mode. Your checkboxes will now be locked in place, and clicking on them will only add or remove a check. You will need to click 'Design Mode' again if you want to edit the boxes any further.

How to Turn an Excel Spreadsheet Into a Standalone Application

1. Download and install the XCell Compiler using the link in the Resources section.
2. Launch Microsoft Excel and open the spreadsheet that you would like to convert to a standalone EXE file.
3. Click the Office jewel in the upper-left corner of the window. Click 'Excel Options.'
4. Click the 'Trust Center' link on the left side of the window, then click the 'Trust Center Settings' button.
5. Click the 'Macro Settings' link on the left side of the window, then place a check in the box labeled 'Trust access to the VBA project object model.' Click 'OK.'
6. Click the 'Add-Ins' tab at the top of the Excel window.
7. Click the 'DoneEx' menu, then click 'XCell Compiler' and 'Compile.' If XCell Compiler is not registered yet, a window will be displayed. Click 'Close.'
8. Click the '...' button next to 'Target Path,' and browse to the location where you would like the converted file to be saved. Click the 'Compile' button.
9. Click 'OK' and close Excel. Find the converted EXE file in the location that you specified in the previous step. The program will open when double-clicked just as a standard application.

Saturday, October 19, 2013

How to Copy and Paste Tables From Excel into Microsoft Word 2003

1. Highlight and copy the table from Excel. Left-click and hold as you highlight the entire table in Excel. Release the mouse button and the table will remain highlighted. Right-click on the highlighted table, and then click on “Copy.”
2. Select the area in Word that you wish to paste the table into. Click on the portion of the document that you wish to paste the table into.
3. Paste the table into Word. Scroll to the Edit tab and then select “Paste.”
4. Choose your table paste attributes. On the lower-right corner of the table you just pasted, there will be a clipboard icon. Click on this to change the table paste attributes. You can choose from the following table paste attributes by clicking on the corresponding radial button: Keep Source Formatting (which will paste that table the same way you copied it), Match Destination Table Style (which will copy the formatting of any existing tables in Word), Keep Text Only (which will only copy the text from the table), Keep Source Formatting and Link to Excel (which will update the table in Word any time you make changes to the same table in Excel) and Match Destination Formatting and Link to Excel (which will match the formatting of any existing Word tables, and update the table as you make changes to it in Excel).

How to Check for Duplicates in Excel

1. Click the Windows 'Start' button and select 'All Programs.' Click the 'Microsoft Office 2010' folder to expand the contents of the folder. Click the 'Microsoft Excel 2010' option to launch the Excel application.
2. Click the Microsoft Office button, and click 'Open.' Navigate to the spreadsheet file to search for duplicates.
3. Click the top leftmost cell to select the entire worksheet. To select a range of cells, click the top leftmost cell and drag the cursor to select the desired columns and rows.
4. Click 'Data' from the top navigation ribbon. Click 'Filter' and then click 'Advanced Filter.'
5. Click the 'Filter the List, in Place' option.
6. Check the check box in front of the 'Unique Records Only' option, and then click 'OK.'
7. Click the 'Edit' option on the top navigation ribbon, and then click 'Office Clipboard.'
8. Click the 'Copy' icon on the top navigation ribbon, or press the 'Ctrl' 'C' keys to copy the Excel data to the clipboard in the left pane of the Excel interface.
9. Click 'Filter' and then click 'Show All' from the Data pane in the top navigation ribbon. The original data are displayed, including duplicates.
10. Press the 'Delete' key to delete the original list.
11. Click the filtered list in the clipboard pane. The filter list replaces the original list in the Excel file. Duplicate records are removed.
12. Click the 'Microsoft Office' button, and then click 'Save' to save your new filtered list.

How to Recover a VBA Excel Password

1. Navigate to the A Pass Cracker Atomic Visual Basic for Applications (AVBA) link (see Resources). AVBA recovers passwords for VBA passwords associated with Microsoft Excel and Word. The software promises to recover passwords instantly regardless of length. Although there is a trial version of the software available, it will only show the first two symbols of the cracked password: you must purchase the full version to see the rest of the password.
2. Click on Last Bit's VBA Password link (see Resources) and download the software. VBA Password uses a combination of six different techniques to crack your password, including dictionary attack which tries to match your password against every word in the dictionary. The software uses brute force (trying every possible character and letter combination) to recover some passwords; this process may take days so you can choose to reset the password instantly for access. VBA password works with all versions of Excel.
3. Navigate to Elcomsoft'sAdvanced VBA Password Recovery (AVPR) website (see Resources). AVPR can help you to recover Excel VBA passwords from Excel 97, 2000, XP, 2003. The software can also find the password for any Excel add-ins. The software works by utilizing the 'backdoor' method which bypasses the password and recovers the file. Download the trial version of the software and use it free for 30 days. The trial version is limited to cracking passwords of 3 letters or less.

How to Get Solver for Excel 2007

1. Open a spreadsheet in Excel.
2. Click 'Add-ins' on the Tools menu. Select 'Solver Add-in' check box.
3. Click 'OK,' and the Solver installation will begin automatically.
4. Click on 'Solver' on the Tools menu to run the program.

Friday, October 18, 2013

How to Protect a Pivot Table

1. Open the worksheet with the pivot table. Select the pivot table information in your Excel worksheet that you would like to protect.
2. Click 'Review' from the ribbon menu. Under the Changes group, click 'Protect Sheet.'
3. Click the 'Use PivotTable Report' check box. Type a password in the 'Password to Unprotect Sheet' box.
4. Click 'OK' and re-enter the password. Your pivot table is now protected.

How to Calculate Mode Using Excel

1. Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 9,6,5,9,4,9,2 and 1 in A2, A3, A4, A5, A6, A7, A8 and A9.
2. For this example, click on the 'A11' cell. This is the cell where you will calculate the mode. When you calculate another mode, choose any cell at the bottom of the list of numbers you are using.
3. Click 'Insert Function' on the top, left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open. With Microsoft Excel 2007, click on the 'Formulas' tab and then 'Insert Function.'
4. Click on the drop-down menu of 'Or select a category.' Select 'Statistical' from the drop-down menu.
5. Scroll down the 'Select a function' window. Choose 'MODE,' which is the function of mode.
6. Click 'OK.' The 'Functions Arguments' window will pop open. Ensure that on 'Number 1' cell, A2:A9 is populated. If A2:A9 is not populated, enter A2:A9 manually. Click 'OK.'
7. The mode has been successfully calculated. In this example, the calculated value of the mode is 9.

Thursday, October 17, 2013

How to Add a Calendar Date Picker to an Excel Spreadsheet

1. Download the free 'Calendar' tool available at isamrad.com/ExcelCal/default.htm. This plugin is a standard Excel add-in program. It is offered as a compressed ZIP file which must be unzipped after download. Once the XLA file is placed into the Excel 'XLSTART' directory in the 'Program Files' folder of Windows, Excel will show a date picker icon next to cells that are already formatted as dates. Additionally, the date picker can be launched by right-clicking on any cell and choosing the 'Pick from Calendar' option. The applet also installs a toolbar button for the feature.
2. Activate a free 30-day trial of the Pop-up Excel Calendar created by Office Kit. This program offers a robust calendar interface that includes quick jumps to any month or year as well as shortcut buttons. Additionally, the program installs a unique calendar toolbar which features a quick launch for the Pop-up Excel Calendar as well as other features, including a settings button. The applet works on all major versions of Excel. If you are pleased with the demo, Pop-up Excel Calendar costs $20 as of February 2010.
3. Download the WinCalendar utility. The software comes in multiple versions, including a free option. The program is a robust date picker that displays multiple calendars simultaneously for quick selection of any date within a three-month period. Additionally it highlights holidays and offers user customization of new holiday entries. The calendar can be re-sized to suit the user's preference. It also functions in other Microsoft Office programs, or as a standalone program. The free version is functional but excludes emphasis of some holidays and limits the size of the pop-up interface. The free version also limits the number of entries that may be made.

How to Enter Formulas in Excel Divide the Sum of Several Numbers by a Number

1. Click on an empty cell in your spreadsheet.
2. Type an '=' sign.
3. Type the following formula into the cell: UM(A1:A10).
4. Replace 'A1:A10' with the cell locations of the numbers you want to add. In this example, the series of numbers is in cells A1 through A10.
5. Type a division sign '/' followed by the number you want to divide by. For example, if you wanted to divide the total of cells A1 to A10 by 3, type '/3.'
6. Press the 'Enter' key; Excel will perform the calculation.

How to Create a Flow Chart in MS Word

1. Open Microsoft Word, which automatically defaults to a blank portrait-oriented page on the screen. To change your flow chart to landscape orientation, click the 'Page Layout' tab at the top of the screen and click the 'Orientation' button directly below it. The page changes to landscape.
2. Type the name of the flow chart at the top of the page, such as 'Weather Closing Phone Tree.' Highlight the words, click the 'Home' tab at the top of the screen and change their appearance using the options in the 'Font' section of the ribbon/toolbar, such as font style and text color.
3. Click the 'Insert' tab at the top of the screen. Click the 'SmartArt' button on the ribbon/toolbar below it, which opens the 'Choose a SmartArt graphic' window.
4. Scroll through the different flow chart options, including vertical hexagons, triangles of different sizes and boxes and arrows in a horizontal line. The flow chart options in the 'Process' section of the window may be especially applicable to a flow chart.
5. Double-click a SmartArt shape group and it appears on the Word window. Enlarge the flow chart to fit the page by grabbing a corner and dragging it toward one of the edges of the Word workspace.
6. Click one of the '[Text]' words within the SmartArt flow chart shape and type the shape's information, such as a worker's name. Repeat this to fill the rest of the flow chart with labels for each shape.
7. Recolor the flow chart (optional) by double-clicking anywhere on it to bring up a new 'SmartArt Tools' toolbar. Click the 'Change Colors' button on the toolbar and hover your cursor over the options in the drop-down menu. As you hover over each group of colors, the flow chart changes. Click a color group to commit the change.
8. Click the 'File' tab, click 'Save As,' give the flow chart a name and save it to your computer.

Wednesday, October 16, 2013

How to Exit Out of Header Footer Option in Excel 2007

1. Add a header or footer. Click 'Header Footer' in the 'Text' area of the 'Insert' tab. Click on the document in the area marked 'Click to add header' or 'Click to add footer.'
2. Design the header or footer. Select preformatted options from the menu, type in your own text, or use a combination of menu items and typed text. When you finish the header or footer, click anywhere on the body of the document.
3. Change to Normal View. Click 'Normal' in the 'Workbook Views' area of the 'View' tab. You will leave header/footer mode and return to your worksheet.

How to Print Address Labels in Excel

1.
Open a blank worksheet in Excel. Go to the 'File' menu, click on 'Page Setup' and go to the 'Margins' tab in Excel 2003. In Excel 2007, go to the 'Page Layout' tab and click 'Margins.'
2.
Enter '0' in the Top and Bottom boxes. Change the margins in the left and right boxes to '.19.' Under Center on Page, select 'Horizontally' and 'Vertically.' Apply these changes by clicking 'OK.'
3.
Select cells A1 through A10 with the mouse. Go to the 'Format' menu in Excel 2003, point to 'Row' and select 'Height.' In Excel 2007, click 'Format' on the Home tab and click 'Row Height.' Enter '72' and click 'OK.' In the same manner, change the Column Width to 35. Repeat the steps in cells C1 through C10 and E1 through E10.
4.
Use the mouse to select the cells in B1 through B10. Change the width of the column to 1.29. Repeat with cells D1 through D10.
5.
Select all of the cells from A1 through E10 with the mouse. Click the 'Borders' drop-down on the Format toolbar in Excel 2003 or the Font group on the Home tab in Excel 2007. Click 'All Borders.'
6. Enter the names and addresses into the label cells. The cells in columns A, C and E are the label areas. Columns B and D are the margins between labels. Print onto the label paper.

How to Use the Transpose Function in Excel

1. Highlight the range of cells where you want the transposed data to appear. For example, if you want to transpose cells A1:A10 into a row in cells B1:K1, you would highlight B1:K1.
2. Go to the 'Formulas' tab and click on 'Insert Function.' Type 'Transpose' in the 'Search for a function' field and click on the 'Go' button. Select 'Transpose' from the 'Select a function' section and click 'OK.'
3. Highlight the range of cells you would like to transpose. In our example, we would highlight cells A1 through A10. The range will appear in the 'Array' field of the Function Arguments window.
4. Hit 'Ctrl,' 'Shift' and 'Enter' on your keyboard simultaneously. The transposed data will appear in the new location, which is B1 through K1 in the example.

Tuesday, October 15, 2013

How to Delete Shading From Alternate Rows in Excel 2003

1. Open the spreadsheet which contains the unwanted shading.
2. Click on the row number of one of the shaded rows. This will highlight the entire row. Ctrl-click on each row number where there is shading you want to delete.
3. Click on 'Format' on the menu then 'Format Cells.' Click on the 'Patterns' tab. Click the bar above the color palette that says, 'No Color.' This will delete the shading from alternate rows in Excel 2003.

How to Draw a Histogram in Windows Excel

Load the Analysis ToolPak
1. Open a new Excel spreadsheet. Click on the 'File' tab at the top of the screen, and then click 'Options' on the menu that appears.
2. Click 'Add-Ins' from the list on the left side of the Excel Options menu. Click the drop-down arrow next to the 'Manage' box at the bottom of the window. Choose 'Excel Add-Ins' from this drop-down menu, and click 'Go.'
3. Click the small box next to 'Analysis ToolPak' to place a check in the box. Click 'OK' to close this window.
Create the Histogram
4. Click on cell 'A1' and enter the title for the data that you want to use with the histogram. Then select cell 'A2' and enter your first value. Continue to enter values using the cells in column 'A' until you have added all of your data.
5. Click cell 'B1' and type in 'Bin Range.' This column of cells will contain the numbers that represent the number ranges that will make up the horizontal axis of your histogram. Remember that each bin number represents the top of the range of values that will be counted as part of that bin, so be sure to include the highest possible value for your data as part of the bin numbers. Enter the values starting at call 'B2,' and continue down the column until you have entered all your desired bin ranges.
6. Click the 'Data' tab at the top of the screen, and then click the 'Data Analysis' button on the far right side of the ribbon. Select 'Histogram' from the list of options and then click 'OK.' A small 'Histogram' window will appear.
7. Click in the blank next to 'Input Range' in the Histogram window. Click on cell 'A2' and hold down the mouse button. Drag your mouse down until you reach the last cell that holds data in column 'A'; then release the mouse button. Click on the blank next to 'Bin Range' and do the same thing as you did in column 'A,' but this time do it in column 'B' starting with cell 'B2.'
8. Select the radio button next to the output option that you desire. You can place the histogram somewhere on this worksheet, you can place it on a new worksheet within this workbook or you can place it in an entirely new workbook.
9. Place a check next to any of the bottom three options, if any apply to your desired histogram output. 'Pareto' will sort your data in descending order of frequency, 'Cumulative Percentage' will include another column on the histogram that has the cumulative percentages for your frequency data, and 'Chart Output' will display a chart of the histogram in addition to the standard table.
10. Click 'OK' to close the window and Excel will create your histogram.

Monday, October 14, 2013

How to Remove Multiple Duplicate Rows in Excel 2003

1. Open the spreadsheet and select the cells that may include duplicate rows. The values in the rows can be text or numbers.
2. Click the 'Data' menu, hover over the 'Filter' option and choose 'Advanced Filter' from the submenu.
3. Choose 'Copy to another location,' which leaves the original range of data intact, just in case.
4. Check the prepopulated 'List range' to confirm it's the range of data you want to filter of any duplicate values.
5. Choose, in the 'Copy to' field, the first cell to which you want to copy the filtered cells.
6. Place a tick mark in the box labeled 'Unique records only.' Click 'OK' to close the box and filter duplicates from the list of data.
7. Delete the original data when you're satisfied with the filtered results.

How to Make a 2nd Line on MS Office Excel

1. Click 'Start,' then 'All Programs.'
2. Click the 'Microsoft Office' folder, then click 'Microsoft Office Excel.' This will open a new workbook. To open an existing workbook, click the 'File' tab, then click 'Open' and browse to your file.
3. Click the cell into which you wish to add a second line.
4. Add the data for the first line, then press 'Alt Enter' to create a second line within the cell. You can continue to use 'Alt Enter' to add additional lines to your cell as well.

How to Add Data Labels to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to add data labels.
2. Select the chart you want to add the data labels to by clicking it. The chart will be surrounded by a light blur border indicating it is selected.
3. Choose the 'Layout' tab at the top of the Excel 2007 screen to display the options in the 'Layout' ribbon. Locate the 'Labels' group in the 'Layout' ribbon.
4. Click the 'Data Labels' button in the 'Labels' group of the 'Layout' ribbon to display a drop-down list of options.
5. Opt for 'Center' from the 'Data Labels' drop-down list to display the data labels centered on the data points of your chart. 'Inside End' will display the data labels inside the end of the data points while 'Inside Base' will display the data labels inside the base of the data points.
6. Watch as the data labels are added to the selected Excel chart in the position you have chosen.

Sunday, October 13, 2013

How to Add a Custom Menu to an Excel Toolbar in Excel 2003

1. Log on to your computer and open your Excel worksheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Go to 'Tools' in the toolbar. Hold the mouse over the arrow to expand the menu and select 'Customize'.
3. Add the functions that appeal to you by checking desired toolbars under the 'Toolbars' heading.
4. Click on the 'Commands' tab. Choose a category from the displayed list, then choose a command and drag it off the dialog box to the toolbar. Repeat as desired.
5. Delete a command by dragging the icon outside the toolbar and letting go of your left mouse button.
6. Determine whether you want a command by selecting the command under the 'Commands' heading, then choose 'Description'. This allows you to view a description of the command.
7. Close your 'Customize' window by clicking the 'X' in the left-hand corner of the window.

Saturday, October 12, 2013

How to Multiply in Excel

Single Cell Multiplication
1. Select the cell where the answer will be. You can put a self-contained multiplication problem in one cell anywhere in a workbook and use it just like a calculator.
2. Start with the equals sign in the cell where the answer goes. This is the first step to tell Excel that the cell contains a math function.
3. Use the asterisk sign as the multiplication symbol. Like standard math, insert the asterisk between numbers in the equation with no spaces and hit 'Enter.' The cell should contain the solution to the multiplication problem.
Multiply an Entire Range of Numbers by Another Number
4. Decide what number to multiply the entire data set by and select a random cell in the workbook. This is only a temporary cell entry and is erasable after you complete the function. Enter the multiplier in this cell.
5. Select the cell containing the multiplier, right click and choose 'Copy.'
6. Choose the entire row or column of varied data to multiply. Right click the highlighted row and use the 'Paste Special' function, which brings up a sub-menu window.
7. Click the 'Multiply' option under the 'Operation' section and hit 'OK.' The multiplier now changes all of the data visible in the chosen range within the Excel workbook.

How to Restore the Default Settings in Excel 2007

Tools Menu
1. Log on to your computer and open Microsoft Excel 2007. Open a blank spreadsheet.
2. Click on the 'Tools' menu. Choose 'Customize' from the list of options.
3. Right-click on the menu you want to restore to its default settings. Choose 'Reset' to restore the menu to its original default settings.
Help Menu
4. Open Excel 2007. Click on the 'Help' menu.
5. Choose the 'Detect and Repair' option. Check the 'Restore my shortcuts while repairing' check box.
6. Click 'Start' to begin the repair process. Repairing Excel 2007 will bring it back to its original configuration.

How to Use Visual Basic to Add a Worksheet in Excel 2007

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel.' The programming software opens.
2. Click the 'Office' button and click 'Open.' Click your Excel file name to open it in your software. Click the 'Development' tab and 'View Code' to open the VBA coding file for the spreadsheet.
3. Type the following code in your VBA code file:Set newSheet = Worksheet.AddnewSheet.Name = 'New Sheet'newSheet.ActivateThis code creates a new sheet and names it 'New Sheet.' The code then makes it the active sheet.
4. Click the 'Save' button and close the VBA code file. Close the file and reopen it to see a new sheet created in the file.

Friday, October 11, 2013

How to Update Link Workbooks in Excel 2007

1. Close out all of the workbooks in the Microsoft Excel 2007 application. Click on the “Microsoft Office” button and then click on the “Open” option.
2. Select the destination workbook that contains all of the links and then click on the “Open” button.
3. Click on the “Update” button from the pop-up dialog box that appears on the screen to automatically update all of the links for the source workbooks.
4. Click on the “Don’t Update” option if you want to only select links to other workbooks, and then click on the “Edit” option from the top toolbar menu.
5. Click on the “Links” option and then select the object you want to update from the “Source” list. Click on the “Update Values” option.

How to Detect Repair Excel 2007

Microsoft Excel 2007
1. Open Microsoft Excel 2007.
2. Select the 'Microsoft Office' button. Click the 'Excel Options' button.
3. Select 'Resources' located on the left side. Click the 'Diagnose' button and then click 'Continue.'
4. Click the 'Start Diagnostics' button. Office tries to identify your software problem and attempts to repair any problems. Click 'Close' when the diagnostics are complete. If Microsoft Diagnostics cannot fix the problem, repair the software through the Control Panel.
Control Panel
5. Open the 'Start' menu. Choose 'Control Panel' from the menu. Click the link to 'Uninstall a Program.'
6. Select your Microsoft Office Suite from the list of programs. Click the 'Change' button.
7. Choose the radio button to 'Repair.' Click the 'Continue' button. Microsoft Office then repairs the software.

How to Calculate a Date in Excel

1. Populate today's date with the Today function. Type '=TODAY()' in a blank cell to return today's date. The Now function returns the date and the time stamp: '=NOW()'.
2. Calculate a future date by entering a date in a cell. Click on an empty cell and type '=cell reference number of days' where cell reference refers to the date that was entered and the number of days should be substituted for the actual number of days you want to calculate. For example, if we enter 5/22/2009 in cell A1 and want to know what the date will be in 21 days, the formula would be '=A1 21', which returns 6/12/2009.
3. Calculate a past date following the same procedure as in Step 2, but replace the plus sign with a minus. For example, if we enter 5/22/2009 in cell A1 and want to know what the date was 13 days ago, the formula would be '=A1-13', which returns 5/9/2009.
4. Calculate the number of days between two dates with the DAYS360 formula. Enter a start date and end date in two cells. Click on an empty cell and type '=DAYS360(start date,end date)', where the start and end dates would be substituted with cell references. In our example in Step 3, if we had the original date of 5/22/2009 in cell A1 and the calculated end date in B1, the formula would be =DAYS360(B1,A1), which returns 13 days.

How to Learn Basic Microsoft Excel

1. Learn Excel online through the Microsoft Office website, which includes Excel tutorials for all levels. Click the 'support' tab at the top of your page and select 'Excel' from the drop-down list. Select the Excel version you want to learn from the right and click 'Getting started.' Pick a topic -- such as 'Basic tasks in Excel 2010' -- that interests you. (see References)
2. Learn how to complete basic tasks in Excel through video tutorials at Microsoft Office. Click the video that interests you, such as 'Video: Getting Started with Excel 2010,' 'Video: Create a workbook,' or 'Video: Print a worksheet.'
3. Learn basic Excel through books and CDs. You can purchase low-cost books at Amazon.com, Ebay, Craigslist, discount outlets, yard sales or borrow one from your local library. Try books like 'Microsoft Excel 2007 Step by Step,' which also includes a companion CD, 'Excel 2010 in Easy Steps,' 'MS Excel 2007 Training,' or 'Mastering Excel Made Easy Training v. 2010 through 97.'
4. Take a class at your local community college to learn the basics of Microsoft Excel, such as learning about cells, spreadsheets, using formulas and inserting rows/columns.

How to Embed a PDF File in Excel

1. Select the cell in your Excel spreadsheet where you wish to embed a PDF. The PDF file will take up more than just one cell; select the cell you want the top left-hand corner of the file to appear. Click on the 'Insert Object' button in the Text group under the Insert tab. Highlight 'Adobe Acrobat Document' under Object type in the Object pop-up window .
2. Decide how you want your PDF to appear in your Excel document. If you want the PDF to appear as the first page of the PDF, simply click the OK button. If you want the PDF to appear as an icon, click the check mark labeled 'Display as Icon' and choose an icon using the 'Change Icon...' button or click 'OK' to accept the default Adobe Acrobat Document icon.
3. Navigate to the PDF you wish to embed in your Excel workbook in the Windows Explorer pop-up window. Double-click on the PDF file or click the 'Open' button after highlighting the PDF file to embed it in your Excel workbook.
4. Double-click on your embedded PDF or representative icon in Excel to access your embedded file.

Subscribe to: Posts (Atom)