Thursday, June 14, 2012

How to Recover an Excel Work File That Wasn't Saved


File Never Saved
1. Reopen Excel.
2. Click on 'File' in the upper-left hand corner of the screen. Then click 'Recent.'
3. Select 'Recover Unsaved Workbooks.' This will display a list of any workbooks you worked on that were not saved.
4. Select your file you were working on. Excel will record the time that the file was last worked on, so you can tell which file is the one you want if the time is close to when you closed Excel without saving or Excel crashed.
5. Click 'Open.'
6. Click 'Save As' to save your file. You need to name the file and then click 'Save.'
Files Previously Saved
7. Open the document you were working on.
8. Click 'File' in the upper left-hand corner of the window.
9. Click 'Info.'
10. Under Versions, click the version labeled '(when I closed without saving)'.
11. Click 'Restore.'
Read more ►

Wednesday, June 13, 2012

How to Sort Alphabetically in Excel 2003


1. Enter your data into a column or row in Excel.
2. Click on the column letter on the top of the spreadsheet or the row number on the side of the spreadsheet that you want to alphabetize.
3. Click the button with an A on top of a Z near the top of the window if you want to sort from A to Z or click the button with a Z on top of an A if you want to sort from Z to A.
Read more ►

How to Save Print Settings in Excel 2007


1. Open Microsoft Excel from the Windows Start Menu by clicking 'Start' > 'Program Files' > 'Microsoft Excel'. The application will start in a new window.
2. Open your spreadsheet from the top pull-down menu. Click 'File' and 'Open...', and then select your spreadsheet file using the file browser window.
3. Select the cells you want to print by clicking and dragging your mouse on the spreadsheet to highlight the cells of your choice.
4. Open your Page Setup settings from the top menu by clicking 'File' > 'Page Setup...'. Adjust the margins and page orientation to fit what you are printing through your custom view.
5. Click on 'View' > 'Custom Views' from the top menu to open a new dialog box which will allow you to add a new custom view for printing. Click on the 'Add...' button in the Custom Views dialog box.
6. Type in the name you want to use for your new custom view in the 'Name:' text field. Keep the 'Print Settings' and 'Hidden Rows, Columns and Filter Settings' options selected and click the 'OK' button to add your new print view.
7. Click on 'Views' > 'Custom Views' again to list the created custom views in your document. To load the view, click the 'Show' button. When you print your document, the print settings that were saved with your custom view will be used.
Read more ►

How to Stop Excel Windows From Maximizing


1. Click 'Start' and open 'All Programs.'
2. Right-click on 'Microsoft Excel.'
3. Click 'Properties' and choose 'Shortcut.'
4. Open the 'Run' pull-down menu and click 'Minimized.'
5. Click 'Apply.' Click 'OK' to save your changes. Excel windows will now stay minimized.
Read more ►

How to Add Borders to Cells in Microsoft Excel 2007


1.
Select the cell or region of cells you would like to add borders to.
2.
Click on the 'Border' icon located on the Home tab of the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.
3.
Select the style of border you would like applied to your selected cells.
4.
Select the cell or range of cells you would like to edit the formatting on. For example, you can change the color of a selected border.
Read more ►

How to Compress Pictures in Excel


1. Open Microsoft Excel 2007 and start a new workbook and insert at least one picture into it, or open an existing workbook from your files that contains at least one picture you would like to compress.
2. Click to select the picture that you want to compress. After you have clicked the picture it will be surrounded by white sizing handles indicating that it is selected.
3. Choose the 'Format' tab at the top of the screen to display the Format ribbon. The Format ribbon contains all the commands you can use to format a picture in Excel.
4. Select the 'Compress Pictures' button in the 'Adjust' section of the Format ribbon. The 'Compress Pictures' dialog box will open.
5. Click to add a checkmark so you compress only the selected pictures and click the 'Options' button in the 'Compress Pictures' dialog box. The 'Compression Settings' dialog box will open.
6. Choose your compression options and target output for the selected picture in the 'Compression Settings' dialog box. You can choose for the compression to occur on saved and for any cropped areas of the picture to be deleted. You can also choose to for the picture to compress depending on whether you intend to print it, view it in Excel or email it. Click the 'OK' button after you have made your selections to close the dialog box.
7. Click 'OK' to close the 'Compress Pictures' dialog box and compress the selected picture.
Read more ►

