2011 - Excel

Browse » Home » Archives for 2011

Wednesday, December 28, 2011

How to Make a Bar Graph in Excel 2007

1. Start a new document and add data or open the document containing the data you wish to convert into a graph. Arrange the data with the item headers at the top of the column and data in the same column or the headers at the beginning of the row and the data to the right.
2. Select the data you want to include in the graph.
3. Click on the 'Insert' tab in the ribbon.
4. Click on the down arrow under the 'Bar' graph icon.
5. Choose the graph design you like the best. Your data will convert into a bar graph on the spreadsheet.
6. Click on the 'Design' tab to change colors, layout, add a title, switch the rows and columns, move the chart, change the chart type or save as a template.

How to Lock Charts in Excel

1. Open the worksheet with the graph in Excel.
2. Click the 'Review' tab and then click 'Protect Sheet.'
3. Click 'Allow All Users of This Worksheet To' and then select the elements you want people to be able to change. For example, even though the chart is protected, you may want users to be able to format cells, insert rows or perform sort functions on the worksheet.
4. Type a password into the 'Password to Unprotect Sheet' box and then click 'OK.' You need to type the password again to confirm it.

How to Calculate Age in Excel Using a Date of Birth

1. Open a new Microsoft Excel 2010 worksheet.
2. Click on cell A1. Enter the birth date of the person whose age you want to know. You can enter a date with dashes between the numbers, such as '3-30-1983.'
3. Click on cell B1 and enter this formula:=datedif(A1,today(),'y')In this example, 'A1' refers to the cell where you typed in the person's birth date. 'Today()' calls the current date on the machine. 'Y' indicates that Excel should give the result in years. Press 'Enter' on your keyboard to complete the formula entry, and the person's age will appear in cell B1.

Tuesday, December 27, 2011

How to Delete an Embedded Chart in Excel

Delete an Excel Chart Object
1. Launch Microsoft Excel. Click the “File” tab followed by “Open” and open the workbook document that contains the embedded chart.
2. Click the chart object to select and highlight the item. After it is highlighted, Excel displays a box around the chart with “handle” dots around the edge of the box.
3. Press the “Delete” or “Del” key on your computer keyboard to delete the embedded chart.
Delete an Excel Chart Sheet
4. Highlight the chart sheet in the Excel workbook document.
5. Click the “Home” tab on the main menu ribbon across the top of the document.
6. Click the down-arrow icon in the “Delete” section of the “Cells” group. Select “Delete Sheet” from the pull-down options list.
7. Click “OK” in the confirmation dialog box. Excel deletes the chart sheet and the chart object.

How to Import Excel 97 Macros to Excel 2003

1. Click 'Start' > 'All Programs' > 'Microsoft Office' > 'Microsoft Excel' to open Excel 2003.
2. Click 'File' > 'Open,' browse through your computer files to find the Excel 97 worksheet you want to import, select the file and then click 'Open' to open the Excel 97 worksheet in Excel 2003.
3. Click 'Tools' > 'Macro' > 'Security' to access the macro security options in Excel 2003.
4. Select the 'Medium' security setting to ensure that you are able to access most of the safe macros on any file but avoid accessing potentially harmful macros.
5. Click 'OK' to save the new security settings and then you'll be able to do whatever you need to do on the Excel 97 worksheet you just imported to Excel 2003.
6. Click 'File' > 'Save As,' select 'Excel 97-2002' from the 'File Type' menu and then click 'Save' to save the worksheet. Saving the worksheet in this manner will keep the worksheet accessible to any Excel version starting with Excel 97 and ending with Excel 2003.

A Tutorial to Sort in Microsoft Excel

Excel 2003
1. Open the file in Excel 2003 with data that you would like to sort.
2. Highlight the data that you would like to sort. If you want to sort the entire worksheet, click the small rectangle located in the top-left corner between the 'A' column and the '1' row.
3. Click 'Data' from the drop-down menu located above the toolbar.
4. Click 'Sort.'
5. The 'Sort' dialog box is displayed. Specify which column you would like the data sorted by first, second and third.
6. Specify if you want the data sort in ascending (alphabetical or number sequence low to high) or descending (reverse alphabetical and number sequence high to low).
7. Under the 'My list has' section, choose whether your data has a 'Header Row' or 'No Header Row.' This refers to whether you input a title for the columns that you want to sort. For example, is one of the columns titled 'date' or 'name?' If you do have header titles for your columns, you can click the 'Header Row' radio button and Excel will sort the data starting below your header rows. If you do not have header rows, click the 'No Header Rows' radio button and Excel will sort all the data.
8. Click 'OK' to sort your data.
9. If you only want to sort a single column of data, highlight just the column of data you would like to sort and then follow steps 3 through 8.
Excel 2007 and 2010
10. Open the file in Excel 2007 or 2010 with data that you would like to sort.
11. Highlight the data that you would like to sort. If you want to sort the entire worksheet, click the small rectangle designated with a small triangle located in the top-left corner between the 'A' column and the '1' row.
12. Click on the 'Data' tab located across the top of the screen.
13. Locate the 'Sort Filter' box.
14. This box displays the options of 'Sort A to Z' (ascending order) or 'Sort Z to A' (descending order) or to choose custom sort under 'Sort.'
15. Clicking 'A to Z' or 'Z to A' will quickly sort the data you have highlighted.
16. Click 'Sort' to apply additional parameters for the sort.
17. A 'Sort' dialog box is opened displaying the parameter choices of 'Column,' 'Sort On' and 'Order.'
18. Click the 'Column' drop-down menu.
19. A listing of the highlighted columns is displayed. Choose which column you would like to first sort the data by (i.e., date, name, title).
20. Next click the 'Sort On' drop-down menu.
21. Excel allows you to choose which feature you would like to sort the data on. For example, you can choose to sort the data by the values in the cells, the color of the cell (if you have colored coded your data), the cell font color (if you have colored the text within the cell, such as negative numbers being colored red) or cell icon.
22. Next click the 'Order' drop-down menu.
23. Choose whether to sort the data in ascending (A to Z alphabetical or number sequence low to high) or descending (Z to A reverse alphabetical and number sequence high to low) order.
24. At this point, if you would like to sort the data by data in multiple columns, click the 'Add Level' button located at the top-left of the 'Sort' dialog box. This feature allows you to sort first by the data in one column and then by the data in a second column, third column and so on. For example, if you have an address book in Excel, you could choose to sort the address book first by last name, then by first name. At that point, you could even choose to sort it by a third criterion of city. Your data would then be displayed in alphabetical order by last name and then first name, and then categorized by city.
25. Click 'OK' when you are finished setting the parameters.
26. If you only want to sort a single column of data, click in the column header cell of the column of data you would like to sort and then click either the 'A to Z' or 'Z to A' button.

How to Move a Picture, Text Box or Other Object in PowerPoint, Excel or Word Exactly Where You Want it

Removing Word Restrictions
1. Launch the Word 2010 document that contains the object that you want to move.
2. Click the object, and then click the 'Format' tab on the far-right end of the Word ribbon.
3. Click the 'Arrange' button in the ribbon. Select 'Wrap Text' from the menu that appears. Then choose 'Through,'Behind Text' or 'In Front of Text.' Any of these three options release the object so that you can click on the object's border and move it where you want.
Removing PowerPoint Restrictions
4. Open the PowerPoint 2010 presentation that contains the object you want to move.
5. Click the object, and then click the 'Format' tab at the top of the screen.
6. Click the 'Arrange' button in the PowerPoint ribbon, and then click the 'Align' button on the menu that appears. Finally, click 'Grid Settings' to open up a small window.
7. Remove the check mark from the 'Snap objects to grid' option by clicking on the check box. Click the 'OK' button to close the window. Your PowerPoint objects can now be positioned anywhere on the presentation.
Precisely Moving Objects
8. Open the Excel, Word or PowerPoint file that holds the object you want to move.
9. Select the object that you want to move.
10. Press and hold the 'Ctrl' key on your keyboard. If you are moving a text box, click any of the four corners to select the box without placing the cursor inside the box. Tap the arrow keys to move the object one pixel in the direction of the arrow that you press.

How to Create Axis Bar Graphs in Excel

1. Open Microsoft Excel. Start with a new document or load an existing spreadsheet with data you wish to analyze.
2. Organize the data in a row structure. If you are creating a new spreadsheet, type different items of the same data set over many rows within the same column. For example, if you wish to graph daily temperature changes, create a column heading called 'Temperature' in cell A1. Then, type each day's weather below it in cells A2, A3 and on down through column A. Do not skip any rows.
3. Click on any cell within the data column so Excel recognizes the active set of data. It is not necessary to highlight or select the data, as Excel can easily recognize where the list begins and ends.
4. Click the 'Chart' button on the tool bar. Optionally, click on the 'Insert' menu and choose the 'Chart' option. A pop-up window will appear.
5. Select the chart type you wish from the list. To create a conventional bar graph, choose either the 'bar' or 'column' graph types. Both create a bar chart, but change the angle of the bars to either horizontal or vertical.
6. Select a chart sub-type from the selection to the right of the chart type list. This selection changes depending on the chart type you choose. Bar charts can be presented in a three-dimensional view or with various forms of color shading.
7. Press the 'Next' button.
8. Choose the 'Rows' option, since your data exists within a row structure. A preview of the final chart will appear in the window.
9. Press the 'Next' button.
10. Add an additional explanation to either axis. The column heading of column A is automatically selected as the 'X' axis title. You may offer additional wording to describe the axis in the 'Category X Axis' field, if desired, to maintain full control over the axis of the bar graph.
11. Press the 'Finish' button. Your bar graph is now created in Excel.

Monday, December 26, 2011

How to Copy a Worksheet As a Picture in Excel 2007

1. Highlight the area that you want to copy as a picture. Click in the uppermost left cell and drag to the lowermost right cell while holding down the mouse button.
2. Click the 'Copy' drop-down menu on the 'Home' tab. Select the 'Copy as Picture' option. This action will launch a separate dialog window.
3. Select 'As shown on screen' to copy the selection as a screen shot, or select 'As shown when printed' to copy the selection as it would appear printed on a page. Select the 'Picture' option to copy the worksheet as a scalable image, or select 'Bitmap.' The 'Bitmap' copy will result in more distortion if you need to enlarge the worksheet picture after it is pasted into another document. Click the 'OK' button to copy the worksheet. You may now paste the picture into another document using the 'Paste' option.

How to Prevent Excel From Dropping Off Leading Zeros

1. Open your Microsoft Excel document or a new Excel document.
2. Select the cells you would like to modify. If you wish to modify the entire spreadsheet to show leading zeros, click the diamond icon in the upper left of the workbook window, or click the 'Edit' drop-down menu and choose 'Select All.' If you want to select only a portion of the cells, click the leading cell and drag to highlight additional cells. Also, you can click the letter at the top of a column to highlight an individual column. You can do the same for rows by clicking the number at the beginning of a row.
3. Select the 'Format' drop-down menu. Choose 'Cells...' or 'Format Cells,' depending on the release of Excel you are running. A window will pop up.
4. Choose the 'Number' menu within the 'Format Cells' window. Scroll down through the list of various categories and select 'Text.' This will allow any data put into a cell to be displayed as it was input. Click 'OK.'

