June 2012 - Excel

Browse » Home » Archives for June 2012

Thursday, June 28, 2012

How to Round Up in Excel 2007

1. Open the spreadsheet that contains the data that you want to round up.
2. Right-click the column heading of the column directly to the right of the column that contains the data that you want to round.
3. Click 'Insert' in the pop-up menu to insert a new column to the right of the data column. You'll use this column to store the rounded version of your original data.
4. Click the cell that is directly to the right of the first value that you want to round up.
5. Type the following function in the cell: =ROUNDUP(B1,1)Substitute the address of the first to-be-rounded data cell in place of 'B1' in the above example. The integer after the comma represents the number of decimal points to which you want to round. In the example, the value 3.60 in cell B1 would be rounded to 3.6, while the value 3.65 in cell B1 would be rounded to 3.7.
6. Right-click the cell that contains your roundup function and click 'Copy' in the pop-up menu.
7. Highlight all cells in your function column that are adjacent to the to-be-rounded data. Right-click the highlighted area and click 'Paste' in the pop-up menu. The rounded values will appear in the pasted cells.

How to Merge Cells With Different Formulas in Excel

Concatenate Function
1. Open the Excel file in which you want merge cells.
2. Right-click on the column header directly to the right of the columns to be merged. For example, if the A2 contains 'First name,' B2 contains 'Middle name,' and C2 contains 'Last name,' right-click on the header for Column D.
3. Select 'Insert' from the menu that opens. This will move all columns to the right and will insert a blank column that will become the new Column D.
4. Click in cell D1 and type 'Full name.'
5. Click in cell D2 and type the following formula:=CONCATENATE(A2,' ',B2,' ',C2)This formula will return the combination of cells A2, B2, and C2 with a single space in-between each cell's contents. For example: If A2 contains 'Mickey,' B2 contains 'M' and C2 contains 'Mouse,' then the formula will return 'Mickey M Mouse.'If you do not need spaces between the cells, change the formula to the following:=CONCATENATE(A2,B2,C2)The formula will return 'MickeyMMouse.'
6. Copy and paste the formula down all rows you want to merge.
Function
7. Complete Steps 1 through 4 from Section 1.
8. Click in cell D2 and type the following formula:=SUM(A2' 'B2' 'C2)This will return 'Mickey M Mouse' in cell D2.
9. Copy and paste the formula to all rows in which the data needs merged.

Wednesday, June 27, 2012

How to Import Data From MS Word to MS Excel

1. Open the Microsoft Word document. Click the 'File' tab in Word 2010, 'Microsoft Office Button' in Word 2007 or 'File' menu in Word 2003. Click 'Save as.' In the 'Save as Type' menu, select 'Plain Text,' then click 'Save. This will save the Word document as a text file that Excel can read. It will remove any formatting you added in Word.
2. Open a blank worksheet in Microsoft Excel.
3. Click 'File' or the 'Microsoft Office Button, then 'Open.' Select 'Text Files' from the file type list. Select the text file you just saved and click Open.' Excel will automatically launch the Import Text Wizard.
4. Choose the original data type. Select 'Delimited' if each piece of data in your text file is separated by tabs, spaces, commas or other characters. Choose 'Fixed Width' if each field of data is the same width. Click 'Next.'
5. Choose which delimiter is used by your data. This is the character that separates values of data in the text file. For instance, if your data is separated by tabs, select 'Tab.' This will determine how Excel will separate the data into cells. See how the text is affected in the preview at the bottom. Click 'Next.'
6. Select the column data format you want Excel to use for each column that appears in the Data Preview. If a column contains a mix of numbers and letters, Excel will convert the column as 'General.'
7. Click 'Finish' to import the text.
8. Click 'File' or the 'Microsoft Office Button,' then 'Save as' to save the Excel file.

Tuesday, June 26, 2012

How to Use a ListView Control

1. Open Microsoft Excel.
2. Click 'Tools,' 'Macro' and then 'Visual Basic.' This will open the VBA editor.
3. Click 'Insert' and 'UserForm.' This will place a UserForm on your screen, including the VB ToolBox that contains the controls to use on the UserForm.
4. Add the ListView object onto the ToolBox by clicking 'Tool' and 'Addition Controls.' This opens the ActiveX control box. Scroll down and select 'Microsoft Listview Control' and click 'OK.' This will add the ListView object to the ToolBox.
5. Select the 'ListView' from the ToolBox and draw it onto your UserForm.
6. Double-click on the UserForm. This will open the UserForm's code window.
7. Highlight and select all the content within the Form's code window and press 'Delete' on your keyboard.
8. Type or copy and paste the following code in the code module:Private Sub UserForm_Activate()With ListView1.ListItems.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'End WithEnd SubThe code adds a ListView object onto a UserForm and then uses the ListView's listitems method to populate it with several items. The code is inserted in the UserForm's activate event procedure so the code populates the ListView object instantly at the running of the program.
9. Press 'F5' on your keyboard to run your program; you should have a UserForm on your screen. You should see your ListView object populated with a number of items.

How to Use the BINOMDIST Function in Microsoft Excel

1. Start Microsoft Excel. You can do this by clicking on Start, highlighting Programs, and clicking Microsoft Excel, or double-clicking the Microsoft Excel icon on your computer's desktop.
2. We just need one cell for this function, so we will use cell A1. Select cell A1. At the top of the screen, click on Insert, and click on Function. Type BINOMDIST in the Search for a function dialog box, click Go, and click OK. Note that you do not want to choose NEGBINOMDIST since it is a separate function. It is the negative binomial distribution, which deals with the probability of a number of failures before a success.As a second possibility, you could select Statistical from the Or select a category drop-down menu, highlight BINOMDIST, and click OK.
3. A dialog box appears, asking you to input the function arguments. Arguments are values used to perform a calculation. The first is Number_s, which is the number of successes in trails. Type 5 here. The second is Trials, which is the number of independent trials. Input 10 here. The third is Probability_s, the probability of a 'success' in each trial. Here, it would be the probability of getting a head, which is .5. The last argument is Cumulative, and you enter TRUE or FALSE here. Since we are interested in the chances of obtaining EXACTLY five heads, enter FALSE. Entering TRUE would give the probability of getting AT MOST five heads.
4. Click OK. The answer, rounded to four decimal places, is .2461. Again, this is the probability of getting EXACTLY five heads on ten flips of a balanced coin.