Tuesday, June 12, 2012

How to Change Text to Uppercase in Excel 2007


1. Insert a blank column to the left of the column with cells that need to be converted to uppercase text by right-clicking on the column-header of the source cells and choosing 'Insert' from the shortcut menu that appears.
2. Use the UPPER function to convert the text by clicking in the first cell of the new column and typing the following formula: =UPPER(A1) where A1 is the cell reference of the first cell with lowercase text. Use your keyboard to hit the Shift Enter keys. You will see the result of the formula while staying in the active cell.
3. Fill the formula down to all additional cells in the new column as needed to convert lowercase text from the source column. To fill down easily, position the mouse over the bottom right-hand corner of the current cell, and when the solid, black cross-pointer appears, click and drag the mouse downward. You should now have a new column of uppercase text.
4. Delete the old column of lowercase text by right-clicking on the column header and choosing 'Delete' from the shortcut menu that appears. If this is not convenient because of your spreadsheet's layout, then copy the cell containing the first UPPER formula and then right-click on top of the first lowercase cell, choose 'Paste Special' from the shortcut menu and then choose the 'Values' option and click the 'OK' button.
5. Fill down the formula to the rest of the lowercase cells as needed. This option will overwrite any cells in the range which happen to contain a formula.
Read more ►

How to Edit Macros in Excel


1. Open an existing spreadsheet or workbook in Excel that has at least one macro already created. Go to the 'Tools' menu bar and select 'Macro' and then 'Macros.'
2. Type the name of the macro you wish to edit into the 'Macro Name' box. Click on the desired macro if multiple appear.
3. Click the 'Edit' button. The Visual Basic editor will be displayed in a separate window with the code for that macro displayed.
4. Make the desired changes to the macro. Leave the first line starting with 'Sub' and the last line should remain 'End Sub.'
5. Close the box with the edited information. The changes are saved automatically. Test the macro changes by going back to the spreadsheet you wish to use it on, and highlighting the cells it is to be used on.
6. Select 'Tools', then 'Macro' and 'Macros.' Choose the macro you just edited and click the 'Run' button. Save the spreadsheet again to save the changes to the macro.
Read more ►

How to Remove Recent Documents From Excel 2007


1. Click 'Start,' 'All Programs,' 'Microsoft Office' and then select 'Microsoft Office Excel 2007.'
2. Click the 'Office' button in the upper left corner of the screen and select 'Excel Options' at the bottom of the window.
3. Select 'Advanced' from the column on the left side of the 'Excel Options' window.
4. Scroll to the 'Display' section and then adjust the number to '0' next to 'Show This Number of Recent Documents.'
5. Click 'OK' to close the window and apply your changes.
Read more ►

Monday, June 11, 2012

How to Create a Regression Equation Chart in Excel 2003


1. Input your data into your spreadsheet in two columns. For example, use fertilizer use and crop yield. Make sure the data 'lines up' -- that the fertilizer use and crop yield of plot A are in the same row; the same for plot B and so on.
2. Select the height and weight data that you want to graph and use the Chart Wizard to plot the data. The dependant variable, the one that you expect is affected by the other variable (in this case crop yield), should be on the y-axis.
3. Right-click on one of the data points on the graph and select 'Add trendline.'
4. Select the 'Linear' trend/regression type and then click on the 'Options' tab.
5. Tick the box for 'Display equation on chart' and click 'OK.'
6. Move the text box containing the equation to somewhere clear of data points to improve the clarity of the graph.
Read more ►