Sunday, December 25, 2011

How to Create a Pivot Table From Multiple Pivot Tables

1. Open the PivotTable you would like to work with. Also open a worksheet you would like to consolidate all other pivot table information onto from one PivotTable.
2. Click on a cell with the new worksheet where you want to start the consolidated data.
3. Click 'Consolidate' on the Data menu.
4. Click on 'Sum' (or another function) in the Summary function in the Function box. This is the function you want Microsoft Excel to use to consolidate the data.
5. Input the sheet tab of the first range to consolidate in the Reference box and repeat for each PivotTable you want to grab information from. Select 'Create links to source data' check box if you want to update the data and need to know which PivotTables are open to access the data.
6. Create a new PivotTable from the consolidated data. On the Data menu, click 'PivotTable and PivotChart Report.' Use the data from the consolidated worksheet for your data range and click through all other options. Click 'Finished' for consolidated PivotTable.

How to Remove Encryption in Excel 2007

1. Open the encrypted spreadsheet in Excel, which will require your password.
2. Click the circle in the upper-left side of the Excel window. This will appear just before 'Home' on the menu and will have a Windows logo inside. In the drop-down menu, select 'Prepare' and then 'Encrypt Document.'
3. Delete the password in the window entitled 'Encrypt Document.' Press 'OK' to remove the password from your spreadsheet.

How to Add a Note to an Excel Formula

1. Open an Excel file with existing formulas or create your worksheet with at least one formula.
2. Click in the cell at the end of the formula. For example, click within cell A5. Then type the formula '=A2 A3' (without quotes).
3. Go to the 'Ribbon' at the top of Excel and click on 'Review.' Click on 'New Comment.' A colored text box will appear to the right of cell A5.
4. Start typing your comment after the username that appears. When finished typing, click another cell. The cell with a comment will contain a red triangle. Move your mouse over the cell to reveal the comment.
5. Alternatively, add a note directly to the Excel formula by using the 'N' function. To do so, add the following to your existing formula: N(“Write your comment here”). Move your mouse over the cell to reveal the comment.

How to Remove a Drop Down Box in Excel 2003

1. Click inside the cell from which you want to remove the drop-down list box. Do not double-click, as this will take you into the cell editing mode; just use a single left click.
2. Select the 'Data' menu from the top menu bar, and then click the 'Validation' option in the menu that opens.
3. Click the 'Settings' tab in the data validation dialog box.
4. Click 'Clear all' and then select 'OK'. Your drop-down list will be deleted.

How to Open a Xlsx File

Microsoft Excel
1. Launch Microsoft Excel. Click “Start,” “All Programs” and “Microsoft Office.”
2. Choose “Microsoft Excel” so that the program will start. A new spreadsheet will open.
3. Go to the ribbon and click the “File” tab. Select “Open” from the menu so that a dialog window appears. Select “My Computer” on the left, browse to find your XLSX file, then click “Open” so that it will load inside the program
OpenOffice Calc
4. Launch the OpenOffice spreadsheet. Click “Start,” “All Programs” and “OpenOffice.org”
5. Choose “OpenOffice.org Calc” so that the program will start. A new spreadsheet will open.
6. Go to the top menu and click “File” then “Open.” At the window prompt, browse to find your file, then click the “Open” button so that it will load into the software.
SkyDrive
7. Navigate to the Microsoft SkyDrive website, create an account and log in. If you have a Windows LiveID or Hotmail account, you may log in using your username and password instead.
8. Go to the menu and click “Add files.” A 'File Upload' dialog window will open.
9. Browse to find your XLSX file, then click “Open.” Your document will load into the Excel Web application, and will be accessible to you online.

How to Combine Columns in Access

1. Open the database you want to update in Access.
2. Go to the Database window and choose 'Query' from 'Objects.' Then select 'Create query in using wizard.'
3. Click the 'Tables/Queries' drop-down in the wizard and select the first table containing the columns you need.
4. Select the fields (columns) you want to add. To add a single field, click the '>' button. To add all the fields, click the '>>' button.
5. Click the 'Tables/Queries' drop-down and choose the next table containing the columns you want to combine. Then follow step four to add the fields. To add additional columns from other tables, repeat this step and click 'Next.'
6. Type in a title for your query.
7. Choose the 'Open the query to view information' option and click on 'Finish.'

Saturday, December 24, 2011

How to Show a Comment Box in a Cell With a Drop Down List in Excel

1. Open the Microsoft Excel application on your computer, then open a file. Create a list of entries for the drop-down list in a single column.
2. Select the cell where you want the drop-down list to appear. Click on the 'Data' option from the top toolbar menu.
3. Click on the 'Validation' option then click on the 'Settings' tab. Click on the 'List' option from the 'Allow' box.
4. Enter a reference to your list in the 'Source' box. Click on the box next to the 'In-cell drop-down' field so it's selected.
5. Click on the 'Input Message' tab. Click on the box next to the 'Show input message when cell is selected' field so it's selected. Type the message you want and the comment box will appear in the cell.

How to Find Probability in Excel 2007

1. Open Excel 2007 and select the 'Formulas' tab.
2. Click on the icon for 'Other Formulas.' A pop-out menu will show up. Select 'Statistical Formulas' from the pop-out menu.
3. Select BINOMDIST. A dialog box will appear with four fields.
4. Enter the number of successful tests in for the first field.
5. Enter the number of trials in the second field.
6. Enter the probability of success for a given success in the third field.
7. Enter TRUE or FALSE in the fourth field. Use TRUE if the probability distribution is cumulative, and FALSE if it's not.
8. Read the result from the bottom of the dialog box.

How to Combine Bar Graphs in Excel

1. Open the document with the data that you would like to combine into one bar graph. If you have created any graphs or charts at this point, delete them by right-clicking and selecting 'Delete.'
2. Highlight only the data for the first graph. For example, if you wanted to combine the 'Expenses' and 'Profit' graphs together, only highlight one of those pieces of information.
3. Click 'Insert' and choose 'Bar' from the 'Charts' group. Choose the specific type of bar graph that you would like. It should appear to the right of the information you highlighted.
4. Highlight the second set of data, making sure to unhighlight the first set of data. Press 'Ctrl c' to copy the information. Click on the graph and press 'Ctrl v.' This should insert the second set of information into the graph. Repeat for any other pieces of information.

How to Attach Macros to All in Excel

1. Click 'View' and then click 'Macros.'
2. Click 'Record' macro to open the Record Macro window.
3. Click the arrow underneath 'Store macro in:' to open the drop down menu.
4. Click 'Personal macro workbook' to store the macro in the Personal.xls workbook. The macro will be available for use in any workbook.

How to Create an Invoice in MS Excel

1. Navigate to the Microsoft Office templates website. This will display a list of invoice templates (blank, preformatted documents with categories that that you can fill in).
2. Click on the name of the template you would like to use.
3. Click on the 'Download' button on the next page. You will need to accept the Microsoft Service Agreement by clicking on the 'Accept' button. The document will download to your computer.
4. Open Microsoft Excel. Click on 'File' and then 'Open' in Excel 2003; in Excel 2007, click on the 'Office' button and then click 'Open.'
5. Find the downloaded template file on your computer and open the document. The template will load and open in an Excel spreadsheet, ready to fill in.

How to Replace Text in Excel 2007

1. Open the Microsoft Excel 2007 spreadsheet that you want to replace text for. Click on any cell in the spreadsheet.
2. Click on the 'Home' tab and then click on the 'Find and Select' option from the 'Editing' group.
3. Click on the 'Replace' button. Enter the text that you want to find in the 'Find what' text box and then enter the text you want to replace that with into the 'Replace with' text box.
4. Click on the 'Options' button to define your search by selecting specific rows or columns, to search for case-sensitive data, or to match the entire cell contents.
5. Click on the 'Replace' option to replace text that is found or click on the 'Replace All' option to replace all of the text throughout your spreadsheet.

Friday, December 23, 2011

How to Open Word or Excel in a Specific Program

Open an Excel Document in Another Specific Program
1. Start Microsoft Excel.
2. Click the 'File' tab and then click 'Options' under 'Help.'
3. Click 'Save,' then click 'Save files in this format.' Under 'Save Workbooks,' select a default format from the list and then click 'Save.'
4. Start the specific program and then open your file with the new default format.
Save and Then Open a Word Document in Another Specific Program
5. Start Microsoft Word, click the 'File' tab and then click 'Open.'
6. Click 'OpenDocument Text' from the 'File of type' list.
7. Select the Word file you want to open in another specific program and then click 'Open.'
8. Click the 'File' tab and then 'OpenDocument Text' from the 'Save as type' list.
9. Type in a name for your file and then click 'Save.'
10. Start the specific program you want to use and then open your file.
Link an Excel Worksheet or Word document in Another Specfic Program
11. Start either Microsoft Excel or Microsoft Word as well as the specific program to which you want to link the data. (Note: These steps apply to Microsoft Office 2010 programs.)
12. Select the data from either Excel or Word that you want to create a link from and then press and hold 'CTRL C.'
13. Go to the specific program and then click on the area where you want the Excel or Word file link to appear.
14. Click the arrow for 'Paste' from the 'Home' tab's 'Clipboard' group, then click 'Paste Special.'
15. Select 'Microsoft Office Excel object' from the 'As' list and then click 'Paste link.' (Note: Alternately, you can click 'Paste' to embed the object as static, which will not be automatically updated.)

How to Add a Pointer to Table in Excel 2007

1. Open the Excel file that contains the table to which you want to add the pointer.
2. Click the 'Insert' tab on the Excel Ribbon.
3. Click the 'Shapes' button. Click one of the arrow icons in the 'Lines' section to select it.
4. Click and drag your mouse from a space on your Excel worksheet to the cell in the table to which you want the arrow to point.
5. Click and drag the pointer's circular resize handles to resize the arrow to an appropriate length. Hover over the arrow you inserted with your mouse until a four-way arrow appears. Click and drag the arrow if you want to move or reposition it in the table.
6. Click the 'Format' tab in the 'Drawing Tools' section on the Ribbon. Click the options in the 'Shape Styles' group if you want to add an outline to the pointer, change its thickness and add other effects like a reflection or glow.

How to Create a Check Register in Excel

1. Label columns A through G in the first row, as follows: cleared, date, check #, item description, debit, credit and balance. Highlight columns E, F and G (debit, credit and balance) and right-click to 'Format cells...' and adjust these columns to represent currency. Column B can be adjusted for date format in the same manner.
2. Extend the width of these columns as far as you need by clicking on the right edge of the column and dragging toward the right. For example, the 'item description' column will need to be much longer than the 'cleared' column.
3. Key in your first entry as 'starting balance,' including the date, and enter the balance your check register begins with.
4. Enter in the box below the balance you just entered in Step 3 the following formula: =G2-E3 F3. Hit 'Enter.' That field should now read the same as the balance you keyed in above it.
5. Click on the box you just entered your formula into, and you will see that it is highlighted with a small square at the bottom right-hand corner. Click and hold the small square, dragging the formula down to as many rows as you desire.
6. Take note that all of the balance fields now contain the same amount; however, as you enter in your checking account information, the balance will adjust for you, giving you the new total.
7. Mark an 'X' in the 'cleared' column on any items you know have cleared your bank to balance your check register. Take your most recent bank statement total and add any un-Xed outstanding deposits and subtract any un-Xed outstanding debits. The balance you show on your spreadsheet should equal this calculation.

