Excel

Browse » Home » All posts

Wednesday, July 18, 2012

How to Print Grid Lines in MS Word

Show Gridlines
1. Open the Word document that contains your table or labels.
2. Select 'Table' and 'Show Gridlines' from the toolbar to make gridlines visible. You will see the grid lines on your page.
3. Select 'File' and 'Print Preview' from the toolbar. The grid lines will not be visible.
4. Press the 'Escape' key to return to your page.
Add and Print Borders
5. Select the table or labels containing the grid lines you want to print.
6. Click on 'View,' 'Toolbars' and 'Tables and Borders' in the toolbar to open the 'Tables and Borders' toolbar.
7. Expand the 'Border Color' button by clicking on its arrow. Then select 'Gray-25%' (a color similar to that used in grid lines) for the border color.
8. Expand the 'Border' button by clicking on its arrow. Then select 'All Borders' from the list. A border will be applied to your entire table.
9. Select 'File' and 'Print Preview' from the toolbar. The borders you just applied will now be visible. Press the 'Print' icon on the Print Preview page to print your table or labels with the borders.

Tuesday, July 17, 2012

How to Use Paste Special in Microsoft Excel 2007

1.
Select the data you would like to work with and choose 'Copy.' Copying can be performed by right clicking your mouse on the area or clicking the copy icon in the Home Tab of the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.
2.
Click the 'Paste' drop-down menu located in the Home tab on the Ribbon. Select 'Paste Special.'
3. Click on the cell or range of cells you would like your data to affect and click the
'Paste' drop-down menu again. Choose 'Paste Special.'
4.
Choose the function you would like to perform from the 'Paste Special' dialog box and click 'OK.' Your function will be performed as directed. There are numerous options available. For example, you can transpose a list of data to appear in reverse order. You can paste the copied value into another sheet or workbook and link them so that it automatically updates each time you edit the original cell.

How to Change Positive Data to Negative on Microsoft Excel

1. Click 'Start,' 'All Programs,' 'Microsoft Office' and then 'Microsoft Excel 2010.' Click 'File' and 'Open.' Locate your file and click 'Open.'
2. Double-click the cell containing the number you wish to change from positive to negative. Enter the negative symbol before the first number and press 'Enter.'
3. Select 'Number' from the drop-down list in the Number group of the Home tab of the ribbon bar. Click the expander arrow in the lower right of the Number group or 'Alt,' 'H,' 'F' then 'M' to show all the formatting options.
4. Select one of four options for displaying negative numbers on the Number tab. Click 'OK.'
5. Click 'Conditional Formatting' > 'Highlight Cell Rules' > 'Less Than' to set conditional formatting. Enter '0' (zero) in the first box and select an option from the second. Click 'OK.'

How to Add Numbers in Microsoft Excel if they Meet Certain Criteria

1. Select 'More Functions' from the 'Sum' drop-down menu in the 'Editing' section on the 'Home' tab. This opens the 'Insert Function' window.
2. Type 'sumif' in the 'Search For A Function' field and click the 'Go' button. Verify that 'SUMIF' is highlighted on the 'Select a Function' pane and click the 'OK' button. This opens the 'Function Arguments' window.
3. Click in the 'Range' field and enter the range of the data that needs to be tested; for example A1:A25 will test each cell starting with A1 and ending with A25 to see if it matches the criteria. The ':' represents 'through.'
4. Click in the 'Criteria' field and either type the criteria or enter a cell value. The cell value should contain the criteria.
5. Click in the 'Sum_Range' field and enter the range of cells that need to be added; for example, B1:B25 will sum any values that match the criteria. The results are displayed in the lower-left corner of the window next to 'Formula result =.' Click the 'OK' button.

Monday, July 16, 2012

Animate Objects in Microsoft Excel