How to Add a Data Table to an Excel Chart


1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like add a data table.
2. Select the chart you would like to add a data table to by clicking on it. You should see a light blue border surrounding the chart once it is selected.
3. Choose the 'Layout' tab at the top of the Excel 2007 screen to display the options in the 'Layout' ribbon. Find the 'Labels' group near the center of the groups in the 'Layout' ribbon.
4. Click the 'Data Table' button in the 'Labels' group of the 'Layout' ribbon. A drop-down list of options will appear.
5. Use the 'Show Data Table' from the drop-down list to display a data table for the selected chart. Choose 'Show Data Table with Legend Keys' to display a data table that includes a legend key to help users read data. Whatever data table you choose will be displayed at the bottom of the selected chart.
Read more ►

How to Convert Mailing Labels to Columns in Excel


1. Open the Word document containing the mailing labels. In Word 2003 or earlier, go to the 'File' menu, select 'Open,' click on the document name and click the 'Open' button. In Word 2007, click the 'Office Button,' select 'Open,' click on the document name and click the 'Open' button. In Word 2010, click the 'File Tab,' select 'Open,' click on the document name and click the 'Open' button.
2. Select all of the information in the labels by clicking the 'Table Move Handle' at the top left side of the table. This handle looks like crossed, double-headed arrows. Because Word views the label document as a table, clicking this button will highlight and select the entire document. You can also select all of the information on the tables by clicking anywhere in the document and pressing the keyboard shortcut 'Ctrl A.'
3. Copy the selected information by clicking the 'Copy' button on the 'Standard' toolbar in Excel 2002 or 2003 or in the 'Clipboard' group of the 'Home' tab in Excel 2007 or 2010. You can also use the keyboard shortcut 'Ctrl C.'
4. Open the Excel worksheet where you want to convert your mailing labels. Click inside the top cell of the first column into which you want to paste the data. Although you are only clicking inside of one cell, Excel will convert the data into the specific number of columns and rows required, saving you from having to count the number of cells into which you need to paste the data.
5. Click the 'Paste' button on the 'Standard' toolbar in Excel 2002 or 2003 or in the 'Clipboard' group of the 'Home' tab in Excel 2007 or 2010. You can also use the keyboard shortcut 'Ctrl V.'
6. Click the 'Paste Options' button that appears as a small clipboard at the bottom right side of the pasted data. Select 'Keep Source Formatting' if you want the converted information to match the formatting used in the Word labels. Select 'Match Destination Formatting' if you want the formatting you are using in your Excel worksheet applied to the data.
Read more ►

How to Use Excel for Optimization Calculations


1. Compute an equation for the optimization. For example, suppose the problem is to create a fence that encloses 100 square feet while using the least amount of fencing. Then, the two equations would be 'area = length * width' and 'total fence = 2*l 2*w.'
2. Enter the formulas into Excel. Enter an example length of 25 feet in box 'A1.' Enter an example width of 4 ft in box 'A2.' Type '=A1*A2' into box 'A3.' Box 'A3' is the area parameter. Type '=(2*A1) (2*A2)' into box 'A4.' Box 'A4' is the perimeter which is to be optimized.
3. Select 'Solver' from the 'Tools' menu.
4. Enter the value to be optimized into the 'Target Cell' box. Type 'A4' into the 'Target Cell' box, as this is the perimeter to be minimized. Click on the 'Min' dial.
5. Enter the values that can be changed into the 'By Changing Cells' box. Type 'A1, A2' into the 'By Changing Cells' box, as the length and width are the parameters that can be changed.
6. Enter the problem constraints into the 'Constraints' box. Type 'A3 = 100' into the 'Constraints' box, as this is the constraint of the problem.
7. Press 'Solve.' In this example, the length and width would both be 10 feet, to produce a minimum fence length of 40 feet.
Read more ►