How to Count Duplicate Value in Excel 2003

1. Double-click the Excel 2003 XLS file on your hard drive. The file loads in the Excel 2003 software for you to edit its content.
2. Click an empty cell in the column directly after your document information. Type the following function in the empty cell:=IF(COUNTIF(A2:A7,A2)>1,1,'')Replace the cell ranges with the cells you want to check for duplicates.
3. Click another empty cell and type '='. This triggers the 'function' feature in Excel. Select 'Sum' from the 'Function Name' drop-down and highlight the column used previously to detect duplicates. Press 'Enter.' The result is the sum of all duplicate records.

How to Remove All Macros in Excel 2007

1. Open up your spreadsheet workbook in Microsoft Excel.
2. Press (ALT) (F11). This will open up the VBA editor.
3. Right-click on the module associated with the macro. You'll find all of the modules that are active in your current spreadsheet in the upper left-hand corner.
4. Select 'Remove Module X,' where 'Module X' will be the name of the module you want to remove. Excel will ask if you would like to export Module X. Click 'No.'
5. Repeat steps three and four until you've removed all of the modules associated with your workbook.

Thursday, December 22, 2011

How do I Delete Control From Excel Spreadsheet at Runtime?

1. Click the Office Button, then click 'Excel Options'.
2. Select the 'Popular' category and check the 'Show Developer Tab In The Ribbon' box. Click 'OK'.
3. Open the Developer tab and click 'Design Mode'.
4. Find the control you want to delete. It will be surrounded by a border, either simple and thin, or thick and dotted, depending on whether or not it is an ActiveX control. Click on this border to select the control.
5. Press the 'DELETE' key on your keyboard.

How to Rotate a Worksheet in MS Excel 2003

1. Select the entire table you want to rotate by left clicking at the top, left cell, dragging the mouse down to the bottom, right corner, and releasing.
2. Press 'Ctrl' and 'C' at the same time to copy your table.
3. Select a cell somewhere outside of the table you're rotating. You can even select a cell on a different worksheet entirely.
4. Click the black, down arrow under 'Paste' in the Clipboard section of the Home tab. Select 'Paste Special.'
5. Click the 'Transpose' check box, then click 'OK' to paste the transposed data.

How to Create an Email Distribution Mailing List in Excel

1. Open Microsoft Excel. Start with a new blank document.
2. Type column headers across row one. Your emails created from the distribution list can include any type of personalized information you desire. The most important column that must be included is the actual email address, so be sure one column header says 'Email' or 'Email address' to make this clear. Alternately, you may include first and last names or other information that will be unique for each email.
3. Enter the data for each recipient on a separate row. The first recipient in the email distribution list will be listed on row two, directly under the column headers. Type the email address in the appropriate column and any other information required by your distribution list, such as the recipient's name.
4. Save the Excel document under any file name you choose. Your email distribution list is now complete.

Wednesday, December 21, 2011

How to Calculate Subtotal

Calculating a Subtotal in a Few Easy Steps
1. Obtain a data set. This means that you need to have a set of numerical values listed. Using the example of a checking account deposit, this would be the checks and/or cash amounts that you need to deposit. Deposit slips have blank boxes for you to fill in these values.
2. Write your values down. For instance, you may have a check for $50.27, a check for $85 and $75 in cash that you wish to deposit. In the section for checks on your deposit slip, you would write 50.27 and 85.00. In the blank section for cash, you would record 75.00.
3. Add your data set. Using a calculator or just old-fashioned math skills, you should total your figures. In this example, you would perform the following operation: 50.27 85.00 75.00 = 210.27. This is your subtotal.
4. Perform any additional mathematical steps to your subtotal. Once you've obtained the subtotal, you may need to complete additional steps. For instance, if you are depositing money, but you want to keep out some of the money, you have to subtract from your subtotal. In this example, assume you want to keep out $15.50 from the deposit. You would subtract this amount from the subtotal, as illustrated with the following equation: 210.27 - 15.50 = 194.77. Your final deposit would be $194.77.

How to Set the Defaults for an Excel Comments Box

Set Excel Comment Defaults in Windows XP
1. Right-click the desktop, avoiding any icons. Select 'Properties' to open the 'Display Properties' dialog box.
2. Go to the 'Appearances' tab. Click the 'Advanced' button to open the 'Advanced Appearance' dialog box.
3. Select 'Tooltip' from the 'Item' list near the bottom of the dialog box.
4. Select the new default color for Excel comments in the 'Color1' box. Choose a default font in the 'Font' box, a font size in the 'Size' box and a font color in the 'Color' box.
5. Click 'OK' to apply the changes. Close any open dialog boxes.
Set Excel Comment Defaults in Windows Vista
6. Right-click the desktop, avoiding any icons. Select 'Personalize' to open the 'Personalize' window.
7. Select 'Windows Color and Appearance.'
8. Click 'Open Classic Appearance Properties for More Color Options' at the bottom of the 'Windows Color and Appearance' window to open the 'Appearance Setting' window.
9. Click the 'Advanced' button to open the 'Advanced Appearance' dialog box. Select 'Tooltip' from the 'Item' list near the bottom of the dialog box.
10. Select the new default color for Excel comments in the 'Color1' box. Choose a default font in the 'Font' box, a font size in the 'Size' box and a font color in the 'Color' box. Click 'OK' to apply the changes. Close any open dialog boxes or windows.
Set Excel Comment Defaults in Windows 7
11. Click the 'Start' button. Type 'Window Colors' into the 'Instant Search' box at the bottom of the Start menu and press 'Enter.' The 'Windows Color and Appearance' window will open.
12. Click 'Advanced Appearance Settings' at the bottom of the window.
13. Select 'Tooltip' from the 'Item' list near the bottom of the dialog box.
14. Select the new default color for Excel comments in the 'Color1' box. Choose a default font in the 'Font' box, a font size in the 'Size' box and a font color in the 'Color' box.
15. Click 'OK' to apply the changes. Close any open dialog boxes or windows.

How to Find Cells With Strikethrough in Excel

1. Open Microsoft Excel. Hold the 'Control' key and press the 'F' key on the keyboard. The combination opens the'Find' function.
2. Click on the 'Format' button in the 'Find and Replace' window. Click on the 'Fonts' tab.
3. Check the 'Strikethrough' checkbox in the 'Effects' section and click the 'OK' button.
4. Click the 'Find all' button in the 'Find and Replace' window.
5. Look at the bottom of the 'Find and Replace' window. All the cells with 'Strikethrough' are listed at the bottom of the window.

Tuesday, December 20, 2011

How to Convert Hours to Seconds in Excel

1. Open a new workbook in Microsoft Excel.
2. Enter the time in a blank cell such as 6:20:35 in A1.
3. Click on a blank cell and enter the following formula: =(HOUR(cell)*60*60) (MINUTE(cell)*60) SECOND(cell), where cell refers to the cell reference that contains the time. The formula extracts the hour, minutes, and seconds from the time. It multiplies the hours by 60 to convert into minutes, then multiplies by 60 again to get seconds. The minutes are multiplied by 60 to get the seconds, and the seconds are added to the calculation to get the total seconds.For example, the formula =(HOUR(A1)*60*60) (MINUTE(A1)*60) SECOND(A1) would bring back 22,835 seconds.
4. Format the cell as general by right clicking on the cell and selecting the 'General' category in the 'Number' tab of the 'Format Cells' window.

How to Change the Password in Excel

1. Open the Excel spreadsheet you want to edit. If the file already has a password configured, enter the password and click 'OK.'
2. Click 'File' in the main menu toolbar and select 'Save As.' In the window that opens, click the 'Tools' button to open a dialog box for advanced settings.
3. Click 'General Options.' In the text box labeled 'Password to modify,' enter a new password for your Excel file. Press 'OK.'
4. Click 'Save' to save your new password settings. The Excel file saves to the hard drive and the password is modified.

How to Convert Phone Numbers in MS Excel

1. Open Microsoft Excel. Open the file you’d like to work on by going to “File”, then “Open,” or begin a new workbook using the blank workbook that opens by default.
2. Select the cell that has the numbers typed to be converted to a phone number format. If you have multiple cells that need to be converted, select them all by holding the left mouse button and dragging the mouse.
3. Locate the “Format” option on the toolbar and click once. This will bring up a drop-down menu. Click on “Cells” to bring up a formatting box. You can also pres “Ctrl” “1” to bring up the box.
4. Click “Special” on the “format cells” box. This will bring up four options on the right-hand side, click on “Phone Number” and then click “OK.” This will close the box and format the number to the correctly display in phone number format.

Monday, December 19, 2011

How to Remove a Title Chart in Excel 2003

1. Open the Excel 2003 chart that contains the title you wish to delete.
2. Click the chart title once.
3. Hit the 'Delete' key to delete the chart title in Excel 2003.

Excel Macro Online Tutorial

1. Navigate to the Brown University Excel macros tutorial listed in References. The guide walks you through running macros, creating macros, and running macros using toolbar buttons and shortcut keys. The guide offers plenty of examples and an advanced section on how to edit macro code. The instructions for creating and running the macros are applicable to Excel 2003, but the concepts are the same for Excel 2007.
2. Go to the Help With PCs website listed in the References section. This website includes an Excel macros tutorial, complete with screen shots to help you see what actions you are performing. The beginning tutorial is a guide to creating a simple macro and playing it back. The guide is applicable to Excel 2003.
3. Load the Florida Gulf Coast University website listed in References. Click on 'Macros' toward the bottom on the page to take you to an Excel 2007 online macros tutorial page. The site also offers a variety of other basic help for Excel 2007, accessible by clicking on any menu item.

How to Remove a Sort in Excel 2007

1. Click the 'Office' button at the top left of the Excel interface, then click 'Excel Options,' 'Popular' and 'Edit Custom Lists.' In versions of Excel earlier than 2007, click 'Tools,' 'Options,' then 'Custom Lists.'
2. Click the list you want to delete to select it.
3. Click 'Delete,' then click 'OK.'

How to Sort by Time in Excel 2007

1. Place titles in the top boxes of every column that you wish to fill. For example, if you are listing phone calls, you might want to list the time, date, name and length of the call.
2. Format each column by clicking on the first box under the title box and then holding the 'Shift' key while you press the 'down' arrow. When the number of boxes you will be use are selected, choose the 'Home' tab to see the 'Numbers' group. Click 'Time' for the time column. The rest of the columns can be formatted in the same way but use the option of 'Date' or 'Number.'
3. Highlight the column again to choose a specific format for time and click on the small arrow at the bottom-right corner of the 'Number' group box. This will bring up the 'Format Cells' menu and show all the options available for formatting the text listed. Within the 'time' option, there are several specific formats from which to choose.
4. Group all the completed entries used so far by highlighting them and then sort the entries by the time. Choose the 'Data' tab and then look at the 'Sort and Filter' group. Choose the 'Sort' option to open the menu. Pick the column that holds the time value as the sorting column and then the 'A to Z' option for the earliest to latest time.

Sunday, December 18, 2011

How to Enable Macros in Microsoft Excel 2007