Monday, June 25, 2012

How to Improve the Print Resolution of Excel Charts

File Prep
1. Finish drawing up your Microsoft Excel chart as you would normally.
2. Expand the size of the chart if necessary by grabbing a corner of the image with your mouse and dragging it outward.
3. Position the chart on the page exactly as you want it to look when it is printed. Confirm that the chart is positioned inside of the printable page by choosing 'Print Preview' from your menu.
Acrobat Method
4. Go to your Acrobat tab in Excel. Select 'Preferences' and then 'Advanced Settings' to check the resolution. If you are using Adobe's online PDF creation tool, called 'CreatePDF,' go to 'Set Options' and then 'Document Options' under your preferences. Select 'Print' as your desired profile. Click 'Edit Settings' and then 'Edit' once more to make changes to the resolution.
5. Assure that the resolution is set to at least 300 dpi (600 if you want a high-quality print).
6. Convert the file to PDF. Print your Excel chart from the PDF file to get a high-resolution print.
Photoshop Method
7. Install a postscript printer driver to your computer (see direct link in the Resources section).
8. Go to 'Print' on your Excel menu and find the postscript printer, which will likely be named 'General Postscript' or similar.
9. Click 'Properties' to the right of the printer name in your dialog box then 'Advanced.' Scroll down to 'Document Options' and click the plus sign beside 'PostScript Options.' Under 'PostScript Output Option' change the selection to 'Encapsulated PostScript (EPS).'
10. Press 'OK' to go back to the main print dialog box and select 'Print.' Save the file in a name that you will remember with the extension '.eps' at the end. (To find the file you may have to do a search of your computer files and folders.)
11. Open the EPS file in Adobe Photoshop. You will be prompted to enter the desired resolution before the file is opened. Set it to a minimum of 300 dpi and then open the file.
12. Save the file as either a PDF or TIF file in CMYK format (go to 'Image' and then 'Mode,' and choose 'CMYK Color' on the Photoshop menu). Print the Excel chart from Photoshop or another image-editing program.

How to Sort a Row or Column in Excel 2003

1. Open your Excel worksheet after logging on to your computer. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Click on any cell within the range of cells you would like to sort. Go to 'Data'. Select 'Sort' from the appearing menu.
3. Choose 'Options'.
4. Select 'Sort Left to Right' from the displayed 'Orientation' menu. Choose 'OK'.
5. Select the rows to sort from the 'Sort By' and 'Then By' boxes. Sort one column without affecting others by clicking the column heading of the column to sort.
6. Choose 'Sort Ascending' or 'Sort Descending'. A 'Sort Warning' will be displayed.
7. Select 'Continue with the Current Selection'. Then click 'Sort'.

Sunday, June 24, 2012

How to Disable a Hyperlink Warning in Excel 2003

1. Click 'Start.' Select 'Programs' or 'All Programs' and click 'Run' in the 'Accessories' folder. The 'Run' pop-up dialog window will open.
2. Type 'regedit' in the 'Run' dialog box and click 'OK.' The 'Registry Editor' will open.
3. Navigate to 'Hkey_Current_User >Software >Microsoft >Office >11.0 >Common.' Click 'Edit' at the top of of the 'Registry Editor' window and select 'New,' then 'Key.'
4. Type 'Security' in the 'New Folder' field and press 'Enter.' Click 'Edit' at the top of of the 'Registry Editor' window and select 'New,' then 'DWORD Value.'
5. Type 'DisableHyperlinkWarning' in the 'DWORD' name field. Right-click 'DisableHyperlinkWarning' and click 'Modify' from the pop-up menu. Select the 'Decimal' radio button in the 'Edit DWORD Value' dialog box
6. Type '1' in the data value field. Click 'OK.'

How to Calculate PMT in Excel

1. Open Excel 2010, and click the 'fx' button on the formula bar. Type 'PMT' to locate the PMT function. Press 'Enter' on the keyboard. Click the 'PMT' function.
2. Add the variables to the PMT function. Enter the current interest rate for the loan in the 'Rate' section. Enter the number of payments in the 'Nper' section.
3. Enter the present value of the loan in the 'PV' section. Click 'Ok' to see the loan payment appear in the Excel worksheet.

How to Set a Default Format for Excel Charts

1. Open Microsoft Excel by opening the 'Start' menu, clicking 'All Programs,' opening the 'Microsoft Office' folder and then clicking on the Microsoft Excel icon.
2. Click the tab labeled 'Insert' at the top of the Microsoft Excel window.
3. Click the 'Create Chart' button on the bottom-right of the 'Charts' section on the Insert tab, below 'Other Charts.'
4. Select the chart type that you would like to use from the preset templates.
5. Click 'Set As Default Chart' to set the selected chart as the default format of all created charts. Click 'OK' to save your changes and to exit the window.

Saturday, June 23, 2012

How to Use Excel for Project Timelines

1. Open the Excel worksheet.
2. Click the 'Insert' tab on the command ribbon.
3. Click the 'SmartArt' button in the 'Illustrations' group. A dialog box with a list of commands and timeline diagrams appears.
4. Click 'Process' in the list.
5. Click the preferred timeline diagram.
6. Click 'OK.' A 'SmartArt Tools' ribbon appears. The timeline template appears on the worksheet.
7. Type the text in the '[Text]' area. A text pane appears to the side. If the text pane does not display, click the left-arrow control on the side of the timeline to open. The text box can display dates, events or other project news for your timeline.
8. Click the 'Design' or 'Format' tabs on the ribbon for more options, such as changing the layout or color. For example, the timeline can appear at an angle when you click the 'Bird's Eye Scene' button in the 'SmartArt Styles' group in the 'Design' tab.
9. Move or re-size the template by pulling on the frame handles.