Download and Install Excel 3-D Animation Engine
1. Open a browser and go to Microsoft's Download Center.
2. Locate the 3-D Animation Engine under Office products add-ons.
3. Click 'Download' and 'Run' to begin the program download.
4. Click 'Yes' to download and run the program on your computer.
5. Click 'Unzip' to open the file. Save the file to your desktop for easy access.
6. Click 'OK' to complete the program's installation.
Open and Configure Excel 3-D Animation Engine
7. Double-click on the 3-D Animation Engine file to open it. The program will automatically open in Excel 2007.
8. Click on the Macro warning box to enable Macros in this program. Click 'OK.'
9. Delete the animation example that is in the work space by clicking on the shape and hitting the backspace key. The program configuration is complete.
Animate an Excel Object
10. Click the Insert tab on the tool ribbon on the top of the spreadsheet.
11. Click 'Shapes' and choose a shape or object to animate. Pick an area on the work screen to place the animated object and click on that area. The shape will appear on the screen.
12. Left-click on the shape and choose 'Format Shape' to adjust the size, shape, color and angle of the shape. Close the 'Format Shape' box when finished.
13. Change the size of the shape by clicking on one of the handles on the box surrounding the shape and dragging the handle to increase or decrease the shape's size.
14. Click the 'Animate!' button to animate the object when you have finished formatting.
15. Click 'Save' to save the image. Type in a name for the image in the dialog box and click 'OK.' The image can accessed through Excel to be used in a spreadsheet or exported to a PowerPoint presentation.

How to Create Footers in Microsoft Excel 2003

1. Open the view menu. Scroll to the “View” tab on the command bar.
2. Access the page header/footer menu. Select “Header And Footer” from the “View” menu. A header /ooter properties box will open.
3. Select the footer. To add text to the footer, click on the “Custom Footer” button.
4. Create text for the footer. Enter the desired text for all sections: left, right and center.
5. Insert text into the footer. Click on the “Okay” button when you have finished entering the desired text. This will bring you back to the header/footer properties box.
6. Insert the footer into the spreadsheet. Click on the “Okay” button to add the footer into the spreadsheet.

How to Unhide Columns in Microsoft Project

Unhide Columns in Microsoft Project
1.
'Click' the column header to the right of the position in which the new column should appear, as in Image 1. This action will select and darken the entire column.
2.
'Click' the 'Insert' option from the menu bar, as displayed in Image 2. This will cause a drop-down menu to appear.
3.
Click 'Column' from the drop-down menu. This will bring up a dialogue box entitled 'Column Definition,' as in Image 3. This feature allows you to select the column to be displayed.
4.
Click the down arrow to the right of the 'Field Name' text box. This will cause a list of fields to display, as in Image 4. Select the name of the column you wish to unhide from the list.
5.
Click the 'Best Fit' button at the bottom of the Column Definition dialogue box to display the hidden column as in Image 5. You may also add a column by clicking 'OK,' but that will require manual column-width adjustment.

How to Crop Images in Excel

1. Start Microsoft Excel 2007 and open a workbook from your files that contains an image that you want to crop.
2. Select the image you want to crop by clicking on it. The image will be surrounded by a thin, black border and light blue sizing handles once it is selected.
3. Choose the 'Format' tab at the top of the Excel 2007 screen to display the Format ribbon. Locate the 'Size' section of the Format ribbon. This section is on the right side of the Format ribbon.
4. Click the 'Crop' button and thick, black lines should appear on the corners and sides of the selected image.
5. Hover your mouse over one of the black borders on a corner or a side and your cursor will change to two borders overlapping each other.
6. Crop the image by clicking your mouse on the border and dragging inwards while holding the mouse button down. The part of the image you are moving over will disappear.
7. Release your mouse button and view the effects. Continue to use this same process to crop other parts of the image.

How to Remove Fractions in Excel

1. Launch Microsoft Excel 2010 and open the spreadsheet that contains or will contain the numbers that need to be displayed without fractions.
2. Click a cell or highlight (Select) a range of cells by clicking on one cell and holding the mouse button down while dragging the cursor across a range of cells. Release the mouse button to select the highlighted range. You can also click directly on a column letter or row number to select the entire column or row. Add or remove from the current selection by holding down the 'CTRL' key while selecting the cells you do not want changed.
3. Right-click any of the selected cells and click 'Format Cells' from the context menu.
4. Click the 'Number' tab in the 'Format Cells' dialog box.
5. Click the desired format for the display and storage of the numbers in the selected cells. Make preference adjustments on the right side of the dialog box using the instructions at the bottom of the dialog box. The 'Sample' box will display a preview of the format for any data in the selected cell.