1. Click the Microsoft Office button located in the upper left corner of the computer screen.
2. Select 'Excel Options' from the menu.
3. Click 'Trust Center' to open the 'Excel Trust Center.'
4. Select 'Trust Center Settings.'
5. Click 'Macro Settings' to modify the settings for macros within Microsoft Excel 2007.
6. Select 'Enable all macros' to allow all macros in your spreadsheets to run.

How to Make a Line Graph That Compares Two Things in Excel

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell 'B1.' Enter the name of the first set of data you want to include in your graph. This name will appear as a label next to the line on the graph. Click on cell 'C1' and do the same for the second set of data.
3. Click on cell 'A2.' Enter the X-axis labels into the cells in this column. While the 'Y-axis' in a line graph is always numerical, the X-axis can display numbers, dates, times or even text.
4. Enter your data into the cells just under the headers in columns 'B' and 'C.'
5. Click any cell in your data table. Select the 'Insert' tab at the top of the screen. Click the 'Line' button under 'Charts' and choose one of the line chart types. You can choose a regular line chart, on which each line is plotted based on its value; a stacked line chart, on which the second data set is added to the first; and a 100 percent stacked chart, on which each line is plotted as a percentage of the sum of the lines. Click your selection to create the chart. Excel automatically colors the lines differently to provide contrast between the two data sets.

Saturday, December 17, 2011

How to Divide Excel Pivot Table Data Into Separate Spreadsheets Within the Same Workbook

1. Click on the worksheet in the Excel file that contains the Pivot Table. You can do this by clicking the appropriate worksheet tab at the bottom of the spreadsheet window.
2. View the Pivot Table and identify the summary calculations along its right side. Pivot Tables can create many different types of calculations, but all are based on the groups indicated in the Pivot Table, and the results of these calculations appear to the right of each group's row.
3. Double-click a result calculation in a single row of the Pivot Table. All the records from the original spreadsheet that comprise that Pivot Table group are instantly copied and pasted into a new spreadsheet in the same workbook. The column headers remain intact.
4. Click back to the worksheet that contains the Pivot Table.
5. Double-click the result calculations for each of the other rows in the Pivot Table, using this same process. Excel creates a new spreadsheet for each group. You will have to click back to the Pivot Table worksheet after creating each spreadsheet so you can move on to the next row. Since the Pivot Table contains groups that summarize all the original spreadsheet data, the entire data source is divided into separate spreadsheets after you finish this process.
6. Click the worksheet tabs at the bottom of the Excel window to view the different spreadsheets in this workbook.

How to Convert Numbers to Words in Excel

1. Open Microsoft Excel.
2. Press the 'Alt' and 'F11' keys simultaneously to start the Visual Basic Editor.
3. On the Insert menu, click 'Module' and type the following code into the module sheet (Note: Omit the '*').Option Explicit'Main FunctionFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = ' Thousand 'Place(3) = ' Million 'Place(4) = ' Billion 'Place(5) = ' Trillion '' String representation of amount.MyNumber = Trim(Str(MyNumber))' Position of decimal place 0 if none.DecimalPlace = InStr(MyNumber, '.')' Convert cents and set MyNumber to dollar amount.If DecimalPlace > 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace 1) _'00', 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber
''Temp = GetHundreds(Right(MyNumber, 3))If Temp
'' Then Dollars = Temp Place(Count) DollarsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ''End IfCount = Count 1LoopSelect Case DollarsCase ''Dollars = 'No Dollars'Case 'One'Dollars = 'One Dollar'Case ElseDollars = Dollars ' Dollars'End SelectSelect Case CentsCase ''Cents = ' and No Cents'Case 'One'Cents = ' and One Cent'Case ElseCents = ' and ' Cents ' Cents'End SelectSpellNumber = Dollars CentsEnd Function' Converts a number from 100-999 into textFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right('000' MyNumber, 3)' Convert the hundreds place.If Mid(MyNumber, 1, 1)
'0' ThenResult = GetDigit(Mid(MyNumber, 1, 1)) ' Hundred 'End If' Convert the tens and ones place.If Mid(MyNumber, 2, 1)
'0' ThenResult = Result GetTens(Mid(MyNumber, 2))ElseResult = Result GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd Function' Converts a number from 10 to 99 into text.Function GetTens(TensText)Dim Result As StringResult = '' ' Null out the temporary function value.If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...Select Case Val(TensText)Case 10: Result = 'Ten'Case 11: Result = 'Eleven'Case 12: Result = 'Twelve'Case 13: Result = 'Thirteen'Case 14: Result = 'Fourteen'Case 15: Result = 'Fifteen'Case 16: Result = 'Sixteen'Case 17: Result = 'Seventeen'Case 18: Result = 'Eighteen'Case 19: Result = 'Nineteen'Case ElseEnd SelectElse ' If value between 20-99...Select Case Val(Left(TensText, 1))Case 2: Result = 'Twenty 'Case 3: Result = 'Thirty 'Case 4: Result = 'Forty 'Case 5: Result = 'Fifty 'Case 6: Result = 'Sixty 'Case 7: Result = 'Seventy 'Case 8: Result = 'Eighty 'Case 9: Result = 'Ninety 'Case ElseEnd SelectResult = Result GetDigit _(Right(TensText, 1)) ' Retrieve ones place.End IfGetTens = ResultEnd Function' Converts a number from 1 to 9 into text.Function GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = 'One'Case 2: GetDigit = 'Two'Case 3: GetDigit = 'Three'Case 4: GetDigit = 'Four'Case 5: GetDigit = 'Five'Case 6: GetDigit = 'Six'Case 7: GetDigit = 'Seven'Case 8: GetDigit = 'Eight'Case 9: GetDigit = 'Nine'Case Else: GetDigit = ''End SelectEnd Function
4. Save in the final workbook environment you will be working in, and either create a new copy by selecting 'Save as' every time or continuously update the original workbook.
5. You must enable macros for this function to work. In order to use this function, use one of these methods. Use a direct entry method where you changed 24.35 into 'Twenty Four Dollars and Thirty Five Cents.' Go into the cell or another cell and type: =Spellnumber(24.35).Another method would be cell reference. Do this by referring to another cell you want turned into words. An example is enter =SpellNumber(A1) in another cell and it will turn into 'Twenty Four Dollars and Thirty Five Cents.'You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell:=SpellNumber(A1)

How to Create a Pie Chart in Excel That Illustrates a Portion That Contributes to a Total

1. Open Microsoft Excel. Click into the first cell on the spreadsheet, A1. Type the first sector of items to calculate for the pie chart, such as 'Cats.' Press the 'Enter' key to drop to the next cell, A2. Type the next item, such as 'Dogs.' Continue entering items until all are listed.
2. Click into cell B1. Type the number to use for the pie chart calculations, such as '100.' Press the 'Enter' key to drop into cell B2 and type the number corresponding with the entry in column A. Repeat until all of column A's cells have numbers in column B.
3. Highlight all of the cells you just entered. Click the 'Insert' tab at the top of the workspace.
4. Click the drop-down arrow below the 'Pie' chart button. Click the second button under '2-D Pie,' which is the exploded pie and looks like a Pac-Man. Excel automatically produces a pie chart showing portions of the pie contributing to the total pie.

How to Change Color of Selected Cells in Excel 2007

Manual Color Change
1. Highlight the selected cells you wish to change color. Hold the left mouse button down while running your mouse pointer over the selected cells, provided the cells are continuous. If the cells do not touch each other and are more selective, you can hold the Ctrl (Control) button as you click each cell you wish to highlight.
2. Select the 'Home' tab on the top tool bar. Underneath 'Home' should be seven sections, with labels on the bottom of each section. The section names should be: 'Clipboard,' 'Font,' 'Alignment,' 'Number,' 'Styles,' 'Cells' and 'Editing.'
3. Click the down arrow next to the icon that looks like a paint can, located in the 'Font' section. Select one of the 'Theme Colors' or 'Standard Colors' by clicking on the colored box representing the color you want. If you don't see the desired color, click 'More Colors...' for more standard and custom color options. Once you have clicked on the desired color, the cells you highlighted will change to the color you selected.
Automatic Color Change
4. Highlight the selected cells you wish to change color. Excel 2007 offers conditional formatting enabling you to set the cells to automatically change color based on the rules you set.
5. Select the 'Home' tab on the top toolbar. Click on 'Conditional Formatting' in the 'Styles' section. A drop-down box will appear with options.
6. Move your mouse over 'Highlight Cells Rules' at the top of the drop-down box. Another box will appear to the side listing the most common rules used, plus an option to select 'More Rules...' should you not immediately find what you need.
7. Select 'Greater Than...' or the rule most appropriate for your formatting. If you use 'Greater Than...,' a small box will pop up with the cursor automatically in a blank box on the left and color descriptions in a box on the right of the little screen. The same box will appear for all represented rules, but with different titles for the purpose of the rule's function.
8. Enter the value that highlighted cells should be greater than in order to change color. Click the drop-down arrow next to the color description box on the right, and select one of the predetermined cell formats, or select 'Custom Format...' to create your own rules on how the cells should look. Click OK when you are done. Your cells will now change color depending upon the number entered.

How to Link Excel Charts to Powerpoint

1. Click the 'Start' button on the lower left corner of your screen, and point your cursor to 'All Programs.' Scroll down, and click 'Microsoft Office' in the alphabetical list of your programs, then 'Microsoft PowerPoint.'
2. Click the 'Insert' tab on the ribbon on Microsoft PowerPoint 2007. If you're using PowerPoint 2003, click the fourth menu item, labeled 'Insert,' on the upper left side.
3. Click 'Object' on the right side of the ribbon if you're using PowerPoint 2007 and 'Object' in the 'Insert' menu if you're using 2003.
4. Click the second radio button on the left, labeled 'Create from File,' and click 'Browse....' Click the location of the Excel chart in the left panel, and double-click the file on the main panel on the right.
5. Click the toggle box labeled 'Link' next to the 'Browse...' button. Click 'OK' on the right side. This will link your Excel chart into your presentation.

How to Create a BOM Using MS Excel

1. Start Excel by double-clicking the Excel icon on your taskbar or desktop, or click 'Start,' point to 'Programs' or 'All Programs' and select 'Microsoft Excel.'
2. Go to the 'File' menu in Excel 2003 and select 'New' or click the 'Office Button' in Excel 2007 and select 'New.'
3. Type 'bill of materials' into the 'Search Office Online' under 'Templates.' Click 'Go' or press 'Enter.'
4. Select the BOM template you want to use. Click 'Download.' The template opens as a new Excel worksheet. Go to the 'File' menu in Excel 2003 or the 'Office Button' in Excel 2007 and click 'Save As.' Enter a name for the worksheet and save the file in your desired location.
5. Enter the job name, material descriptions, costs and quantities and dates purchased or used. Save the file again before closing and print as needed.

How to Remove Cells with Zeros from Excel 2007

1. Click 'Alt' and 'F11' to open the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module.'
3. Cut and paste the following code into the blank window:Sub CleanZeros()Dim c As RangeFor Each c In ActiveSheet.UsedRangeIf c = 0 And Len(c) > 0 Then c.DeleteNext cEnd Sub
4. Press 'F5' to run the macro.

Friday, December 16, 2011

How to Share an Excel 2003 Spreadsheet With Multiple Users