How to Record a Macro in Excel 2003

1. Open Excel. Either open the program itself or open an existing file.
2. Set your security level. Go to 'Tools > Options.' Go to the Security tab and click on 'Macro Security.' Go to the Security Level tab and change the level to medium or low.
3. Go to 'Tools > Macro.' Choose 'Record New Macro' from the options.
4. Type a name into the macro name box.
5. Type the location of the macro into the box labeled 'Store Macros In.' Click 'OK.'
6. You are ready to begin recording your macro. Perform the series of tasks you would like to create the macro for.
7. When you are finished, click 'Stop Recording' on the toolbar. You may make as many macros as you need.

How to Turn a Picture into a background or watermark

1. Open the MS Word program.
2. Click the 'Page Layout' tab on the command ribbon.
3. Click the 'Custom Watermark' option. A 'Printed Watermark' dialog box opens.
4. Select the 'Picture watermark' option. The 'Select Picture' button becomes accessible.
5. Select the 'Picture watermark' option. The 'Select Picture' button becomes accessible.
6. Click the 'Select Picture' button. The picture library file opens.
7. Select the picture file.
8. Click 'Insert.'
9. Select the scale value. For example, 50 percent.
10. Select the check box for 'Washout' if you prefer a very faint watermark effect.
11. Click 'OK.' The picture file becomes a watermark on the Word document.

How To Freeze All Top Cells In Excel 2003

1. Open your Excel spreadsheet. You can open a spreadsheet by double-clicking the XLS file on your computer, or you can open in the 'Open' dialog box when you click the 'Open' icon at the top of your window.
2. Click the row letter on the left of the spreadsheet to highlight the row you want to freeze. To highlight more than one row, hold the 'Ctrl' key and click each row letter with your mouse.
3. Click the 'Window' menu item. Click 'Freeze Panes' to freeze the rows. Scroll down the spreadsheet. Notice the rows you froze are always displayed at the top of your workspace.

How to Compare Multiple Groups of Cells in Excel

1. Open the Excel 2010 spreadsheet that holds the cell groups you want to compare.
2. Decide which group of cells will be the main group, which all other groups will be compared against.
3. Click the top left cell in the second group of cells. Hold the 'Shift' key and click the bottom right cell from that group. This will highlight the entire group of cells.
4. Click the 'Home' tab at the top of the screen, then click the 'Conditional Formatting' button and choose 'New Rule' from the drop-down menu.
5. Click 'Use a formula to determine which cells to format' at the top of the New Formatting Rule window. Enter the following into the 'Format values...' box:=not(a1=d1)Change 'a1' to the top left cell in your master group of cells and change 'd1' to the top left cell in the current selection. Click the 'Format' button in the lower right corner of the window, choose the 'Fill' tab and select a color that you want to use to highlight the differences in the groups. Click 'OK' when you are done. The differences in the second group will now appear highlighted.
6. Repeat this process for every group of cells that you want to compare against the original group.

Friday, June 22, 2012

How to Turn Anchor Text Into URL in Excel

1. Open Excel 2010. Click on one of the sheet tabs that appear at the bottom of the worksheet. Click into one of the cells and select the 'Insert' tab. Click 'Hyperlink.'
2. Select 'Existing File or Web Page' in the 'Link To' section.
3. Add the anchor text in the 'Text To Display' box. For example, if you are linking to cnn.com, enter 'CNN.' Type the web address in the 'Address' box. Click 'Ok' to save the changes. The hyperlink, or anchor text, appears in the Excel worksheet.

How to Remove Auto

1. Open Excel 2007, then open the document for which you want to remove the automatic publishing feature.
2. Click the round 'Microsoft Office' menu in the upper-left corner of Excel and select 'Save As.' Click the 'Save As Type' drop-down list and select 'Web Page' if the option isn't already selected. Click the 'Publish' button near the bottom of the dialog box to open the 'Publish As Web Page' dialog box.
3. Clear the box beside 'AutoRepublish Every Time This Document Is Saved' under 'Publish As' to disable automatic publishing for the open document. To disable the feature for other documents you previously published, click the 'Choose' drop-down list and select 'Previously Published Items.' Click the document's name under the menu and click the 'Remove' button.
4. Click the 'Close' button at the bottom of the dialog box, then press 'Ctrl' and 'S' to save your current document.

How to Turn off Filter Keys in Excel 2003

1. Click 'Start.' Click 'Control Panel.' Type 'access' into the Search box and press 'Enter.'
2. Click 'Change How Your Keyboard Works' from the results. Click 'Set up Filter Keys' from the Make It Easier to Type section.
3. Uncheck the 'Turn on Filter Keys' and 'Turn on Filter Keys When Right Shift Is Pressed for 8 Seconds' boxes.
4. Click 'Apply' to turn off Filter Keys for all applications, including Excel 2003. Click 'OK' to exit from Control Panel.

How to Make a Bar Graph Using Microsoft Excel 2003

1.
Label the data you want to graph in Microsoft Excel 2003. In Row 1, type the labels for each piece of data you plan to graph. For example, type “Month” in cell A1 and “Units” in cell B1.
2.
Enter the data that you want to graph. Microsoft Excel 2003 needs you to provide the data that will be included in the bar graph. Under the labels you have set up in each row, type the information you want to graph. For example, type the months of the year under the heading in Column A (“Jan” in A2), and the units under the heading in Column B to correspond with the months in Column A.
3. Open the chart wizard window. From the menu bar, select Insert>Chart.
4.
Select the type of bar graph you want to make. Under Chart Type, select 'Bar'. Then, click to select the particular type of bar graph you want to make under Chart sub-type. Click “Next.”
5.
Select the range for the data to be included in the bar graph. Microsoft Excel 2003 defaults to capture the data in the spreadsheet. If Microsoft Excel 2003 does not default to capture the data, then click and drag your mouse over the data to be included in the bar graph. Click “Next.”
6.
Define bar graph options. Microsoft Excel 2003 provides several tabs to define bar graph options, such as to define where to put the legend, what information should be tracked on which axis and what the title of the bar graph should be. Make your selections and then click “Next.”
7.
Define where to save the bar graph. The default is to save the bar graph in the current spreadsheet. Click the radio button for your choice and then click 'Finish'. The bar graph appears where you told Microsoft Excel 2003 to make it.
8. Change the text on the bar graph if needed. If you want to change the title or other text on the bar graph, click on the text you want to change and then type in your changes.