How to Change Interval of Category Axis in an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you want to change the interval of the category axis.
2. Click in the white area of the chart so the whole chart is selected. You can tell the entire chart is selected because a light blue outline will surround the entire chart.
3. Select the 'Format' tab at the top of the Excel screen to display the 'Format' ribbon. Locate the 'Current Selection' group-it is the first group in the 'Format' ribbon.
4. Click the 'Chart Elements' arrow to display the drop-down list. This is the top option in the 'Current Selection' section of the 'Format' ribbon. Choose '(Category) Axis' from the drop-down list.
5. Press the 'Format Selection' button right underneath the 'Chart Elements' drop-down list. The 'Format Axis' dialog box will appear on the screen. Click 'Axis Options' if necessary to display the options for changing the interval of the category axis.
6. Type the number of units you have to use in the 'Interval between tick marks' text box at the top of the 'Format Axis' dialog box.
7. Click the 'Close' button to close the 'Format Axis' dialog box and return to your Excel chart. You will now see that the interval for the category axis has changed.

How to Create a Bar Chart in Microsoft Excel 2003

1. Make a table that contains all of the data you want represented in your bar chart. Be sure to include column and row labels and numerical quantities when making your table.
2. Highlight all of the data in your table--including the column and row labels--and click the “Chart Wizard” button on the toolbar. The “Chart Wizard” button looks like a tiny column chart.
3. When the Chart Wizard appears, select “Bar” from the “Chart Type” menu, then select the subcategory of bar chart you would like to create. Click the “Next” button.
4. Use the “Data Range” box if you need to modify the data set. Select “Rows” from the “Series In” option to sort your data by row or “Columns” to sort your data by column. Click “Next.”
5. Enter titles and labels for your chart to make it easy to read. Excel will offer you a preview of how the chart will appear while you work.
6. Use the “Axes,” “Gridlines,” “Legend,” “Data Labels,” and “Data Table” tabs as additional tools for modifying the appearance of your bar chart. Each tab modifies a specific part of your bar chart. Click “Next” when you are finished modifying the appearance of your chart.
7. Select “As New Sheet” to place your bar chart in a new Excel spreadsheet or select “As Object In” to place the bar chart in the same spreadsheet that you built your data table. Click “Finish” to complete the creation of your chart and place it on a spreadsheet.

How to Synchronize Spreadsheets in Excel

Linking Separate Excel Workbooks Together
1. Open the workbook with the data you expect to be shared in Excel. This is the source workbook.
2. Open the workbook you want the data to be replicated in. This is the destination workbook.
3. Select the cell in the source workbook you want the data to come from, and enter 'Ctrl C' to copy it.
4. Switch to the target workbook, and select the cell you want the data to appear in.
5. Enter 'Ctrl V' to paste the reference in. If you examine the formula, you'll see that prior to the row and column entry, there will be a reference to the drive location (or network location) and the file name of the source workbook. An example would look like this: [C:\Book1.xlsx]Sheet1!A1. This means that Excel will pull the value from the linked file.
Synchronizing Worksheets In The Same Workbook
6. Open your workbook in Excel.
7. Hold down the 'Ctrl' key while clicking on multiple worksheet tabs. Be sure to leave one tab unselected when you do this. This will ensure that any edits you make on one tab will be replicated across all selected tabs, with data entered in the corresponding cells.
8. Click the tab that's not part of the group to undo the tab group selection. This will get Excel to revert to 'normal mode,' where changes in one tab only affect that tab.

How to Add an Analysis Toolpack in Excel 2003 GPO

1. Launch Excel 2003. To install the Analysis Toolpak on a version of Excel that was installed through a GPO (Group Policy Organization), you need a privileged account, or you must talk to your network administrator to unlock the procedure.
2. Click on the 'Tools' menu item. Navigate through the drop-down menu and select 'Add-Ins'. A dialog box will appear; this will show all the add-ons currently available.
3. Check the check box that says 'Analysis Toolpak.' You may need to click on the 'Browse' button to load it; it will be listed as Analysis.xla in the Add Ons folder for Excel. If you do not see it, you may need to reinstall Excel 2003 from your installation media. If prompted 'Do you want to install the Analysis Toolpak?', click 'Yes' to install it.
4. Click the 'Tools' menu item; there will now be a 'Data Analysis' entry added to the drop-down menu.

How to Insert a Dynamic Date in Excel

1. Start Microsoft Excel 2007, and open a spreadsheet from your files into which you want to insert a dynamic date. Or, you can start a new, blank spreadsheet.
2. Select the cell you want to insert the dynamic date into by clicking on it. The cell will be surrounded by a thick, black outline once it is selected.
3. Type the formula '=today()' (without the quotation marks) into the selected cell. This formula will instruct Excel to insert the current date into the cell where the formula is entered.
4. Press the 'Enter' key on your keyboard or the green check mark on the formula bar to input the formula into Excel. Excel will then accept the formula and display the current date into the cell where you typed the formula. The date will be updated every time the 'Enter' key is pressed when you are working in the workbook where the formula has been inserted.
5. Use the procedure above to insert the dynamic date into any other cells in the open spreadsheet that you would like to display the current date.

