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...
Browse » Home » Archives for June 2012
Thursday, June 28, 2012
How to Merge Cells With Different Formulas in Excel
Concatenate Function1. 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...
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...
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...
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...
Monday, June 25, 2012
How to Improve the Print Resolution of Excel Charts
File Prep1. 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 Method4. 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...
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....
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'...
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 workshe...
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 wind...
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...
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...
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....
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 workspa...
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...
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 workshe...
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...
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 Pan...
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...
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 settin...
How to Do a Pie Chart in Microsoft 2003 Programs
Excel 20031. 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...
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 ...
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 appe...
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 workshe...
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,...
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...
How to Use Conditional Statements in Excel
The Three Parameters of IF() Statements1. 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...
Tuesday, June 19, 2012
How to Download Microsoft Excel 2003
Buy an Older Version1. 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...
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 versi...
How to Copy and Paste for Conditional Formatting in Excel
Copy Conditional Formatting1. 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...
How to Show Grid Lines When Printing in Microsoft Excel
Printing1. 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...
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...
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...
How to Use Hyperlinks in Dropdown Menus in Excel
Adding Links to Drop-down Menus1. 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 Method5. 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 te...
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...
How to Add a Watermark to an Excel Spreadsheet
Insert Your Watermark1. 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 Watermark4. 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 waterma...
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...
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...
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....
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...
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...
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 the...
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...
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...
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...
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...
How to Remove Duplicate Lines in Excel
Excel 20031. 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....
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 cel...
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...
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 progr...
How to Recover an Excel Work File That Wasn't Saved
File Never Saved1. 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 Saved7. Open...
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...
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...
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 minimiz...
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 bord...
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'...
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...
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...
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 chang...
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...
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....
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...
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...
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 Time6. 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...
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...
Subscribe to:
Posts (Atom)