How to Change the Default Email in Excel 2003 Groupwise

1. Close the Microsoft Excel program if it is currently open or running.
2. Open the Start menu and click on 'Control Panel.'
3. Double-click on the 'Internet Options' icon.
4. Go to the 'Programs' tab at the top of the 'Internet Options' pop-up window.
5. Open the drop-down menu labeled 'Email' and choose 'Novell GroupWise' from the list of applications.
6. Hit the 'OK' or 'Apply' button to save the settings and change your default email client.
7. Relaunch Microsoft Excel and click on an email hyperlink to test the new settings.

How to Do a Pie Chart in Microsoft 2003 Programs

Excel 2003
1. Open a Microsoft Excel spreadsheet that contains the data that you want to add to a pie chart.
2. Click 'Insert' from the Excel 2003 menu bar and then select 'Chart.' The Chart Wizard will appear.
3. Select the 'Pie' chart type from the left side of Excel 2003's Chart Wizard.
4. Select the standard 'Pie' sub-type from the right side of the window. Click 'Next.'
5. Select either 'Rows' or 'Columns' for the data range. Microsoft Excel 2003 requires you to organize the data that you want to add to a pie chart in rows or columns.
6. Enter the data range manually into the 'Data range' field or click the button to the right of the field and select the data range with your mouse. Click 'Next.'
7. Click the 'Titles' tab and enter a title for your pie chart.
8. Click the 'Legend' tab and place a check-mark next to 'Show legend' if you want a legend to appear next to the pie chart. Select the placement for the legend, if you choose to show a legend.
9. Click the 'Data Labels' tab and select the labels that you wish to add to your pie chart. You can add category names, values, percent values or series labels to the pie chart.
10. Click 'Finish' and your chart will appear in your document.
Word
11. Open a blank Microsoft Word document.
12. Click 'Insert' in the Microsoft Word 2003 menu bar and scroll down to 'Picture.' Scroll right and click 'Chart.' A generic bar chart will appear in your document along with a 'datasheet,' which is a separate window with cells that contains pre-filled data. Values on the datasheet correspond to the values on the chart in the document.
13. Right-click an empty area inside the bar chart that appeared in your blank Microsoft Word 2003 document in Step 2. Click 'Chart Type.' The Chart Type menu box will appear.
14. Select 'Pie' as the 'Chart type' in the left side of the window. Select the standard 'Pie' sub-type from the right side of the window. Click 'OK.'
15. Click 'View' and select 'Datasheet.' If the datasheet is already visible in your document, skip this step.
16. Input the labels for each of the pie slices in the top row of the datasheet window. Input the values of each of the pie slices into the row below the labels. The pie chart on the Microsoft Word 2003 document will update as you change the data in the datasheet. Close the datasheet once you are happy with the changes.

Thursday, June 21, 2012

How to Protect Excel Cells From Modification

1. Start Microsoft Excel and open the file you want to change.
2. Drag mouse and highlight the cells you want to protect.
3. Open the Format menu and select Cells.
4. Select the Protection tab.
5. Click the Locked option.
6. Select OK to accept the changes.
7. Open the Tools menu and select Protection, then select Protect Sheet.
8. Click OK.

How to Insert Comments in Microsoft Excel 2003

1. Select the cell to which you wish to add comments. Do this by left-clicking on the cell.
2. Access the comments submenu by scrolling to the “Insert” tab on the command bar and selecting “Comment.”
3. Enter the comment. A comment call-out box will appear above the cell. Enter the desired text in that box.
4. Implement the comment by left-clicking on any adjacent cell. The 'commented' box will appear with a red arrow over the left side. When you hover the cursor over it, the comment call-out box will again appear.

How to Disable a Password in Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that contains the worksheet or workbook that is password protected.
2. Click on the 'Review' tab, and then click on the 'Unprotect Worksheet' option from the 'Changes' group.
3. Enter your password if the 'Protect Workbook' dialog box appears.
4. Click on the 'OK' button, and then the 'Unprotect Worksheet' option will read as 'Protect Worksheet.'
5. Click on the 'Microsoft Office' button, and then click on the 'Save' button to save your changes to unprotect the worksheet.

How to Create Organizational Charts With Excel 2007

1. Open Excel to a blank spreadsheet.
2. Click the 'Insert' tab, then click the 'SmartArt' button.
3. Select the 'Hierarchy' group from the left pane. Choose an organizational chart layout you like. Click on each one to see a preview and a description of it in the right pane. Click 'OK' after you have selected the one you want. You should see a chart with text boxes and lines appear.
4. Select a shape in the chart, then click the down arrow next to the 'Add Shape' button in the Design tab to add more boxes to your organizational chart. You can select whether you want to add a shape before, after or next to the box you originally selected. Keep adding shapes to your organizational chart as needed; Excel will automatically add lines showing hierarchies.
5. Click inside each text box to edit the names of each person. If you selected an organizational chart with images, you can click on each image to add an individual's picture.
6. Select different colors or styles for your chart by clicking a different 'SmartArt Styles' design or by clicking 'Change Colors.' Move or resize each text box by clicking and dragging the box or the handles surrounding the box.

How to Calculate a Future Date in Excel