1. Open Excel 2003 by clicking the program's icon on your computer's desktop or by selecting the program's name on the “All Programs” menu.
2. Click “File” followed by “Open.” Navigate to the directory containing the spreadsheet you want to share with other users. Double-click the spreadsheet's file name to open the file.
3. Click “Tools” and select “Share Workbook.” The “Share Workbook” dialog box will open.
4. Click the check box next “Allow changes by more than one user at the same time.”
5. Click the “Advanced” tab, if you want to change the default settings related to sharing a spreadsheet.
6. Click “OK” to close the “Share Workbook” dialog box.
7. Click “OK” when prompted with the question “This action will now save the workbook. Do you want to continue?” The Excel 2003 spreadsheet is now shared. You will see the file name of the spreadsheet at the top of the screen and the wording “[Shared]” next to it.

How to Add Minutes Seconds

1.
Add the minutes and the seconds separately.
Add up all the minutes and separately add up all the seconds. For three values as follows, two minutes and 33 seconds, 12 minutes and 10 seconds and 17 minutes and 23 seconds, the total minutes is 31 and the total seconds is 66.
2. Divide the total number of seconds by 60 to convert the seconds into minutes. In the example, 66 divided by 60 is one minute, six seconds.
3. Add the minutes and the seconds. The total is 32 minutes, six seconds.

How to Use the Microsoft Excel Program

1.
Create a chart or graph from information on a spreadsheet. Select the cells to be included in the chart. In Excel 2003, click the 'Chart Wizard' button on the toolbar. In Excel 2007, go to the 'Insert' tab of the ribbon and select a type of chart. Or press the 'F11' key to create an instant, basic chart.
2.
Format the chart by right clicking it and selecting 'Chart Type' to change the type or subtype of the graph. Right click and select 'Format Plot Area' to alter the chart background.
3.
Make a header or footer for the spreadsheet. In Excel 2003, go to the 'View' menu and click 'Header and Footer.' In Excel 2007, go to the 'Insert' tab and click 'HeaderFooter.' Select 'Custom Header' or 'Custom Footer' to enter text or graphics.
4.
Create a drop-down list. Select a cell or cell range to contain a list. In Excel 2003, go to the 'Data' menu and click 'Validation.' In Excel 2007, go to the 'Data' tab and click 'Validation.' Select 'List' under 'Allow' on the 'Settings' tab. Enter the list items in the 'Source' box, with a comma between each one.
5.
Add functions to cells or ranges. Select the cell in which the calculation should appear and click the 'Insert Function' button to the left of the Formula Bar. Type a description of what you want to do and select the function that best suits your need.

How to Make a Graph on Excel With Intervals of 0.5

1. Open the Excel 2010 spreadsheet file that contains the data you want to make into a graph.
2. Click on any cell within the data field that you want to use in your graph, then click the 'Insert' tab at the top of the window. Click on your desired chart type from the Charts area of the ribbon, then click on the specific chart that you wish to create. Excel will place the chart in the middle of your spreadsheet.
3. Click on any of the white space in the chart to select the entire chart, then click the 'Layout' tab at the top of the screen. Click the drop-down box on the left end of the ribbon and choose 'Vertical (Value) Axis.' If you have created an XY Scatter chart and want to modify the horizontal axis, you can instead choose 'Horizontal (Value) Axis.' Click the 'Format Selection' button, located just below the drop-down box, to open the Format Axis window.
4. Select the 'Fixed' radio button next to 'Major Unit' on the right side of the window. Place your cursor into the text box to the right of this radio button and type '0.5' into the box.
5. Click 'Close' to close the window and see your new axis on the chart.

How to Make a List with Colors Using Microsoft Excel 2007

1. Open Excel 2007 and enter data into your spreadsheet. Make sure that you include a few rows and columns as you add your data.
2. Highlight a few rows of data and click the 'Home' tab. Click the 'Fill Color' icon in the 'Font' group. Select a color for these cells.
3. Highlight the remaining rows of data. Click the 'Home' tab and select the 'Fill Color' icon. Select a different color for this set of cells.

Thursday, December 15, 2011

How to Use Excel Dashboard

1. Open Excel.
2. Download or create your data as an Excel spreadsheet, with the first row containing the column names. Select 'Save As' in the 'File' menu, name your file, and click 'Save.' Select 'Exit' on the file menu to close the Excel program.
3. Open the Excel Dashboard program. Select the 'Excel' button. The driver field will auto-fill. Select the 'Access/Excel File' button. Select the file name, and the 'Connect Name' will auto-fill.
4. Select 'New Blank Dashboard' in the 'Choose Action' box. Name the dashboard. Select 'OK.' Expand the menu in the 'Table Browser' window. Double-click on the file to be opened. Select the top alias box to select all of the column titles. Select a column to change its name, if required. Select 'Create table.'
5. Right-click the first item of data in a column. Select 'Create' and 'Create Calculation' to add calculations to the raw data. Select the first column to be used in the calculation. Select the operation symbol. Select the second column to be used in the calculation. Select a name for the 'Total' column. Select 'Use This Formula.'
6. Right-click the title of a column. Select 'Create Chart' from the pop-up menu.
7. Select the type of chart from the drop-down menu in the 'Chart Factory' window. Select the columns to be used for the X and Y axes. Select the Y aggregation type. Select 'Create Chart.' Right-click the 'Get Name' in the 'Chart Editor.' Select 'Full Apply.' Repeat this process to create further charts.
8. Select 'Save As' in the Dashboard 'File' menu. Select the file to be saved or type it into the 'File Name' box. Select 'Close All' from the 'File' menu.
9. Select 'Open' from the 'File' menu. Select the required file. Select 'Open.' Select the 'Name' portlet, and select the scissors to delete the data relating to the charts, if required. Select 'Yes' in the 'Warning' box to delete the table. Repeat this process for other charts that are no longer required.
10. Select 'Save As' in the Dashboard 'File' menu. Type the new file name to be saved in the 'File Name' box. Select 'Close All' from the 'File' menu.
11. Right-click and change the 'Get Name' in the 'Chart Editor.' Select the 'Edit' option, and retype the name.
12. Drag the corners of each portlet to resize the charts. Select 'Edit.' Select 'Resize Mode.' Select 'Align' and the alignment option to do a group alignment.
13. Select 'Save As' in the Dashboard 'File' menu. Type the new file name to be saved in the 'File Name' box. Select 'Close All' from the 'File' menu.

Wednesday, December 14, 2011

How to Sort Data in Excel 2003

1. Open the Excel 2003 file that contains the information you need to sort.
2. Click and hold on the top left cell in the data that you want to sort. Drag the mouse to the bottom right cell and release the button. Your selected data will now be highlighted in a different color.
3. Click 'Data' on the right side of the toolbar on the top of the window. Choose 'Sort' from the drop-down menu to open the 'Sort' window.
4. Click the drop-down arrow next to 'Sort By' and select the column that you want to sort by. You can also choose to sort the data further by selecting additional columns in the 'Then By' fields. Next to each column selection, you can choose to select 'Ascending' or 'Descending' depending on how you want to sort the data. If your columns do not have a header row, select 'No header row' at the bottom of the window.
5. Click 'OK' to close the window, and Excel 2003 will sort your data.

How to Open a 2003 Excel Workfile From 2007 Excel

1. Launch Excel 2007.
2. Click the Microsoft Office button in the top left corner of Excel.
3. Click 'Open.' This brings up a list of files in your default directory. Find the directory where your Excel 2003 file resides if it is not displayed in the default list.
4. Double-click the Excel 2003 file to open it. If you do not see your Excel 2003 document in the list, click the down arrow on the button that says 'All Excel Files,' and select 'All Files' or 'Excel Files' instead. The Excel 2003 file opens in Excel 2007.

How to Sort Numbers Dashes in Excel

1. Launch Microsoft Excel 2010.
2. Right-click on the letter 'A' above the first column and click 'Format Cells' from the context menu. Click the 'Number' tab and click 'Text' in the 'Category' box. Click 'OK' to save the setting and change the way Excel treats column 'A' to text. This allows you to enter and sort numbers that contain dashes.
3. Click the first cell in the first column, or Cell 'A1.' Type '100' and press 'Enter' to save the contents of the cell and move to the next cell down.
4. Type '1200' in the second cell and press 'Enter.' Continue to enter the following series in the next nine cells: '1300,' '1200-12505,' '1200-311,' 1200-312,' '1200-312506,' '1199-5,' '1201-5,' '3-565' and '1200-5.' At this point, the first 11 cells in column 'A' should have unique entries.
5. Click on the letter 'A' above the first column to select the entire column.
6. Click the 'Sort A to Z' button in the 'Sort Filter' group on the 'Data' tab of the toolbar. The whole numbers without dashes are sorted in numerical order at the top of the list, and all of the numbers with dashes are sorted below the whole numbers. The numbers which include dashes are sorted by the first digit, the ones that match in the first digit are sorted by the second digit and so on.

Tuesday, December 13, 2011

How to Remove Duplicates from Excel Macro

Build a Macro to Remove First Column Duplicates
1. Browse to Tools on the toolbar and down to Macro. To the right, find the Visual Basic Editor and left-click on it. Once in the Visual Basic Editor, find the drop-down that allows you to create a User Form, Module or Class Module. Choose 'Module.'
2. Copy and paste the following into the editing box that opens:
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through.
iListCount = Sheets('Sheet1').Range('A1:A100').Rows.Count
Sheets('Sheet1').Range('A1').Select
' Loop until end of records.
Do Until ActiveCell = ''
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row
Sheets('Sheet1').Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets('Sheet1').Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets('Sheet1').Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox 'Done!'
End Sub
3. Click 'File' and then 'Close and Return to Microsoft Excel.' Once returned to Excel, browse to the Tools option in the toolbar and then to Macros. To the right, choose the 'Macros' option. In the Macro option box, see the macro called DelDups_OneList. Choose 'Options' and assign a shortcut key of 'q.' Click 'OK.'
4. Click 'Run.' 'Done' will appear. The duplicate entries in the first column are deleted.
5. Because you have set up your shortcut key for Ctrl q, you only need to type Ctrl q in the future and the duplicate entries in your first column of data will be removed.

How to Create Bar Codes in Excel