How to Use Minutes Seconds in Excel


1. Select and highlight the group of cells in your spreadsheet (such as a column or a row) where data will be entered as time in minutes and seconds.
2. Select 'Cells' under 'Format' in the top menu bar.
3. Click the 'Number' tab and select the 'Custom' option from the list of categories on the left.
4. Select 'h:mm:ss' from the list of custom format or type 'h:mm:ss' in the field under 'Type.'
5. Click the 'OK' button.
Calculating Time
6. Select the cell that will be used to calculate the total number of minutes and seconds entered.
7. Type the following part of the formula, '=sum(' then select the first cell containing the data.
8. Type ' ' and then select the next cell containing the data. Repeat this step to select the cells to be calculated.
9. Type ')' to complete the formula and press the 'Enter' key.
Read more ►

How to Create Invoice Templates in MS Excel


1. Open Excel. Click on the 'Office' button. Select 'New.' Choose 'Blank and recent' from the 'Templates' pane. Click 'Blank Workbook' in the middle pane. Click 'Create' Button.
2. Select column A by clicking at the top of the column with the mouse. Hold the 'Shift' key while clicking the mouse on column F. Right-click on the selection. Set 'Column Width' to 15.
3. Click in cell A1. Select 'Home' from the menu bar. Choose 'Cell' and 'Format' from the toolbar. Set the 'Row Height' to 58.
4. Choose 'Insert' from menu bar. Select 'Picture' or 'Clip Art' from toolbar. Choose and insert picture or clip art.
5. Right-click on the picture with the mouse. Select 'Size and Properties.' Set 'Height' for the picture in the 'Size' tab under 'Size and Rotate.' Check 'Lock Aspect Ratio' under 'Scale.' Click 'Close' button.
6. Highlight columns B, C and D. Select 'Merge and Center' from 'Alignment' group on the 'Home' tab. Type your company motto.
7. Highlight columns E and F. Select 'Merge and Center' from 'Alignment' group on the 'Home' tab. Type your company motto. Type 'Invoice' in cell E1.
8. Type your company name, address, city, state, ZIP code and contact numbers in cells A3 to A6. Type: 'Invoice:' in cell D3; 'Date:' in cell D4; and 'To:' in cell A9.
9. Type 'Payment Terms' in cell A15 and 'Due Date' in cell B15. Select and highlight these cells. Right click on selection. Select 'Format Cells.' Choose a light gray color from 'Background Color' area under 'Fill' tab. Click 'OK' button.
10. Type: 'Quantity' in cell A18; 'Description' in cell B18; 'Unit Price' in cell C18; and 'Line Total' in cell D18. Select and highlight these cells. Right-click on the selection. Select 'Format Cells.' Choose a light gray color from the 'Background Color' area in the 'Fill' tab. Click the 'OK' button.
11. Select and highlight from cells A18 to F38. Right-click on the selection. Choose 'Format Cells.' Under the 'Border' tab, set 'Color' to 'Automatic.' Click on 'Outline' and 'Inside' boxes under 'Presets.' Click the 'OK' button.
12. Type: 'Subtotal' in cell E39; 'Sales Tax' in cell E40; and 'Total' in cell E41.
13. Select and highlight from cells E38 to F40. Right-click on the selection. Choose 'Format Cells.' Under 'Border' tab, set 'Color' to 'Automatic.' Click on 'Outline' and 'Inside' boxes under 'Presets.' Click 'OK' button.
14. Highlight and select cells E19 to F40. Right-click on the selection. Choose 'Format Cells.' Select 'Currency' from 'Category' pane under 'Number' tab. Click the 'OK' button.
15. Type '=A19*E19' in cell F19. Copy and paste formula from F19 to cells F20 through F37.
16. Type '=SUM(F19:F39)' in cell F40. Save file as an Excel Template (*.xltx).
Read more ►

Blogger news