1. Open a new or existing Excel workbook and select a new worksheet.
2. Use row 1 for column labels. Type 'Start Date' into cell A1--column A, row 1. Type 'Plus 30 Days' into cell B1, 'Plus 60 Days' into cell C1 and 'Plus 90 Days' into cell D1. To display these column labels correctly, you need to widen columns A, B, C and D. To widen a column, select its right border and then drag it to the right.
3. Select--highlight--cells A2 through D3. Under the 'Home' tab, select the 'Cells' grouping, click 'Format' and then click 'Format Cells' to display the 'Format Cells' window. Click 'Date,' select 'March 14, 2001' and then click 'OK' to format the cells and close the window.
4. In cell A2, type '1/27/15' to display 'January 27, 2015.' Widen column A if you see only pound signs--#######.
5. Type formula '=A2 30' into cell B2 and then press 'Enter' to display 'February 26, 2015,' which is the date in cell A2 plus 30 days. Copy cell B2 and paste it into cells C2 and D2 to display 'March 28, 2015' and 'April 27, 2015.'
6. Type formula '=A2 7' into cell A3 and then press 'Enter' to display 'February 3, 2015.' Copy cells B2 through D2 and paste them into cells B3 through D3 to display 'March 5, 2015,' 'April 4, 2015' and 'May 4, 2015.'
7. Copy row 3 and paste it into row 4 to display 'February 10, 2015,' 'March 12, 2015,' 'April 11, 2015' and 'May 11, 2015.'

How to Use Conditional Statements in Excel

The Three Parameters of IF() Statements
1. The first parameter in an IF() statement is an equation or logical operator. For example, A1>A2.
2. The second parameter of an IF() statement is separated from the first by a comma, and is what is displayed if the IF statement is true. For example, 'Passing Grade'.
3. The third parameter of an IF() statement is separated from the second by a comma, and is what is displayed if the IF statement is false. For example, 'Failing Grade.'
4. Finally, here's a completed IF() statement: =IF(A1>A2,'Passing Grade','Failing Grade'). This statement compares the value of a number in cell A1 to a value in cell A2, and if it's larger, displays 'Passing Grade', and if equal to or less than, displays 'Failing Grade'.

Tuesday, June 19, 2012

How to Download Microsoft Excel 2003

Buy an Older Version
1. Make sure that your computer is capable of running Excel 2003. The requirements are Windows XP or later, 128 MB of RAM and 400 MB of hard-disk space.
2. Log on to an online software store such as Brothersoft.com, Amazon.com or Excellent-software.net.
3. Type 'Excel 2003' in the search bar on the website.
4. Scroll through the results and select the program you wish to purchase.
5. Select the method of payment you wish to use. Most websites accept PayPal or credit card payments. Enter your payment details and then confirm the transaction.
6. Follow the on-screen instructions on how to begin your download.
Use a Free Trial
7. Log on to Brothersoft.com and select the download link.
8. Select the location for the file and then select 'OK.'
9. The file will download to the location you specified. Open the file by double-clicking it, to run the installation sequence.

How to Prevent a Microsoft Excel 2007 File from Opening

1. Open your spreadsheet in Microsoft Excel 2007.
2. Click the top-left 'Office' button, and select 'Save As.'
3. Click the 'Tools' drop-down menu, located to the left of the 'Save' button, and select 'General Options.'
4. Enter a password in the 'Password to open' field, and click 'OK.'
5. Re-type the password in the confirmation window, and click 'OK.'
6. Click 'Save' to secure your spreadsheet.
7. Click 'Yes' in the 'Confirm Save As' dialog windows, which asks if you want to replace the current file. This overwrites the unprotected file with the password-protected version.

How to Copy and Paste for Conditional Formatting in Excel

Copy Conditional Formatting
1. Select the Excel cell that contains the conditional formatting rules you want to copy. Hold the 'Ctrl' button and press the letter 'C' to copy the cell to the clip board.
2. Right click on the cell you want to copy the conditional formatting to. If you are copying it to a range of cells, left-click on the first cell and hold the mouse button down. Then move the mouse to the last cell and release the button. Finally, right-click on the selected cells.
3. Move your mouse over 'Paste Special' in the pop-up menu to reveal additional pasting options. Select the icon that has a percentage symbol and a paint brush. The icon is located under the 'Other Paste Options' area of the menu and reads 'Formatting (R)' when you move your mouse over it. Once you select this icon, the conditional formatting will now affect the new cells.
Combine Multiple Groups of Conditional Formatting
4. Select the top-left cell in the first group and hold the mouse button down. Move the mouse to the bottom-right cell in the second and release the mouse button, selecting the entire area. If the cells you need to select are not right next to each other, you can hold down the 'Ctrl' button to select additional cells and areas.
5. Select the 'Home' tab at the top of the screen and then press the 'Conditional Formatting' button in the 'Styles' area. Choose 'Manage Rules' from the menu that appears. You will see a screen with several of the same conditional formatting rules listed.
6. Select one of the duplicate rules and press the 'Delete Rule' button. Continue until you only have one conditional formatting rule remaining. Select the small box next to the 'Applies to' field, which will move your cursor back to the Excel worksheet.
7. Select the exact same area of the worksheet as you did before you brought up the conditional formatting rules manager. Press the small box again once you have completed your selection. Click 'Apply' at the bottom of the rules manager followed by 'OK.' All the selected cells are now a part of the same conditional formatting group.

How to Show Grid Lines When Printing in Microsoft Excel

Printing
1. Open the worksheet or worksheets in Excel that you wish to print. You can do this by clicking on theworksheet tab at the bottom of the screen. To select more than one worksheet, after you select the first worksheet, press Shift and then select the second or multiple worksheets.
2. Go to the 'Page Layout' tab on your menu bar for Microsoft Excel 97-2003 or interface for Microsoft Excel 2007.
3. Select the 'Sheet Options' group and click on the 'Print' check box under 'Gridlines.'
4. Preview the grid lines to view how they will print by pressing the 'Ctrl F2' keys to open the Print Preview window. Remember that grid lines are only designed to print around inputted data in a worksheet. To print grid lines around empty cells you must change the print area.
5. Select the empty cells on the worksheet that you want to include with grid lines as well. Go to the 'Page Layout' tab. In the 'Page Setup' group, select 'Print Area' and then click 'Add to Print Area.'
6. Go to the Microsoft Excel button for the 2007 version and click 'Print.' For Excel 97-2003, go to the menu and select 'Print.'
Troubleshooting
7. Go to 'Page Setup' and 'Sheet' tab. Make sure that the 'Draft' quality box is not checked.
8. Download the latest driver for your printer from the manufacturer's website if the grid lines still are not printing. Sometimes a problem with the printer driver interferes with printing instructions.
9. Change and apply borders to the cells if you are having trouble with grid lines.