Determine Requirements and Install Bar Code Font
1. Determine font requirements. Bar code type determines font requirements. Add-ins covering a number of font types is available but can be costly so a better idea is to purchase either a general-purpose font, or a font specific to the bar code you need. Common bar code fonts include:
Code 39 -- General-purpose bar code
Code 128 -- Used as the basis for Health Industry and Blood Bank bar codes
UCCEAN -- International bar code typically used for shipping/receiving
ITIF14 -- Used to mark external containers of products with an EAN (European Article Numbering) identifier
12of5 -- High-density bar code supporting alphanumeric characters
UPC/EAN -- Most common retail bar code font
GS1 Databar -- Compact version of the UPC bar code that can carry more information and identify small items more easily
2. Ensure the bar code font is compatible with Microsoft Excel. Most fonts will work with Microsoft Excel but double-check to make sure. If you cannot find compatibility information in your user manual, call the manufacturer's customer service department.
3. Read installation instructions and install bar code font add-in.
Create Bar Code
4. Open a new spreadsheet in Microsoft Excel. Bar code fonts run via macros so enable macros before you start. For Excel versions up to 2003, from Tools, click on Macro, and then click on Security. Set the Security level to Low to allow macros to run unrestricted. In Excel 2007, click the Microsoft Office button, then Excel Options, and Trust Center. From the Trust Center, click Trust Center Settings, then Macro Settings. Set macro settings to Enable All Macros.
5. Enter data for the bar code in column A and append asterisks to the data in column B. Data in column A should be in a normal form, such as 123-45-6789. In column B, the same data should be in formula form to append asterisks preceding and following the formula. For example, ='*'A2'*' will append asterisks to the data in column A so it reads *123-45-6789*(assuming the data is on line A2). Enter all data following this procedure.
6. Change the data in column B to a bar code. Select all data in column B (highlight) and choose the bar code font from the toolbar.
7. Format the bar code. First, set the bar code size. If you will be using a scanner to read the bar code, choose a 24-point size. Center the text below the bar code by clicking the Center Text button or use the shortcut Ctrl E. Ensure the entire bar code is visible within the spreadsheet cell by double-clicking the header in column B to auto-fit the contents of the cell.

How to Use Subtotals in Excel 2003

1. Open your Excel worksheet with your data arranged in columns.
2. Select the column you want subtotaled. Hold down the left mouse button and drag down the column.
3. Under 'Data' on the top menu, select 'Subtotal' from the dropdown menu.
4. In the 'Subtotal' pop-up window that appears, check the features you want in your subtotal, and then click 'OK.'
5. A subtotal of your numbers will appear on the worksheet. Save your work.

How to Fix a Corrupt XLS File

1. Locate the file that requires repair. For best results, copy the corrupt file to a stable hard drive, if it is located on removable media or on a networked device.
2. Click 'File->Open...,' (Excel 2003), or the 'Office Button,' then 'Open.' (Excel 2007).
3. In the 'Open' window, select the corrupt file.
4. Click the drop-down arrow next to the 'Open' button, and select 'Open and Repair' from the menu. Excel will open the file in 'File Recovery' mode and attempt to repair the corruption.

How to Create Labels From Excel

1.
Open a blank Excel worksheet. Go to the 'Page Layout' tab of Excel 2007 and select 'Margins.' In Excel 2003 or earlier, go to the 'File' menu, select 'Page Setup' and go to the 'Margins' tab.
2.
Change the 'Top' box to '0' and leave the header at '0.5.' Change both the left and right margins to '0.19.' Change the bottom margin to '0' and leave the footer at '0.5.' Select 'Horizontally' and 'Vertically' under 'Center on Page.' Click 'OK' to apply these changes.
3.
Select cells A1 through A10. If you are using Excel 2007, click the 'Format' dropdown of the Home tab and select 'Row Height.' In version 2003 or earlier, go to the 'Format' menu, point to 'Row' and click on 'Height.' Change the row height to '72' and click 'OK.' Repeat this step, selecting 'Column Width' instead of row height. Change the width to '35.' Apply these changes to cells C1 through C10 and E1 through E10, also.
4.
Select cells B1 through B10. Following the instructions in Step 3, change the column width to '1.29.' Change D1 through D10 to '1.29' also.
5.
Select cells A1 through E10. Click the 'Borders' dropdown arrow, which is in the Font section of the Home tab in Excel 2007 and is on the Format toolbar in Excel 2003 or earlier. Select 'All Borders.'
6. Enter addresses into the cells in columns A, C and E. B and D will be margins between the columns.

Monday, December 12, 2011

How to Validate Data in Excel

1. Know the types of data that you can validate in an Excel spreadsheet. Numbers, dates and times and length can be limited to whole numbers, or ones including decimals, be a minimum or maximum amount or within a range.
2. Use a list of values for the cell. This is helpful when categorizing items with a specific list of data or when the cell always contains one choice such as small, medium or large.
3. Decide if you want the user to view a message when the cell is selected prior to them entering data (input message) or when incorrect data is entered (error message.) Error messages can simply provide information about what should be in the cell, a warning that the data doesn't fit the cell or a stop message not allowing any data that isn't correct to be entered.
4. Set up and name the list. The list can be in the same worksheet or in a different one. Once the data is entered, highlight all relevant cells and click on the 'Insert' command. Choose 'Name' then 'Define.' Enter the equals sign and the name of the list, for instance, '=listname.'
5. Choose one of the cells that you want to validate and select the 'Data' command. Choose 'Validation' and under the 'Settings' tab select the appropriate description for the cell limits. Depending on the choice, additional boxes will appear that need to be filled in.
6. Click the 'Format Painter' icon on the top of the toolbar and click the cell you just validated. Highlight any other cells that have the same validation criteria.

Sunday, December 11, 2011

How to Reset the Excel Document Password

1. Open the worksheet that you wish to change or remove the password for. Enter the password when prompted.
2. Select the 'File' menu to open a left-hand menu pane.
3. Click 'Info.'
4. Click 'Encrypt Workbook' next to 'Permissions.' Another menu will show up.
5. Choose 'Encrypt with password.' A password encryption window will appear and the other menus will close.
6. Remove the password by clearing the 'Password' field and selecting 'OK.' Modify the password by typing in a new one in the 'Password' field, then selecting 'OK.'

How to Make a Pie Chart in Excel 2003

1. Enter the data labels into the cells in one column or row of the Excel worksheet. Enter the data into corresponding cells in the next column or row.
2. Select the data by depressing the left mouse button and dragging the mouse pointer over the cells.
3. Go to the 'Insert' menu and select 'Chart' or click the 'Chart Wizard' button on the standard toolbar to start the 'Chart Wizard.'
4. Select 'Pie Chart' as the chart type. Choose a subtype, such as 'Exploded Pie' or '3-D Pie.'
5. Click 'Next.' Make any other changes or additions in the wizard as desired. Click 'Finish' to complete the chart.

How to Remove Page Breaks From Excel 2007

1. Open the Excel file in which you'd like to remove a page break. Click the 'View' tab at the top of the screen.
2. Click 'Page Break Preview' in the 'Workbook Views' group. This allows you to see your manual page breaks.
3. Click the row or column label after the page break you wish to delete. As an example, to delete the page break between columns E and F, you would click the top 'F' column label, which selects the entire F column. Likewise, click the '20' row label to select the page break between rows 19 and 20.
4. Click the top 'Page Layout' tab.
5. Click 'Breaks' in the 'Page Setup' group, and select 'Remove Page Break.' Alternatively, select 'Reset All Page Breaks' to remove them all.
6. Click the 'View' tab, and click 'Normal' in the 'Workbook Views' group to return to your normal view mode.

Monday, November 28, 2011

How to Use the Quartile Function in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Click on the top-left cell in the spreadsheet.
2. Type your data set into the first column of cells. Each number should have its own cell.
3. Click on cell 'B1.' Type in '=quar' to open a pop-up menu with three options. Double-click 'Quartile.exc' to use the newer version of the quartile function. Double-click 'Quartile.inc' to use the older version of the function. If you need this worksheet to work with earlier versions of Excel, double-click the 'Quartile' function. 'Quartile' and 'Quartile.inc' are the same, but only 'Quartile' works on earlier versions of Excel. Once you make your choice, Excel completes the function name and places an open parenthesis in the formula bar.
4. Click on cell 'A1' and hold down the mouse button. Drag the mouse to the last data entry in the column and release the button. Press the comma key.
5. Enter '1,' '2' or '3' into the formula bar. '1' gives your the first quartile, '2' gives you the second quartile, which is also the median, and '3' gives you the third quartile. If you are using the 'Quartile.inc' function, you can also enter '0' or '4,' which gives your the minimum value and maximum value, respectively.
6. Enter a close parenthesis and press the 'Enter' key. The formula disappears and is replaced by your desired quartile for the data set. The number that appears represents the value where the range of numbers is divided into quarters.

How to Create a Calendar Using Excel

