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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.'
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.'
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.)
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.'
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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)
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

Blogger news