How to Create a Form in Excel 2007

1. Open the Microsoft Excel 2007 application. Click the “Microsoft Office” button and select “Open.”
2. Locate the Excel 2007 file stored on your computer for which you want to create a form. Select the file and click on the “Open” button.
3. Add any column headers to the columns within the spreadsheet. Click on any cell in the data table where you want to add the form.
4. Click on the arrow next to the Quick Access Toolbar and click on the “More Commands” option. Click on the “All Commands” option and select the “Form” button from the list.
5. Click on the “Form” button from the Quick Access Toolbar. The data form will appear as a dialog box within the spreadsheet.
6. Click on the 'New' button within the data form dialog box if you want to enter data for a new row. Enter the data into the new field that appears in the dialog box and the press 'Enter.'
7. Click on the 'Find Next' or 'Find Prev' buttons to navigate to each row in the data form dialog box. Click on the 'Criteria' button to enter any data you want to search for within the form.
8. Change data in a row in the data form dialog box by clicking on any row and entering the new data. Press 'Enter' and the row will be updated on the form and in your spreadsheet.

Monday, June 18, 2012

How to Find Out How Often a Number in a Set Appears in Excel

1. Highlight your data set. For example, if your set of numbers is in cells A1 to A50, highlight cell A1 with your mouse.
2. Choose 'Data' from the ribbon. Click on 'Advanced' to bring up a pop-up box.
3. Check the 'Copy to another location' radio button, and check the 'Unique records only' box. Enter the range of your data set. In this example, cells A1 to A50 would be entered as $A$1:$A$50. In the 'copy to' box, enter where you would like the data to be output. For example, enter $B$1:$B$50 (which will return the result in cells B1 to B50). Press 'OK.' This returns a list of numbers in your data set.
4. Enter the following formula into cell C1: =COUNTIF(A$1:A$50,B1). This counts the number of times the item in cell B1 appears in the data set from cell A1 to cell A50, and returns the result in cell C1.
5. Copy the formula in the cell from Step 4 (in this example, cell C1) for all cells in column B. For example, if you have 10 cells filled in column B, select cell C1 by clicking with the mouse, and drag the fill tool (the little black square in the bottom right corner) down to cell C10.

How to Use Hyperlinks in Dropdown Menus in Excel

Adding Links to Drop-down Menus
1. Access editing capabilities by double-clicking the cell in the menu that contains the URL that you would like to hyperlink.
2. Activate the URL by pressing 'Enter.' This will convert the text URL to an active hyperlink.
3. Click that now-active hyperlink one more time to complete the process.
4. Repeat Steps 1 through 3 for each menu item.
Alternate Method
5. Choose a menu cell, for example, cell B6.
6. Insert the following function into cell C6 (the cell below the chosen menu cell):=HYPERLINK(B6, 'Goto Link')
7. Change 'GoTo Link' to your choice of text.

How to Use Excel to Make a Percentage Bar Graph

1. Open Excel to a blank spreadsheet.
2. Enter your numbers in cells in table form and add labels for each column and row. For example, if you want to compare men to women and the percent who smoke vs. the percent who do not smoke, you would create a 2x2 table. You would label one column 'Men' and one column 'Women.' To the left, you would label the first row 'Smoke' and the second row 'Don't smoke.' When entering numbers in each cell, add the percent sign to let Excel know that these are percentages. For example, you should enter '32%' instead of '32.'
3. Select the table you created by clicking and dragging to highlight the cells.
4. Click the 'Insert' tab on the ribbon.
5. Click 'Bar' in the Charts group. Select a bar style that suits your needs. Excel will automatically create a bar graph with percentages for either your X or Y axis.
6. Click inside the chart to customize colors, sizes, fonts and background. Click the 'Design,' 'Layout' or 'Format' tabs in the Chart Tools section of the ribbon to select custom features.

How to Add a Watermark to an Excel Spreadsheet

Insert Your Watermark
1. Place your cursor where you would like the watermark to appear. Choose to put this in a header or footer or in the middle of a page.
2. Choose 'Picture' under the 'Insert' menu.
3. Browse for and select your image. Hit 'OK.' Your image should now be on the spreadsheet.
Adjust the Coloring to Make the Image Look Like a Watermark
4. Click on the photo. This will make the picture toolbar appear.
5. Click 'Format Picture' on the toolbar.
6. Under 'Image Control/Color,' select 'Watermark.' This will lighten the colors in your image to look like a watermark.

How Can I Make a Dynamic Drop

1. Launch Excel 2003 and click the Tools menu. Select Macro and then Visual Basic Editor. Insert a new module by clicking the Insert menu and selecting Module. Type the following to create a new procedure:Private Sub createDropDownList()On Error GoTo Err_createDropDownList:
2. Type the following to dynamically create a new ComboBox control in the active Excel worksheet:With ActiveSheet.OLEObjects.Add(ClassType:='Forms.ComboBox.1', Link:=False, _DisplayAsIcon:=False, Left:=70, Top:=60, _Width:=100, Height:=25)With .Object.AddItem 'Item List 1'.AddItem 'Item List 2'.AddItem 'Item List 3'End WithEnd WithThis code will also add three items to the ComboBox control.
3. Type the following to exit the procedure and handle errors:Exit_createDropDownList:Exit SubErr_createDropDownList:MsgBox Err.DescriptionResume Exit_createDropDownList:End Sub
4. Press 'F5' to run your procedure. You will see a new ComboBox control created in your worksheet.

How to Copy Paste a Range to a VBA Excel Workbook