1. Open Microsoft Excel. Click on the 'Tools' menu, point to 'Macro' and click on 'Visual Basic Editor' if using Excel 2003 or earlier. For Excel 2007/2010, click on the 'Developer' tab and then click on 'Visual Basic.' If you don't see the 'Developer' tab, click on the 'Office' button and then 'Excel Options.' On the first screen, click on the check box next to 'Show Developer tab on the Ribbon' and then click 'OK.' The 'Developer' tab should show now.
2. Click on the 'Insert' menu item in the Visual Basic Editor. Click on 'Module' to start a new code module.
3. Copy the first part of the following code in the new module:' Sub CalendarMaker()' Unprotect sheet if had previous calendar to prevent error.ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _Scenarios:=False' Prevent screen flashing while drawing calendar.Application.ScreenUpdating = False' Set up error trapping.On Error GoTo MyErrorTrap' Clear area a1:g14 including any previous calendar.Range('a1:g14').Clear' Use InputBox to get desired month and year and set variable' MyInput.MyInput = InputBox('Type in Month and year for Calendar ')' Allow user to end macro with Cancel in InputBox.If MyInput = '' Then Exit Sub' Get the date value of the beginning of inputted month.StartDay = DateValue(MyInput)' Check if valid date but not the first of the month' -- if so, reset StartDay to first day of month.If Day(StartDay)
1 ThenStartDay = DateValue(Month(StartDay) '/1/' _Year(StartDay))End If' Prepare cell for Month and Year as fully spelled out.Range('a1').NumberFormat = 'mmmm yyyy'' Center the Month and Year label across a1:g1 with appropriate' size, height and bolding.With Range('a1:g1').HorizontalAlignment = xlCenterAcrossSelection.VerticalAlignment = xlCenter.Font.Size = 18.Font.Bold = True.RowHeight = 35End With' Prepare a2:g2 for day of week labels with centering, size,' height and bolding.With Range('a2:g2').ColumnWidth = 11.VerticalAlignment = xlCenter.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.Orientation = xlHorizontal.Font.Size = 12.Font.Bold = True.RowHeight = 20End With' Put days of week in a2:g2.Range('a2') = 'Sunday'Range('b2') = 'Monday'Range('c2') = 'Tuesday'Range('d2') = 'Wednesday'Range('e2') = 'Thursday'Range('f2') = 'Friday'Range('g2') = 'Saturday'' Prepare a3:g7 for dates with left/top alignment, size, height' and bolding.With Range('a3:g8').HorizontalAlignment = xlRight.VerticalAlignment = xlTop.Font.Size = 18.Font.Bold = True.RowHeight = 21End With' Put inputted month and year fully spelling out into 'a1'.Range('a1').Value = Application.Text(MyInput, 'mmmm yyyy')' Set variable and get which day of the week the month starts.DayofWeek = WeekDay(StartDay)' Set variables to identify the year and month as separate' variables.CurYear = Year(StartDay)CurMonth = Month(StartDay)' Set variable and calculate the first day of the next month.FinalDay = DateSerial(CurYear, CurMonth 1, 1)' Place a '1' in cell position of the first day of the chosen' month based on DayofWeek.'Remove the quotation marks on the first and last line of the code.
4. Copy the rest of the code that takes the format from the code above and create the actual calendar. Paste the code right under where the code from above ends.'Select Case DayofWeekCase 1Range('a3').Value = 1Case 2Range('b3').Value = 1Case 3Range('c3').Value = 1Case 4Range('d3').Value = 1Case 5Range('e3').Value = 1Case 6Range('f3').Value = 1Case 7Range('g3').Value = 1End Select' Loop through range a3:g8 incrementing each cell after the '1'' cell.For Each cell In Range('a3:g8')RowCell = cell.RowColCell = cell.Column' Do if '1' is in first column.If cell.Column = 1 And cell.Row = 3 Then' Do if current cell is not in 1st column.ElseIf cell.Column
1 ThenIf cell.Offset(0, -1).Value >= 1 Thencell.Value = cell.Offset(0, -1).Value 1' Stop when the last day of the month has been' entered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ''' Exit loop when calendar has correct number of' days shown.Exit ForEnd IfEnd If' Do only if current cell is not in Row 3 and is in Column 1.ElseIf cell.Row > 3 And cell.Column = 1 Thencell.Value = cell.Offset(-1, 6).Value 1' Stop when the last day of the month has been entered.If cell.Value > (FinalDay - StartDay) Thencell.Value = ''' Exit loop when calendar has correct number of days' shown.Exit ForEnd IfEnd IfNext' Create Entry cells, format them centered, wrap text, and border' around days.For x = 0 To 5Range('A4').Offset(x * 2, 0).EntireRow.InsertWith Range('A4:G4').Offset(x * 2, 0).RowHeight = 65.HorizontalAlignment = xlCenter.VerticalAlignment = xlTop.WrapText = True.Font.Size = 10.Font.Bold = False' Unlock these cells to be able to enter text later after' sheet is protected..Locked = FalseEnd With' Put border around the block of dates.With Range('A3').Offset(x * 2, 0).Resize(2, _7).Borders(xlLeft).Weight = xlThick.ColorIndex = xlAutomaticEnd WithWith Range('A3').Offset(x * 2, 0).Resize(2, _7).Borders(xlRight).Weight = xlThick.ColorIndex = xlAutomaticEnd WithRange('A3').Offset(x * 2, 0).Resize(2, 7).BorderAround _Weight:=xlThick, ColorIndex:=xlAutomaticNextIf Range('A13').Value = '' Then Range('A13').Offset(0, 0) _.Resize(2, 8).EntireRow.Delete' Turn off gridlines.ActiveWindow.DisplayGridlines = False' Protect sheet to prevent overwriting the dates.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _Scenarios:=True' Resize window to show all of calendar (may have to be adjusted' for video configuration).ActiveWindow.WindowState = xlMaximizedActiveWindow.ScrollRow = 1' Allow screen to redraw with calendar showing.Application.ScreenUpdating = True' Prevent going to error trap unless error found by exiting Sub' here.Exit Sub' Error causes msgbox to indicate the problem, provides new input box,' and resumes at the line that caused the error.MyErrorTrap:MsgBox 'You may not have entered your Month and Year correctly.' _ Chr(13) 'Spell the Month correctly' _ ' (or use 3 letter abbreviation)' _ Chr(13) 'and 4 digits for the Year'MyInput = InputBox('Type in Month and year for Calendar')If MyInput = '' Then Exit SubResumeEnd Sub'Remove the quotation marks on the first and last line of the code. Click on the 'Save' icon on the toolbar. Click on the 'File' menu item and then 'Close and Return to Microsoft Excel.'
5. Run the 'CalendarMaker' module. In Excel 2007/2010, click on the 'Developer' tab and then click on 'Macros.' In earlier versions of Excel, click on 'Tools,' point to 'Macros' and then click on 'Macros.' Select the 'CalendarMaker' macro from the list in the window that pops up and then click on 'Run.'

Sunday, November 27, 2011

How to Change the Text Orientation in Cells in Excel 2003

1. Open your Excel worksheet.
2. Select the text cells you wish to change. Hold down the left mouse button and drag across the cells or hold the 'Ctrl' key down as you click on individual cells.
3. Click 'Format' on the top menu to access the dropdown list of formatting options.
4. Select 'Cells.' A new window will appear with cell formatting options.
5. Click on the 'Alignment' tab for options on how to align and orient text in your cells.
6. Under 'Orientation,' change the angle of your text cells until they are oriented.
7. Click 'OK' to implement your changes. Then save your work.

How to Change the Cursor in Microsoft Excel

1. Open Excel.
2. Navigate around the spreadsheet. The mouse cursor remains as a white plus sign whenever the cursor is located over a cell in any of the program's rows or columns.
3. Hover the mouse over a cell that is currently selected to change the cursor to other designs that reflect certain actions. Selected cells have a dark black border around them, and their contents are also displayed in the program's formula bar above the spreadsheet grid. When the mouse is placed along any of the cell's borders, it turns to a quadruple arrow. When displayed, this cursor may be clicked and dragged to move the contents of the cell to another cell.
4. Position the mouse over the lower right corner of the selected cell. The cursor changes to a black plus sign. This is the 'fill handle' and may be used to quickly copy and paste the cell's contents to any adjacent cells. Simply drag the cursor in any direction and the cells copied.
5. Hover the mouse over any part of the Excel formula bar. This is the long white strip at the top of the program window. It is labeled at its start with the term 'fx.' The cursor will change to a standard text input cursor, resembling a capital letter 'I'. When displayed, you may click in the formula bar to edit cell contents. The same cursor will appear if you double-click in any cell.
6. Position the mouse over any toolbar button or menu at the top of the program and the mouse cursor will change to a standard Windows pointer so you may click and access those tools.

Saturday, November 26, 2011

How to Group and Outline Data in Microsoft Excel 2003

1. Select the data that you wish to group and outline. The fastest way to select data for this operation is by clicking and holding the mouse button as you drag the cursor over the desired data and highlight it; release the mouse button when you are finished and the data will remain highlighted.
2. Access the 'Group and Outline' menu. Scroll to the “Data” tab on the command bar and select “Group and Outline Data.”
3. Group and outline the data. From the submenu that opens, scroll to and select “Group Data.” This will group the data and outline the rows and columns of the data that you just grouped. The outline will appear as a bar above the column letters and row numbers, which has a plus sign and a minus sign on the ends.
4. Hide the data group. Click on either sign--the plus sign reveals data and the minus sign hides data--to hide or show the groups of data.

How to Calculate Payback on Excel

1. Open a new Microsoft Excel worksheet and type 'Initial Investment' in cell A1. Put the cost of the project in cell B1. For example, assume a project costs $500.
2. Type 'Annual Cash Inflows' in cell A2. Put your estimated cash inflows each year in cell B2. In the example, assume you receive $60 a year from the project.
3. Type 'Payback' in cell A3.
4. Type '=B1/B2' in cell B3. In the example, B3 will say 8.333333333. The project will take about eight years and three months to cover its costs.

How to Change the Background Color in MS Excel

1. Select the cell or cells in which you wish to change the background color. In Excel 2007, click on the 'Home' tab to view the 'Home' ribbon.
2. Click on the arrow next to the paint bucket icon in the 'Font' section. This is the 'Fill Color' button. In Excel 2003, the paint bucket icon is located in the 'Formatting' toolbar. This toolbar is typically located just above the actual spreadsheet and under the main menu.
3. Select the color you want from the menu that appears. If the color you want is not in the menu, click on 'More colors.' Under the 'Standard' tab you can select from a larger array of colors. Or, under the 'Custom' tab you can enter in the RGB values for your custom color.
4. Click the 'OK' button to apply the background color to the selected cells.

Friday, November 25, 2011

How to Restore Data on Microsoft Excel

1. Run 'Detect and Repair' if you are using Excel 2003 or earlier. Go to the 'Help' menu and select 'Detect and Repair.' Clear the checkboxes and click start. Detect and Repair will scan the program and make certain repairs, which may restore the missing data.
2. Run 'Diagnostics' in Excel 2007. Start Excel and click the 'Office Button.' Select 'Excel Options.' Select 'Resources,' 'Diagnose,' and then 'Continue.' Click on 'Start Diagnostics.' Diagnostics will scan Excel for potential problems and repair what it can.
3. Recover data when Excel stops responding. Go to the 'Start' menu, point to 'All Programs,' and then 'Microsoft Office Tools.' Select 'Microsoft Application Recovery.' Click on Excel in the Recovery window, and then click 'Recover Application.' Excel will close and restart with the files that were open when it stopped responding.
4. Start Excel and go to the 'File' menu or the 'Office Button.' Select 'Open.' Find the damaged file and click on it to select it. Click the drop-down arrow on the 'Open' button at the bottom of the window. Select 'Open and Repair.'
5. Save the file as HTML, if you are able to open it. Go to the 'File' menu or 'Office Button' and click 'Save As.' In the 'Save as Type' select 'Web Page.' When you open the HTML version, save it again as a 'Microsoft Office Workbook' with a different name than the corrupted file.

How to Create Spread Sheets in Excel 2007

1. Click on the 'Office' button. This is the round button with the Microsoft logo at the upper left-hand corner of the ribbon menu.
2. Click 'New' to create a new spreadsheet.
3. Click on the icon for 'New Blank Workbook' in the center pain of the menu that pulls up. If there is no icon for 'New Blank Workbook,' make sure that 'Blank and Recent' is highlighted. You can also click on the button at lower right that reads 'Create.'

How to Calculate Time Duration in Excel 2007

1. Format the relevant cells as time by selecting them and choosing 'Time' from the Number group drop-down menu in the Home tab.
2. Enter a start time and an end time in different cells, such as the start time in cell A1 and the end time in cell B1. Ensure Excel hasn't misinterpreted either time, such as making '9:15 pm' read '9:15 am.' If necessary, type 'am' or 'pm' after the times or use a 24-hour clock, typing '21:15' instead of '9:15 pm.' Investigate Excel's Help menu on 'time of day' if necessary to learn more about these formats.
3. Subtract the start time from the end time to calculate the time duration. In this example, type '=B1-A1' and press 'Enter.'
4. Change the number format for the result, which automatically formats itself as a time of day, which is incorrect. For example, the duration between 2:00 pm and 2:30 pm reads '12:30 am,' which is 30 minutes after zero-hour, or midnight. Instead, choose 'Custom' from the Number group drop-down in the Home tab to set the format correctly.
5. Choose 'Time' in the left-hand panel of the resulting dialog box. Click '13:30,' the second option in the right-hand list, to display the duration in hours:minutes -- in this case, '0:30.' Click '37:30:55' for hours:minutes:seconds.

How to Learn Excel 2007

1. Purchase a copy of Microsoft Excel or download a 60-day free trial of Microsoft Office Professional, which includes Excel 2007.
2. Visit the Microsoft Office website to access dozens of free online Excel 2007 training courses. Take the free 40-minute course called 'Up to speed with Excel 2007.' This course teaches the basics, such as how to use common commands and save workbooks. Take the test at the end of the course to assess how much you have learned.
3. Take the online Excel course called 'Get to Know Excel 2007: Create your first workbook.' The course will teach you how to create a new workbook, how to enter and edit data, and how to insert columns and rows. Once you are finished with the self-paced course, open your copy of Excel 2007 and practice creating your own sample worksheet.
4. Learn to create charts in Excel. Participate in the free online training course called 'Charts 1: How to create a chart in Excel 2007.' Complete the practice assignments at the end of the course, which require you to make a basic chart using Excel 2007.
5. Visit the Excel Help Page on the Microsoft website if you don't have time to take an online course but want to learn about specific features of Excel 2007.