Sunday, July 15, 2012

How to Adjust the Color of Excel Bars in a Bar Chart

1. Open the formatted workbook and double-click the specific bar element you wish to change.
2. On the 'Patterns' tab in the 'Area' section of the 'Format Data Series' dialogue box, click your preferred color. You can preview it in the 'Sample' box in the lower left corner.
3. Click the 'OK' button. The chart will show the new color choice.
4. Save the changes to the chart and workbook.

How to Import a Word Doc Into an Excel Spreadsheet

1. Open Excel and click the 'Insert' tab in the toolbar at the top of the page. You can import a picture, chart or file here.
2. Click the 'Object' button and then click the 'Create from File' tab.
3. Click 'Browse.' A list of your folders and files will open. Select the Word document you want to import and click 'Insert.'
4. Click 'OK.' The Word document will now appear in your Excel spreadsheet. Click on the newly created object to move it to the desired position in the spreadsheet.

How To Use the List Feature In Excel

Excel 2003
1. Open Excel 2003. Create a new spreadsheet or open an existing spreadsheet.
2. Enter data headers in the first row. Enter the data below them.
3. Click and drag to select your data including the headers.
4. Open the 'Data' menu. Point to the 'List' menu and click 'Create List.'
5. Check the 'My list has headers' check box. Click 'OK.'
6. Click a header to sort the data based on that column.
7. Type in the bottom row containing an asterisk to add a row to your list.
8. Click and drag the handle at the bottom right corner of your list to expand the range.
9. Click outside the list cells to deselect the list.
Excel 2007 and 2010
10. Open Excel 2007 or 2010. Create a new spreadsheet or open an existing spreadsheet.
11. Enter data headers in the first row. Enter the data below them.
12. Click and drag to select your data including the headers.
13. Click the 'Home' ribbon and click 'Format as Table.' Select a table style from the drop-down menu.
14. Check the 'My table has headers' check box. Click 'OK.'
15. Click a header to sort the data based on that column.
16. Select the bottom right corner and press 'Tab' to insert a new row.
17. Click and drag the handle at the bottom right corner of your list to expand the range.
18. Click outside the list cells to deselect the list.

Saturday, July 14, 2012

How to Make a Time Sheet

Microsoft Excel 2010
1. Select a Microsoft Excel 2010 template. Open Excel 2010 and select the 'File' tab. Click 'New.' Select the 'Time Sheets' category. Review the available time sheets. Preview the time sheet by clicking the image. A small preview and description appears in the right task pane. Download the template by clicking the 'Download' icon beneath the image and preview.
2. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
3. Save the changes to the time sheet by clicking the 'Save' icon on the Quick Access Toolbar.
OpenOffice
4. Select an OpenOffice template. Access the OpenOffice website. Type 'Time Sheets' in the search box. Review the available time sheets that appear. Click 'Use This' to download the template to your computer.
5. Open the downloaded file in OpenOffice Calc. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
6. Save the changes to the time sheet by clicking the 'Save' icon.
Google Documents
7. Select a Google Documents template. Access the Google Documents website. Type 'Time Sheets' in the search box. Click 'Search Templates.' Review the available time sheets that appear. Click 'Use This Template' to download the template to your computer.
8. Open the downloaded file in Google Documents. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
9. Save the changes to the time sheet by clicking the 'Save' icon.

How to Open a Wb3 File in Excel 2003

1. Open Excel 2003, and select 'Open' from the 'File' menu.
2. Locate the WB3 file that you wish to open in the 'Look in' list.
3. Click the name of the file, and click 'Open.'

How to Send Email Using Excel 2007 VBA

1. Start Microsoft Office 2007 and select the 'Developer' tab. Click on 'Visual Basic' and select 'Tools.' Click on 'References' check 'Microsoft Outlook 12.0 Object Library' and select 'OK.'
2. Type the following to create a new sub:Sub sendEmail()Press 'Enter.'
3. Type the following to create your variables:Dim oLookApp As Outlook.ApplicationDim oLookMail As Outlook.MailItem
4. Type the following to assign the outlook object and create a new email:Set oLookApp = New Outlook.ApplicationSet oLookMail = oLookApp.CreateItem(0)
5. Type the following to define the variables for your email:With oLookMail.To = 'john.doe@yahoo.com'.Subject = 'My Workbook'.Body = 'I'm e-mailing this workbook..'.Attachments.Add ActiveWorkbook.FullName.sendEnd With
6. Execute your sub to email the current Excel workbook.