1. Create two different workbooks in Excel. The first workbook will contain the range that you want to copy while the other one is where you will paste the range into. Keep the workbook that currently has the range open. The other workbook doesn't have to be open to complete the rest of the steps.
2. Open the Visual Basic Editor. Click on 'Tools > Macros > Visual Basic Editor' in Excel 2003 or earlier. In Excel 2007, you will need to show the 'Developer' tab first. Click on the 'Office' button and then 'Excel Option.' Click the checkbox next to 'Show Developer tab on the ribbon' under the 'General' tab and then click 'OK.' Click on the 'Developer' tab and then click 'Visual Basic' to open the editor.
3. Click on 'This Workbook' in the left-hand pane to open it in the code window. Add the following code to the code window:Public Sub CopyRange()Workbooks('Book1')._
Worksheet('CurrentSheet') _
Range ('A1:C10').Copy_
Destination:=
Workbooks('Book2')._
Worksheets('PasteSheet')._
Range('A1')
End SubChange the information in the parenthesis to match your workbooks. 'Book 1' is the workbook that you are currently working in. 'CurrentSheet' is the worksheet that has the range you want to copy. 'A1:C10' is the range of cells that you want to copy. 'Book2' is the workbook that you want to paste the range. 'PasteSheet' is the worksheet that you want to paste the range into. 'A1' is the beginning cell that you want to paste the range into.
4. Save and run the code. Click on 'Tools > Macro > Macros' in Excel 97 to 2003. In Excel 2007, click on the 'Macros' button on the 'Developer' tab. See the 'CopyRange' function in the list of macros. Click on it and then click 'Run.' The code will run and copy the range from one worksheet and paste it into another.

How to Make a Chart in Excel to Show 2009 2010 Data Side

1. Open the Excel 2010 spreadsheet that holds your 2009 and 2010 data.
2. Click and hold the mouse on 'A1' above the first column and then drag the mouse to column 'C' and release the button. Right-click 'A' and choose 'Insert,' creating three new columns at the front of the spreadsheet.
3. Click cell 'B1' and type '2009.' Then click cell 'C1' and type '2010.' Click cell 'A2' and type in the first period for which you have data. This can be a day, week, month, quarter or any time period, less than a year, that you need. Continue down the column until you have added all your time periods.
4. Click cell 'B2' and enter the data that corresponds to your time period for 2009. Continue down the column, adding your data as you go. Repeat this process in column 'C,' but use the data from 2010.
5. Click any cell in your data table. Click the 'Insert' tab at the top of the screen, then click the 'Column' or 'Bar' buttons in the 'Chart' area, depending on whether you want your data displayed respectively, vertically or horizontally. Choose the 'Clustered Column' or 'Clustered Bar' chart buttons from the small menu that appears. Your chart, which will contain the 2009 and 2010 numbers side-by-side, will appear on the spreadsheet.

Sunday, June 17, 2012

How to Unprotect a Password Protected Word Document

1. Open the Word document with the password that was previously set. If you don't have the password, you will not be able to open the document or remove the password.
2. Click on 'File' then 'Info.' Under the 'Permissions' area click on 'Encrypt with Password' if you are using Microsoft Word 2010. If using Word 2007, click on the circular 'Office' button, 'Prepare' then the 'Encrypt Document' button. In Word 2003 select 'Tools,' 'Options' then 'Security.'
3. Highlight all characters in the password field and delete them. This removes the password from being prompted at start up. Click on the 'OK' button to close window.
4. Save the document. Check to see that the password has been successfully removed. If not, go back to the security settings and try it again, since your changes may not have been saved.

How to Create a Map From an Excel Spreadsheet

1. Insert your data into an Excel worksheet. Include a column heading. For example, put a list of city names in column A (with the heading 'City names' in cell A1), and put a list of ZIP codes in column B (with the heading 'ZIP codes' in cell B1).
2. Click 'File' and then click 'Save.'
3. Click on the top left cell of your data. In the above example, that would be cell A1. Drag the cursor down over your data, to the bottom right corner.
4. Click 'Insert' and then click 'Map.'
5. Click on the area of the worksheet where you want your map to be. Hold the left mouse button and drag the cursor from the top left to the bottom right until the map is the size you want.
6. Let go of the cursor. Excel will display a pop-up menu.
7. Select the type of map you want by clicking on it. Click on 'OK.' Excel will insert the Excel map into your worksheet.

Saturday, June 16, 2012

How to Repeat an Action

1. Start Excel by clicking the Windows 'Start' button and typing 'excel' into the search bar. Click the 'Microsoft Excel 2010' result that appears.
2. Click the green 'File' tab, click the 'Help' heading, and then click 'Options.'
3. Click the 'Quick Access Toolbar' entry. Click the 'Choose commands from' drop-down box and then select 'Popular Commands.'
4. Select 'Repeat' from the list of commands, and then click the 'Add' button. Click 'OK' to close the dialog box.
5. Point the mouse cursor to the top of the Excel interface and then click the blue 'repeat' button that has appeared there.

How to Use OLAP in Excel

1. Launch Microsoft Excel. Click the “Data” tab on the main menu ribbon. Click “From Other Sources” in the “Get External Data” group. Click the “From Analysis Services” option in the drop-down list.
2. Type the name of the remote OLAP server into the “Server Name” input field in the Data Connection Wizard. Click the check box next to “Use the following User Name and Password.” Enter your database username and password into the applicable boxes. Alternatively, click “Use Windows Authentication” if the remote server is set up to work with your Windows username and password. If in doubt, check login details with the server administrator. Click “Select Database and Table” followed by “Next.”
3. Click on the OLAP database that contains the data you want to import to Excel. To access a specific table or data cube within the database, click and select the “Connect to a Specific Cube or Table” check box. Select your preferred table or cube from the list of available items. Click “Save Data Connection File and Finish.” Click the “Next” button.
4. Type your preferred file name for the imported data into the “File Name” box, or skip this step to retain the default file name. Click “Browse” to set the download location, or use the default location of “My Data Sources.” Type a description of the data, a data name and relevant keywords into the applicable input fields. Click the check box beside “Always attempt to use this file to refresh this data” to enable this option.
5. Click the “Finish” button. Select your preferred option from the list under “Select how you want to view this data in your workbook” in the “Import Data” dialog box. You can choose to create a PivotTable Report or a PivotChart and PivotTable Report. Select “Only Create Connection” if you just want to save the OLAP data connection without importing data.
6. Select “Existing Worksheet” or “New Worksheet” in the “Where do you want to put the data” section. Type the worksheet cell reference of the first cell in the output table range into the input box; for example, type “A1” or 'A22.'
7. Click the “OK” button to import the data from OLAP to Excel.