How to Install Analysis ToolPak for Excel 2003

1. Open any Excel document.
2. Navigate to the 'Tools' menu. Click on 'Add-Ins.'
3. Select the check box next to Analysis ToolPak under 'Add-Ins.' Click 'OK.'
4. Click 'Yes' to install Analysis ToolPak if you see the message 'Analysis ToolPak is not currently installed on your computer.' Analysis ToolPak will be installed.
5. Click on 'Tools' under the menu bar. Open a new Excel document. You will see that the data analysis command listed, which indicates the program has been added properly.

How to Combine Column and Scatter Charts in Excel

1. Open the Excel worksheet with the data you wish to chart.
2. Click and drag the cursor to select the data series and the categories.
3. Click the “Insert” tab on the command ribbon.
4. Click the “Column” menu to display a gallery of column thumbnail images.
5. Click the preferred image. The worksheet data converts to an embedded column chart.
6. Right-click on one data series in the plot area that you wish to convert to a scatter chart.
7. Click the “Change Series Chart Type” option. The “Change Chart Type” dialogue window opens with a gallery of chart images.
8. Click the “X Y (Scatter)” tab on the left pane. Click the preferred “X Y (Scatter)” image in the gallery.
9. Click “OK.” The 'Change Chart Type' dialogue window closes. A combination chart displays both the column and scatter charts on one plot area.

How to Change the Order of the Legend in an Excel Chart

1. Right-click on one of the names listed on your legend.
2. Click on the 'Select Data' option from the list that appears.
3. Click on the entry you want to move in the 'Legend Entries (Series)' box.
4. Click the 'Up' or 'Down' arrows in the box to change the position of the legend. Click 'OK' when finished to save your changes.

How to Create a Searchable Database in Excel

1. Type the following data into a new spreadsheet, pressing 'Tab' in place of the commas. This data is for a hypothetical art supply store. Click your mouse on the data's top left cell, then drag down to the bottom right cell to select the data. Type 'ArtProducts' in the left text box above the worksheet grid. This action names the table, which makes it easier to identify in database queries.product, pricepaintbrush, 1.98Gesso, 3.45
2. Click the 'File' menu's 'Close' command, then click 'OK' to indicate you want to save the workbook. Type 'ArtProduct' for the file name, then click 'Save' to save the workbook. Click the 'File' menu's 'New' command to create a new workbook, then click the 'Data' tab's 'Other sources' icon. Click 'Microsoft query.'
3. Click 'Excel files' in the 'Choose data' dialog box, then click 'OK.' Microsoft query will display a dialog box with which to choose an Excel workbook file. Navigate to and double click the workbook you saved in the previous step, then click 'OK.'
4. Click the arrow button in the 'Columns' dialog box to tell Excel you want to base your query on the 'ArtProduct' database listed in the left pane. Click 'Next,' then click the 'View data' option button. Microsoft Query's main window will open.
5. Click the 'View' menu's 'SQL' command, then type the SQL statement following this step into the new dialog box. This statement runs a query on your 'ArtProducts' database to select only those products whose price is greater than $2.00. Click 'OK' to perform the query. Excel will display only the 'Gesso' product, confirming that you've created an Excel database whose records you can selectively search with SQL commands from querying programs like Microsoft Query.SELECT *FROM ArtProductswhere ArtProducts.price>2.00;

Thursday, November 24, 2011

How to Print a Long Row on One Page in MS Excel

1. Highlight the row you want to print.
2. Select 'Page Layout' and then 'Print Area.'
3. Click 'Set Print Area.'
4. Select 'Scale to Fit' under the 'Width' and 'Height' drop-down menu.

How to Convert Microsoft Excel 2003 to 2007

Using Microsoft Excel XP or Excel 2003
1. Open a Web browser window and navigate to the Microsoft Windows Update website. Follow the on-screen instructions to download and install all high-priority updates for your operating system version.
2. Navigate to the Microsoft Download Center page for the Microsoft Office Compatibility Pack. Click the 'Download' button and save the installer file to your computer.
3. Close all open Microsoft Office programs.
4. Double-click the icon of the installer file you downloaded previously. Follow the on-screen instructions to install the Office Compatibility Pack on your computer. The software allows you to save files to XLSX format using Excel XP or Excel 2003.
5. Open Microsoft Excel XP or Excel 2003.
6. Click the 'File' drop-down menu and select 'Open.'
7. Locate the XLS file you want to convert and double-click its icon.
8. Click the 'File' drop-down menu again and select 'Save As.'
9. Give the file a name and select a save location. Choose the 'Excel 2007' option from the 'Save as type' drop-down menu. Click 'Save' to convert the XLS file to XLSX format.
Using Microsoft Excel 2007
10. Open Microsoft Excel 2007.
11. Click the round 'Office' button in the top left corner of the window and select 'Open.'
12. Locate the XLS file you want to convert and double-click its icon.
13. Click the 'Office' button again and select 'Save As.'
14. Give the file a name and select a save location. Choose the 'Excel Workbook' option from the 'Save as type' drop-down menu. Click 'Save' to convert the XLS file to XLSX format.
Using Zamzar
15. Open a Web browser and log on to the Zamzar website.
16. Click 'Choose File' and select the XLS file you want to convert.
17. Select 'xlsx' from the 'Choose the format to convert to' drop-down menu.
18. Enter your email address in the adjacent field.
19. Click 'Convert.' Your file will be converted to XLSX format within minutes. Once finished, a download link will be sent to the email address you provided.

How to Use Excel's MONTH Function

1. Learn the syntax of MONTH. It is MONTH (start_date) where start_date is the date for which you are trying to find the month.
2. Enter start_date using the DATE function or some other function that returns a date to prevent the problems that can occur when a date is entered as text. For example, DATE(2007,4,12) would be used for April 12, 2007.
3. Observe that Excel uses serial numbers internally to store dates. This is a number that represents the number of days beginning from a default date. Windows uses January 1, 1900, as serial number 1 and Excel for the Macintosh uses January 2, 1904, as serial number 1.
4. Examine the return values for the MONTH function. Excel returns the integers 1 to 12, representing the months in the Gregorian calendar, regardless of the display format of serial_number.
5. Study some examples of MONTH by entering 17-Apr-2008 as a date in cell A2. Example 1: =MONTH(A2) will return 4 because April is the fourth month of the year. Example 2: =MONTH(DATE(2007,3,12) will return 3. Note the use of the DATE function to ensure the argument for MONTH is in date format when entering it directly.

How to Edit Charts in Excel 2010

1. Open the spreadsheet containing your chart in Microsoft Excel 2010.
2. Click anywhere on the chart to enable the Chart Tools, which consist of the Design, Layout and Format menu tabs.
3. Click the 'Design' tab to edit the appearance of your chart. Clicking 'Change Chart Type' allows you to select a different format, such as Column, Line or Pie charts. To choose chart data or add a new data series, use the 'Select Data' group. Clicking an option from the Chart Layout group applies a design template that alters the positioning of labels and data. Clicking an option from the Chart Styles group changes the chart's color scheme.
4. Select the 'Layout' tab to change labels or add special formatting to the chart. The Insert group offers the option to add pictures, shapes and text boxes. The Labels group lets you change titles, labels and legends. The Analysis group allows you to add trend lines and error bars to the chart.
5. Click the 'Format' tab to access additional design features, such as borders, color fills and shape effects. Clicking an option from the WordArt Styles group changes all text on the chart. The Size group allows you to edit the horizontal and vertical size for the chart.

Wednesday, November 23, 2011

How to Remove Blank Cells in Excel 2007

1. Select the range of cells that contains blanks to remove.
2. Click 'Find Select,' at the far right of the Home tab.
3. Choose 'Go to Special....'
4. Click the 'Blanks' radio button in the resulting window.
5. Click 'OK' to close the window and highlight all blank cells in the selected range.
6. Click the 'Delete' arrow button on the Home tab and choose 'Delete Cells....'
7. Choose an option for filling in the blanks: Shift cells left or up, or delete entire rows or columns.

How to Insert a Degree Symbol in Excel

1. Turn on the number lock function on your keyboard's number pad by pressing the 'Number Lock' button. You can only enter the degree symbol by using the number pad (not the row of numbers above the letters.)
2. Click in the cell into which you wish to type the degree symbol.
3. Locate the 'Alt' key on your keyboard.
4. Hold down the 'Alt' key with your left hand, and type '0176' simultaneously on the number pad. You should see the degree symbol appear in your cell.

How to Create High Resolution TIF Files From Excel

1. Open Microsoft Excel by selecting it from the Start menu or double clicking on the Microsoft Excel icon on your desktop. Open the worksheet you want to convert by clicking 'File' and 'Open.' This will open a search window. Locate your document and then click 'OK.'
2. Edit your worksheet, if needed, until you have achieved the final version that you want to appear in the TIFF file. This is the time to hide any columns or rows you do not want to appear in the final document as well as add design elements such as shading and borders. Click 'File' and 'Print Preview' to preview the file and make sure it looks exactly the way you want.
3. Press the 'Print' icon on the upper left corner of the print preview screen to open the print dialog box. Click on the arrow next to the printer 'Name' box to select a different printer. Locate 'Microsoft Office Document Image Writer' and select. Then click the 'Properties' button to open the properties dialog box.
4. Select the 'Layout' tab on the upper left corner of the properties dialog box. Click the 'Advanced' button to open the 'Microsoft Office Document Image Writer Advanced Options' box. Under 'Output Format' select 'TIFF- Monochrome Fax.' Then click 'OK' to accept the option. Keep clicking 'OK' until you return to the Printer dialog box and then click 'OK' one last time to start the process. A small pop-up window will tell you that the document is printing. Then a 'Save As' dialog box will open.
5. Enter the name you want to save the file under in the 'Name:' box. Make sure the document is saving in the correct file location or click the down arrow next to the 'Save in:' box to select a new location. Then click 'Save' to finish the process. Your new TIFF file is now ready to be opened.

How to Delete Data in Excel Not Formulas

1. Click on the cell containing the data you wish to delete. Select more than one cell by holding down the 'Ctrl' key while clicking on the other cells.
2. Go to the 'Edit' menu at the top of the page and highlight the 'Clear' option.
3. Choose 'Contents' by clicking on it to delete only the data and not the formulas from the selected cells.

How to Make a Gantt Chart

Outlining Your Project
1. Document an overview or synopsis of the project before plotting a Gantt chart.
2. Make a list of the tasks required for completion of the project and the dates that each of these tasks needs to be completed by.
3. Create a new spreadsheet.
4. List each of the tasks that must be completed during the course of the project in the far left-hand column. Skip a row at the end of the list.
5. Place the dates of completion for each of the required tasks in a cell on the following row. Allow spaces between the date cells if they are needed for readability.
6. Select the cell that represents the start date of the task, click the 'Format Cells' function and fill the cell with color to indicate the beginning of a task. Repeat this action across the row. Click and drag the mouse over a group of cells to select the cells all the way to the project's end date. Each task needs to be plotted according to its beginning date and end date.
7. Format the text with bolding or italics; add headings and shading to enhance the visual appeal and readability of the chart; or create borders if appropriate.

Subscribe to: Posts (Atom)