How to Insert Hatching in Microsoft Excel Shapes

1. Right-click on the Excel 2010 spreadsheet shape in which you want to insert hatching.
2. Click 'Format Shape' to display an options box.
3. Click 'Fill' to display fill options.
4. Click 'Pattern Fill' from the list to display available fill patterns.
5. Click the hatching pattern you want to insert in the shape and then click 'Close.'

Friday, July 13, 2012

How to Create Rounded Borders in Excel

1. Open the Excel worksheet.
2. Click the “Insert” tab on the command ribbon.
3. Click the arrow under the “Shapes” button in the “Illustrations” group.
4. Click the preferred shape, such as “Rounded Rectangle.” The cursor will convert to a “ ” sign.
5. Click and drag the cursor on the worksheet where you wish to insert the rounded border. For example, click and drag inside a cell to create a row or column header.
6. Click the sizing handles on the shape to resize the rectangle.

How to Remove Protection From Cells in Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'
2. Click 'File,' then 'Open.' Locate, select and open your workbook that has the cells you wish to unprotect.
3. Locate the protected cell.
4. Click the cell, then click 'Format,' followed by 'Cells.'
5. Click 'Protection.' Click the checkbox next to 'Locked' to remove the cell protection. Click 'OK' to close the Format Cells dialog. You have now removed protection from the cell.

Thursday, July 12, 2012

How to Insert and Size Diagrams in Microsoft Excel 2003

1. Access the diagram menu. Scroll to the “Insert” tab on the command bar and select “Diagram.”
2. A diagram selection box will open where you can select six different types of diagrams: organization chart, cycle diagram, radial diagram, pyramid diagram, Venn diagram and target diagram. Choose the desired diagram by left-clicking on it and then click the “OK” button to insert the diagram.
3. Place the diagram. To move the diagram around, left-click on the gray border and hold as you drag the diagram where desired on the spreadsheet.
4. Set diagram attributes. You can add levels to your diagram and size it by clicking on the “Insert Shape” button on the diagram properties box. You can easily label each level and shape by left-clicking on “Click to Add Text” field and entering the desired text. Finally, you can even change the type of the diagram by clicking on the “Change To” drop-down menu and selecting a different diagram shape.
5. Insert the diagram. To insert the diagram, left-click on the “X” in the right corner of the diagram properties box to close it; the diagram will be inserted into your spreadsheet when you do this.

How to Merge Data From Several Excel Workbooks

1. Click the Microsoft Office button on the screen's top-left corner, and select 'Excel options.'
2. Under 'Customize,' click 'All Commands' in the 'Choose commands from' list.
3. Check the box next to 'Compare and Merge Workbooks.' Click 'Add,' and then click 'OK.' The 'Compare and Merge Workbooks' icon will now appear in the quick access bar at the top of the window,
4. Move all workbooks to a single folder. If they share a name, rename them as necessary.
5. Open one workbook. Click the 'Compare and Merge Workbooks' icon.
6. Select the other workbooks whose date you want to merge. To select multiple workbooks, press the 'control' or 'shift' keys as you select them.
7. Click 'OK.'

How to Calculate Negative Times in Excel

1. Open your Excel 2010 worksheet. Click on an empty cell anywhere on the worksheet.
2. Type '-'1:30'' into the cell. The number '-.0625' should appear. Right-click the cell and choose 'Format Cells.' Choose 'Time' from the list on the left and click 'OK.' The number changes to '#####.'
3. Click on the 'File' tab at the top of the screen. Choose 'Options' from the list that appears.
4. Click 'Advanced' on the list on the left side of the Options window. Scroll down to the bottom of the 'Advanced' screen and look for the 'When calculating this workbook' heading. Click the box next to 'Use 1904 date system.'
5. Click 'OK' to close the Options window. The time that you entered now reads '-1:30.' You can perform normal mathematical operations on the number and it will behave exactly like a negative number would.

How to Make an Exponent on Excel