Friday, June 15, 2012

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

1. Open Excel 2007 and select a workbook. Select the 'Office' button and click 'Open.' Browse your network and click the file. Select the 'Open' button. The workbook opens.
2. Highlight the data you want included in your pivot table. Select the 'Insert' tab and click 'Pivot Table.' Select 'Pivot Table' again. The Create Pivot Table dialog appears. Click 'OK.' A blank pivot table appears.
3. Add fields to the pivot table by checking field names from the right 'Pivot Table Field List.' Add fields that you want calculated or summarized into the pivot table by checking them. Manually drag the field from the field list to the 'Sum Values' section of the right 'Pivot Table Field List.'
4. Drag one of your fields into the 'Report Filter' section of your right 'Pivot Table Field List.' A filter appears above your data. Select the 'Pivot Table Tools' tab. Click the 'Option' tab. Select the 'Options' button in the Pivot Table group. Select 'Show Report Filter Pages.' Click 'OK.' The report filter pages are inserted as a worksheet in your workbook.

How to Convert XLS to PRN Files

1. Use Microsoft Excel to open the XLS file you want to convert.
2. Select Print from the File menu. Do not use the Print button on the toolbar because the toolbar button will bypass the print dialog window.
3. In the Print dialog window check the Print to file checkbox. Normally this checkbox is off, in which case Excel would print to the printer. Click the OK button.
4. In the Print to File dialog window type the Output file name. This will be the name of your file on the disk. Excel does not automatically add the '.prn' to the file name so you must type that in yourself; it will still be a PRN file even if you don't give it the '.prn' extension. Click the OK button. You now see a dialog window that tells you that you are printing; when that window disappears the conversion is complete.

Thursday, June 14, 2012

How to Add ActiveX Control in Excel 2007

1. Open the Microsoft Excel 2007 application from your computer and then click on the “Microsoft Office” button.
2. Click on the “Excel Options” button and then click on the box next to the “Show Developer tab in the Ribbon” field. Click on the “OK” button.
3. Click on the “Developer” tab and then click on the “Insert” option from the “Controls” group. Click on the “More Controls” option below the “ActiveX Controls” group.
4. Click on the ActiveX Control that you want to add from the list of controls in the More Controls dialog box.
5. Click the location on your worksheet where you want the ActiveX control to appear. The control will then appear in your worksheet.

How to Remove Duplicate Lines in Excel

Excel 2003
1. Open the Excel file that has the duplicate data that you need to remove.
2. Highlight the data that you want to work with, then right-click and select 'Copy.' Click on an empty cell on your worksheet, right-click and choose 'Paste.' This step creates a backup copy of all the data.
3. Highlight the original data again.
4. Click the 'Data' menu and then click 'Filter.' Scroll over and click 'Advanced Filter.'
5. Place the radio button next to 'Filter the list, in place' in the 'Advanced Filter' window. Put a check mark next to 'Unique records only.'
6. Click the 'OK' button.
7. Delete the backup copy of your data once you are satisfied with the filtered list.
Excel 2007
8. Open the Excel file with the duplicate data.
9. Highlight the data you want to work with, then right-click and select 'Copy.' Move to an empty cell, right-click and choose 'Paste.' This step creates a backup copy of all your original data.
10. Highlight the original data again.
11. Click to select the 'Data' tab. Click the 'Remove Duplicates' button.
12. Choose the rows from which you want duplicates removed. Click the 'OK' button, then 'OK' again in the information box that appears indicating how many duplicates Excel found.
13. Delete the backup copy of your data once you are satisfied with the filtered list.

How to Resize Cells in Excel 2007

1. Click on the cell(s) from the columns you want resized.
2. Click on the 'Home' ribbon, and then click 'Format' in the 'Cells' group.
3. Click the 'Width' option in the 'Cell Size' graph that appears.
4. Type the length you would like the cells to be in the box that appears.
5. Click 'OK' to resize the cells.

How to Password Protect Excel Worksheets

1. Go to 'Tools' on the main menu.
2. Select 'Options.'
3. Select the 'Security' tab.
4. Under 'File encryption options for this document,' enter the password in the field called 'Password to open' and click 'OK.'
5. In the Confirm Password window, enter the password in the field called 'Reenter password to open' and click 'OK.' In the Confirm Password window, Excel gives two cautionary notes: first, that a lost or forgotten password cannot be recovered; second, that passwords are case-sensitive. This means that a password such as 'abcd' is not the same as 'Abcd.'
6. Save the password by saving the document. To do this, click the 'Save' icon.
7. If you want to set a password so that only authorized personnel can modify the worksheet, go to 'Tools' on the main menu, select 'Options,' select the 'Security' tab, and under 'File sharing options for this document,' enter the password in the 'Password to modify' field and click 'OK.'
8. Notify authorized personnel of the password required to open and/or modify the worksheet.

How to Make Excel 2007 Your Default Program

1. Hold the Windows orb key, and press 'R' to open the 'Run' dialog window.
2. Type the exact phrase in the Run dialog window, including quotation marks:'C:\Program Files\Microsoft Office\Office12\excel.exe' /regserverIf you installed Excel 2007 in a different folder, adjust the path accordingly. If you are using a 64-bit version of Windows 7, change 'Program Files' to 'Program Files (x86)'.
3. Click 'OK' to have Excel register itself as the default program.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

Subscribe to: Posts (Atom)