Insert Exponent into Formula
1. Open the Microsoft Excel spreadsheet that contains the formula where you want to add an exponent.
2. Click on the cell that contains your formula. The formula will appear in the bar above the Excel spreadsheet. Click on this bar and place your cursor where you want to add the exponential number.
3. Enter the following into the formula bar, without quotes: 'power(x,y)' where 'x' is the desired number and 'y' is the exponent. If needed, you can wrap the entire 'power' function in parentheses. Additionally, you can write this in shorthand as 'x^y', without quotes.
Have Numbers Display as Exponents
4. Open your Microsoft Excel 2010 spreadsheet where you want to make your numbers display as exponents.
5. Click the top-left cell of the area that you want to change, then hold shift and select the bottom-right cell of that area. If you want to change the entire spreadsheet, you can just click the small box that sits above the '1' and to the left of the 'A' in the top-left corner of the spreadsheet.
6. Right-click anywhere on the selected cells. Choose 'Format Cells' from the pop-up menu.
7. Click on 'Scientific' from the list on the left side of the Format Cells window. On the right side of the window, click the up and down arrows to determine how many decimal places you want to display, then click 'OK.' Excel will now display numbers in those cells like '1.25E 02,' which is the same as '1.25^2' or '125.'

Wednesday, July 11, 2012

How to Open a Password Protected Microsoft Office Excel Sheet

1. Browse to the location where the password-protected Excel document is saved using Windows Explorer.
2. Double-click the document you wish to open. Excel will launch and display a dialog box explaining that the document is protected and requires a password.
3. Enter the password in the space provided, and hit 'OK.' This will open the document and allow you to view and edit the contents.

How to Do an Excel Chart With Multiple Legends

1. Open your Microsoft Excel spreadsheet that already has your data entered into the cells.
2. Click on cell A1, followed by 'Control' and 'A.' This is a shortcut that highlights your complete set of data.
3. Click 'Insert' on your toolbar, followed by 'Bar,' 'Pie' or another form of chart that you like. The chart will appear in your spreadsheet.
4. Click on the chart. Click 'Layout' and then the Legend icon. Click the button that specifies the your legend's orientation such as 'Show Legend at Right.' The legend will appear in your chart.
5. Click on the legend table. Click 'Control' and 'C,' followed by 'Control' and 'V.' A second legend will appear in your graph.

How to Make a Graph From CSV

1. Launch Excel 2010, click 'File' in the top menu and select 'Open.' Click on the file types drop-down menu at the bottom right of the File Open dialog box. You will need to select either 'All files (*.*)' or 'Text files (*.prn, *.txt, *.csv)' because by default, Excel filters your file choices to show only its native file types. Navigate to your file and click 'Open.'
2. Find an empty spot in your spreadsheet and click a cell. The exact location is not important, as you are selecting a place to put your chart while you work on it.
3. Click 'Insert' in the top menu and select any one of the chart types you feel will best represent your data. For instance, click 'Line' then select '2D line.' A blank chart area appears in your spreadsheet.
4. Right click in the blank chart area and click 'Select data.' When the 'Select Data Source' window appears, use you mouse to click and drag over the cells that contain the data you want in the graph. When you release the mouse, you will see the chart automatically updated with your data.
5. Modify the data as required for your purposes. You can modify the text around the X and Y axes by double-clicking on them and modifying the text in the dialog boxes.
6. Save your file when your modifications are complete. If you wish to use this chart in a presentation, or include it in a document, you can copy it to that document: clicking on your chart and press 'Ctrl' plus the letter 'C' to copy it, then switch to the other document and press 'Ctrl' plus the letter 'V' to paste it into the document. You will need to make sure that the other application is capable of displaying the graph or chart, but Microsoft's other major applications, such as Microsoft Word and PowerPoint, can do this.

How to Make a Watermark in Excel

1. Open the Excel workbook in which you want to create a watermark.
2. Click View on the Excel toolbar, or use the keyboard shortcut 'Alt' plus 'V.'
3. Click the View menu and select Header and Footer.
4. Choose Custom Header in the Page Setup dialog box that appears. Click the 'Center Section' so your cursor is active in the corresponding field.
5. Click the 'Insert Picture' button on the right; this button has a picture of mountains and the sun. Navigate to the picture that you wish to use as a watermark and double-click the graphic to insert it.
6. Click the 'Format Picture' button on the far right of the Header dialog box. Resize the picture to fit your page using the options on the Size tab.
7. Click the Picture tab. Under Image Control in the drop-down menu next to 'Color' choose Washout. This will give your graphic a watermark appearance.
8. Click 'OK' on the Format Picture dialog, and again on the Header and Page Setup dialogs.

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.

Subscribe to: Posts (Atom)