Wednesday, September 28, 2011

How to Insert a Button in Excel 2007


1. Open Excel and check that the 'Developer' tab is available on the ribbon. If it is not, click the Windows button, and then click the 'Excel Options' button. In the 'Popular' options, click the check box next to 'Show Developer tab in the Ribbon.' Click 'OK.'
2. Click the 'Developer' tab and click 'Insert.'
3. Click the first icon in the upper left corner of 'Form Controls' for inserting a form control button, or click the first icon under 'ActiveX Controls' for an ActiveX button.
4. Left-click on the portion of the Excel sheet where you want to create the button and drag to create a box.
5. Assign a macro to the button in the 'Assign Macro' window that pops up for the form button. You can create a new macro by clicking the 'New' button or record one by clicking the 'Record' button. 'New' opens Visual Basic so you can enter your code. To record a macro, press the 'Record' button and give the macro a name. Assign a shortcut key and description if desired and click 'OK.' Then perform the actions the macro should perform. When finished, click the 'Stop Recording' button in the ribbon and the macro is saved. To add code to the ActiveX button, double-click the button and then add in your code. Press 'F1' for help and press 'Alt' 'Q' to save and return to Excel.
Read more ►

How to Convert Excel to a Visio Organization Chart


1. Create a table in Excel listing all of the names and titles you want to use in the organization chart. Enter the headings 'Title,' 'Name' and 'Reports To' in the first row of the worksheet. Enter each person's title, name and to whom they report. For the CEO or anyone else who does not report to anyone, leave that cell blank. Save the worksheet and close Excel.
2. Open Visio. Go to the 'File' menu or tab and select 'New.' Select 'Business' as the template category and then click on 'Organization Chart Wizard.' The wizard will open.
3. Select 'Information That's Already Stored in a File or Database' and click 'Next.' Choose 'A Text, Org Plus or Excel File' and click 'Next' again. Click 'Browse' and locate the Excel worksheet you created. Select this file and click 'Next' to import it.
4. Select the appropriate headings that correspond to those in your Excel worksheet, such as 'Name' and 'Reports To.' Click 'Next.' Add the columns from which you want to display data in the organization chart and click 'Next.' Click 'Next' again and then click 'Finish.' A basic organization chart will appear.
5. Make changes to the organization chart as desired. Right-click a shape and select 'Format' to fill it with color or change the line. Select text in a shape, right-click and select 'Font' to change the font, size or color. Save the organization chart when you are finished.
Read more ►

How to Add 12 Weeks to a Date in Excel


1. Open Microsoft Excel.
2. Write the start date in cell A1 using the familiar format month/day/year and press 'Enter.' As an example, to enter January 1, 2010, you would enter '1/1/2010' in cell A1. Alternatively, enter the function '=today()' to enter the current date.
3. Type '=A1 84' in cell B1 and press 'Enter' to calculate the date exactly 12 weeks from the start date. Alternatively, enter '=A1 (7*12)' to break up the number of days in the week times the number of weeks. This allows you to easily change the number of weeks. You can even reference the number of weeks entered in another cell, such as A2, by changing the formula to '=A1 (7*A2).'
Read more ►

How to Name a Chart Object in Excel


1. Right-click your Excel spreadsheet file and select 'Open With.' Click 'Microsoft Excel' in the list of programs.
2. Click the chart you want to edit in the spreadsheet. Click the 'Layout' tab or ribbon if you have Excel 2007.
3. Click the 'Properties' button. Type a new name for the chart in the 'Chart Name' text box. Click 'OK' to save the changes.
Read more ►

How to Protect Individual Cells in Excel 2003


1. Launch 'Excel 2003' and open the file that contains the individual cell that you want to protect. Click once on the cell that you want to protect. This action will display a border around the cell to indicate that it is selected.
2. Click the 'Format' drop-down menu, and select the 'Cells' option. Select the 'Protection' tab. Click the check box next to the 'Locked' listing.
3. Click the 'Tools' menu and highlight 'Protection' to display a list of available options. Click the 'Protect Sheet' listing, and click 'OK.'
Read more ►

Tuesday, September 27, 2011

How to Convert a Number to a Date in Excel 2003 When the Format Is yyyymmdd


1. Create a grid with your numbers in Excel, if you haven't already done so. Open a new spreadsheet and type your numbers into column A. Leave column B blank – it will be used momentarily for the data calculation.
2. Type the following formula into column B:=DATEVALUE(MID(A2,5,2)'/'RIGHT(A2,2)'/'LEFT(A2,4))This sets up the formula to convert your numbers in column A to a date format, and the result will be displayed in column B. The output won't actually look like a date until you format the cell. Continue this formula for as many rows as you have data. You do this by moving your mouse to the corner until the cursor changes to a cross; then, click and drag down the number of rows required.
3. Highlight the cells in column B that you want to convert to a date. Select 'Format' and then 'Cells.' Select 'Date' in the Format Cells box. Choose any date format desired. Readable dates instantly populate in column B of your spreadsheet.
Read more ►

How to Unprotect a Word Doc


1. Click the 'Review' tab at the top of the screen.
2. Click 'Restrict Editing' in the Protect group.
3. Click the 'Stop Protection' button that appears within the Restrict Formatting and Editing window.
4. Enter the password in the blank field in the Unprotect Document dialog box that opens. Click 'OK.'
Read more ►

How to Make an Invoice Spreadsheet in Excel


1. Open Microsoft Excel. A blank spreadsheet will open, and you will notice that predetermined rows and columns are already set up.
2. Enter a title at the top of the spreadsheet. Start typing your title into cell A1. Having a clear and specific title for the spreadsheet will help you remember what invoices are on a particular spreadsheet. Perhaps you need separate spreadsheets for invoices received and invoices sent out. Bold the title by clicking on the cell and using the bold tool on the top toolbar.
3. Set up column headings a few lines down from the title. Begin entering column headings in column A. Some helpful column headings include Invoice Date, Invoice Number, Person or Company being invoiced, Invoice Amount, Date Received, Date Paid and others depending on your exact purpose for the invoice spreadsheet.
4. Bold and center the column headings. Click on the number to the left of the row where the column headings are entered; the entire row will be selected. Use the tools on the top toolbar to bold and center the column headings.
5. Enter data into the spreadsheet. Be sure to plug everything into the appropriate column. If you are missing a piece of information, simply leave the cell blank.
6. Format cells that contain numbers so that all of the numbers in the column look the same. To format cells, highlight all of the cells that have the same types of numbers in them, such as amounts. Then right-click on the highlighted cells and select 'Format Cells.' In the box that opens up, click on the 'Number' tab. Click 'Currency' in the 'Category' box to format all cells that contain amounts in the same way. Then choose how many decimal places you want to include and click 'OK.' You can also format dates, ZIP codes, phone numbers and other data. Select these options from the 'Category' box--if you don't see something listed there, it is most likely listed under 'Special.'
7. Add totals to the spreadsheet. To do this, click in the cell where you want the total to be located. Click on the sigma symbol (it looks sort of like a capital E) on the top toolbar. Click on the first cell to be included in the total, then drag down until the last cell to be included is selected. The cells will be outlined in a moving-dashed box. Press the 'Enter' key and the total will be inserted.
Read more ►

Monday, September 26, 2011

How to Alphabetize a List in Works/Word Documents


1. Type your list, in no particular order, with each item on a separate line.
2. Highlight the complete list.
3. In the 'Home' tab, in the 'Paragraph' group, select 'Sort.'
4. In the Sort Text dialog, navigate to 'Sort by.' Then select 'Paragraphs and Text, and then click either 'Ascending' or 'Descending.' Add more entries to the bottom of the list as necessary. Repeat the sort procedure of the list with the additions.
Read more ►

How to Close the Header Footer Screens in Excel 2007


Reset Normal View
1. Press the 'Esc' key if you are in 'Full Screen' mode and cannot see the Office Ribbon.
2. Click the 'Views' tab on the Office Ribbon.
3. Select 'Normal' in the 'Workbook Views' section. This will work from any view unless you have an open dialog box that you must address first. In that case, click 'Cancel' or 'OK' in the dialog box, depending on what the screen prompts say, then select the normal view.
Within Page Layout View
4. Place your cursor at the top edge of the page, where the white that represents the page ends before the top ruler or formula bar. The cursor will turn to an icon with two arrows pointing toward each other.
5. Click your mouse button while the cursor is in this mode. This hides your header and top margin while keeping you in 'Page Layout' view.
6. Scroll to the bottom of the page and place your cursor along the bottom edge until the double arrow icon appears again.
7. Click to close the footer.
Read more ►

How to Scatter Plot Data on Excel


Scatter Plot Data in Excel 2003
1. Open Microsoft Excel 2003.
2. Place one set of data in one column and another set of data in an adjacent column. For example, place one set of data in column A, which will be your X values, and then place another set of data in column B, which will be your Y values.
3. Select the range of values to be included in the scatter plot chart. To select the range, click the first cell to be included and then drag your mouse towards the last cell to be included.
4. Click the 'Insert' menu and click 'Chart.'
5. Click 'XY (Scatter)' under the 'Chart Type' box. Select the chart sub-type you want to use under the 'Chart sub-type' box.
6. Click 'Next.' This will show you the data range and a view of your chart.
7. Click 'Next' to proceed to the 'Chart Options' window. Enter the information for 'Chart Title,' 'Value (X) axis,' and 'Value (Y) axis.' The 'Value (X) axis' is a descriptive title for the values of the X axis, while the 'Value (Y) axis' is a descriptive title for the Y axis. For example, if you're tracking weekly expenses, you can use 'Days' for the title of the X axis and 'Total Daily Costs' for the title of the Y axis.
8. Click 'Next' to proceed to the 'Chart Location' box. You have the option of placing the chart in the same worksheet as your data or in a separate worksheet.
9. Click 'Finish' to exit and display the scatter plot chart.
Scatter Plot Data in Excel 2007 or 2010
10. Open Microsoft Excel.
11. Place one set of data in one column and another set of data in an adjacent column. For example, place one set of data in column A, which will be your X values, and then place another set of data in column B, which will be your Y values.
12. Select the range of values to be included in the scatter plot chart. To select the range, click the first cell to be included then drag your mouse towards the last cell to be included.
13. Go to the 'Insert' tab. Click 'Scatter' under the 'Charts' group menu.
14. Click 'Chart Area' for the XY chart. This displays the 'Chart Tools,' 'Design,' 'Layout,' and 'Format' tabs specific to the XY chart.
15. Go to the 'Design' tab and click the chart style you want to use.
16. Type the title for your chart under 'Chart Title.'
17. Go to the 'Layout' tab and click 'Axis Titles.' Click the 'Primary Horizontal Axis Title' to place a title for the horizontal axis. Click the 'Primary Vertical Axis Title' to place a title for the vertical axis.
18. Press 'Enter' to exit and show the chart.
Read more ►

How to Lock Column Width in Excel 2007


1. Open Excel 2007 and click the 'Office' button. Select 'Open.' Browse the files and locate the workbook. Click the workbook and select 'Open.'
2. Select the worksheet in the workbook where the locked column width will occur. Right click the column header and select the 'Protection' tab. Make sure 'Locked' is selected. Click 'Ok.'
3. Click the 'Review' tab and select 'Protect Sheet.' Make sure the 'Protect worksheet and contents of locked cells' is selected. De-select the option to 'Select Locked Cell.' Click 'Ok.'
4. Right click the column and notice the option to adjust the column width is grayed out and not available.
Read more ►

How to Remove Filters in Excel 2003


1. Open Excel 2003 and select a workbook with data. Click 'File' on the menu bar. Click 'Open.' Search your files and locate the workbook. Double-click the workbook. The workbook opens.
2. Click the column header in the first column of your data. Select 'Data' on the menu bar and select 'Filter.' Click 'AutoFilter.' Drop-down lists appear in the column headers of your data. Click one of the drop-down lists and select a value. Notice your data is immediately filtered by this value.
3. Remove the filter by clicking 'Data' on the menu bar and selecting 'Filter.' Select 'AutoFilter.' The filter is removed from your data.
Read more ►

How to Change an Excel Chart to Ascending Order


1. Click on the column heading. This is Column A, B, C, etc, in a basic worksheet or the Table in a PivotTable report. Again, you must change the chart data in order to change the chart. Chart data can originate from a basic or PivotTable form.
2. Click the arrow Filter drop-down on Column heading or Column Labels. Again, a column label is the Column heading in a PivotTable.
3. Click the arrow Filter drop-down. In PivotTables, the Axis Fields are categories and the Legend Fields are Series.
4. Change the Excel Chart data to Ascending order by clicking 'Sort A to Z' for text, 'Sort Smallest to Largest' for numbers and 'Sort Oldest to Newest' for dates and times.
Read more ►

Sunday, September 25, 2011

How to Use Negative Numbers to Calculate in Excel


Calculating Negative Numbers
1. Enter a negative number into an Excel spreadsheet cell by using the minus sign (-) before the number.'-12' is an example of a negative number.
2. Use the arithmetic operator, the minus sign (-), to subtract a number using a formula.An example is the formula, '=25-10' that will give a result of '15.'
3. Use a function to calculate negative and positive numbers.An example of this is '=sum(a1:a3)', where cell a1=10, cell a2=12 and cell a3=-25 will give a result of '-3.'
4. Use a function to transform a positive result into a negative result by applying the negative sign (-) to the function. An example of this is '=-sum(a1:a3)' using the established values for those cells will give a result of '3.'
Change Negative Number Formats
5. Select a number or range of numbers to change the way Excel displays these numbers.
6. Click the 'Home' tab on the top menu and select the arrow in the 'Number' section.
7. Choose either the 'Number' or the 'Currency' category in the 'Category:' list in the 'Format Cells' window.
8. Select the number of decimal places by either typing it in or selecting the up or down arrow in the 'Format Cells' window.
9. Choose the 'Use 1000 Separator (,)' check box. Skip this if you are formatting currency.
10. Choose the currency symbol by clicking the down arrow in the box next to 'Symbol.' Skip this if you are not formatting currency.
11. Select the number format in the 'Negative numbers:' box in the 'Format Cells' window.
12. Click the 'OK' button.
Read more ►

How to Insert a Name in Excel 2007


1. Launch Excel 2007, and click on the cell for which you want to insert a name.
2. Click the 'Formulas' tab in the menu bar at the top of the window. In the 'Define Names' section, click the 'Define Name' button. Thereafter, a box titled 'New Name' will pop-up on your screen.
3. Enter the name you want to assign for the cell in the field displayed beside where it says 'Name.' If you choose a name that has two or more words, you'll need to separate each word with an underscore. Select the scope for the cell by clicking the 'Scope' drop-down menu and choosing to set the name for either the workbook or specific sheet of your Excel workbook.
4. Type any comments you may need to use as reference in the text field located beside where it says 'Comment.' You can use the comment area to remember the new name of the cell or how the cell is being used on your spreadsheet.
5. Click the 'OK' tab to finish inserting the name. Now you will see the name you inserted displayed on the upper-left region of the Excel spreadsheet whenever you click on the cell. For instance, if the cell you inserted a name for was cell F7, now instead of seeing F7 when you click on this cell you'll see the name you inserted. Now you can use this name when creating new formulas for your spreadsheet.
Read more ►

How to Insert a Column in Microsoft Excel


1. Open an Excel Worksheet or create a new document.
2.
Highlight the column directly to the left of where the new column should appear.
3.
Select 'Insert' on the standard taskbar.
4.
Click 'Columns' from the drop down menu list. The new column is now found to the left of your highlighted selection.
Read more ►

How to Set Custom Views in Microsoft Excel 2003


1. Set up the page the way you want it to be viewed. Zoom in or out as desired. Determine the page breaks and customize the page as desired.
2. Scroll to the “Views” tab on command bar to access the Views menu.
3. Select “Custom Views” from the drop-down list; a Custom Views properties box will open.
4. Click on the “Add” button to add a custom view. Under the “Name” field, you can name the view you've created. Check the relevant boxes to specify if you wish to include the print settings and any hidden columns, rows and cells in your custom view.
5. Implement the custom view. To add it to your spreadsheet, click the 'OK' button.
Read more ►

How to Change a Negative Currency Into Positive in Excel 2007


1. Select the cell range you would like to format or change on the spreadsheet. These should be the currency numbers you want to change. To select all the cells in the worksheet, right click and Select All or CTRL A.
2. Click on the 'Format' menu. Scroll down to 'Cells.'
3. Click on the 'Number' tab and select 'Currency' or 'Accounting' in the box. Select the currency symbol you want.
4. Select the display style for negative numbers in the 'Negative Numbers' box. You can also choose the number of decimal places you want to display in the cell.
Read more ►

How to Edit the Footer in Excel 2007


1. Open Excel 2007 and select a workbook. Click the 'Office' tab, and select the 'Open' icon. Browse your computer for the workbook. Click the workbook, and select the 'Open' button. The workbook opens.
2. Click the 'Insert' tab. Select the 'Header and Footer' icon. Look at your workbook and notice the visible header. Click the 'Footer' button, and notice the three sections of the footer.
3. Highlight any existing text in the left footer section to edit it. If no text exists, type information in this section. Use the 'Tab' key to advance to the center section.
4. Repeat Step 3 to edit text in the center and right sections of the footer.
5. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar. Your footer edits are saved in your workbook.
Read more ►

How to Calculate Kurtosis Skew


1. Enter the data. In Excel, enter each value in a cell in column A.
2. Go to an empty cell. Click on Formulas, then More functions, then Statistical, and then Kurt. In the first box, highlight the cells containing your data, then hit 'Enter.' This is the kurtosis.
3. Go to an empty cell. Click on Formulas, then More functions, then Statistical, and then Skew. In the first box, highlight the cells containing your data, then hit 'Enter.' This is the skewness.
Read more ►

Saturday, September 24, 2011

How to Run a T


1. Click the 'Data' ribbon in Excel 2007, then click 'Data Analysis.'
2. Click the T-Test type you want to use in the box that appears.
3. Click and drag the cursor over the categories and numbers you want to use.
4. Enter the alpha required for your test in the 'probability' field.
5. Click 'OK' to generate results.
Read more ►

How to Remove 0 From a Chart in Excel


1. Open the Excel spreadsheet file that contains the chart with the zero value.
2. Double-click the '0' to select it.
3. Press 'Delete' to remove the zero from your chart.
Read more ►

How to Convert a Microsoft Excel File to a PDF File


Converting Excel Worksheet to PDF
1. Go to Excel 2007 and open the spreadsheet you want to convert.
2. Select the Microsoft Office Button, then click “Print” and “Publish as PDF or XPS.”
3. Navigate to the folder where you want to save the file and name the PDF, then click “Publish.'
Converting a Selected Area to PDF
4. Open your file in Excel 2007. To convert a section of your document to a PDF, select the area and click the “Page Layout” tab.
5. Choose “Print Area” from the “Page Setup” group and click “Set Print Area.”
6. Select the Microsoft Office Button, then “Print” and “Publish as PDF or XPS.” Name your file and click “Publish.”
Read more ►

Friday, September 23, 2011

How to Use Microsoft Excel 2007 in a PowerPoint Presentation


1. Open your PowerPoint presentation and create a new slide where you want the Excel item to be by choosing 'Insert' from the menu at the top, followed by 'New Slide.'
2. Choose 'Insert' again from the menu at the top, then 'Chart,' and choose the type of chart you want (bar, line, etc.).
3. Insert your data for the chart into the Excel spreadsheet that opens up. You don't have to use the data that appears on the spreadsheet (it is just a suggestion). The headers for the chart will run along the top (Row 1) and left side (Column A).
4. Close the Excel window to save your information. Your data will be reflected on the chart.
5. Edit the chart formatting by clicking on the chart element you want to change and making the changes on the menu that appears. You can reformat the color, fonts and many other features.
6. Edit the data on the chart by clicking on the 'Edit in Excel' button on the formatting palette. If you do not see the formatting palette, click on the 'Toolbox' icon just above the presentation window.
Read more ►

How to Filter Using Keystrokes


Microsoft Excel
1. Open the saved Excel 2010 worksheet.
2. Click a cell in the column you wish to filter.
3. Press the “Alt” key to display the alphabetical labels over the command ribbon.
4. Press the “H” key to access the “Home” tab. Alphabetical labels display over the Home tab commands.
5. Press the “S” key to open the “Sort Filter” menu in the “Editing” group. The “Sort Filter” options are “Sort Oldest to Newest,” “Sort Newest to Oldest,” “Custom Sort” and Filter.”
6. Press the navigation arrow keys to select the row with the preferred “Sort Filter” option you wish to apply to the worksheet data. If you highlight “Filter” and press “Tab,” the column header displays an arrow. The filter is enabled, but requires you to click the drop-menu and click the menu option.
7. Press “Enter” to filter the data.
Microsoft Word
8. Open the Word 2010 document you wish to filter.
9. Press the “Alt” key to display the alphabetical labels over the command ribbon.
10. Press the “H” key to access the “Home” tab. Alphabetical labels display over the “Home” tab commands.
11. Press the “S” and “O” keys to open the “Sort Text” dialogue box.
12. Press the navigation arrow keys located on the right section of the keyboard to navigate the options in the screen’s dialogue box. Categories include 'Sort by,' 'Type,' and 'Ascending' and 'Descending.'
13. Press the “Tab” key to tab to the next category to the right. Continue pressing the navigation arrow keys to highlight the preferred option in each text box.
14. Press “Enter.” The Word document sorts.
Read more ►

How to Create a Data Entry Form for an Excel Spreadsheet


Restricting the Type of Data Entered
1. Select the cell or range of cells in which you want to restrict data entry.
2. Go to the Data tab in Excel 2007 and select 'Data Validation.' (In Excel 2003 or earlier, click on the 'Data' menu, and select 'Validation'). A dialog box will appear with three tabs: Input, Input Message and Error Message.
3. Select the Input tab if it isn't up already. Allowed Validation will show 'Any Value.' Clicking the drop-down list will show the options you can restrict the field to. The most useful kinds of restrictions include whole numbers (for quantities of goods sold or purchased), dates and times (when something was done) and text length (to make entering names easier). Select the input type that best suits your needs. The list type validation is covered in the next section.
4. Select the logical operators (greater than, less than, in between) that suit the data entry form you're building.
5. Click on the Input Message tab and enter the message you want displayed when the cell is selected. This can help your end users figure out the form more quickly.
6. Click in the Error Message tab and enter the message you want displayed when the data is entered incorrectly. Click the 'OK' button on the lower-right edge of the dialog box to finish.
Using a Drop-Down List on a Data Entry Form
7. Create a range of acceptable data values in another set of cells in Excel; for example, a list of branch locations.
8. Select the cell you want the drop-down list to be in.
9. Go to the Data tab in Excel 2007 and select 'Data Validation.' For earlier versions of Excel, click on the 'Data' menu and select 'Validation.' This will bring up the Data Validation dialog box.
10. Select 'List' from the drop-down menu. The field for Source will become available.
11. Click on the 'Source' box. Then select the range of cells you created. Click the 'OK' button at the lower edge of the dialog box to finish.
Read more ►

How to Change the Field Properties to Default Values in Excel 2007


1. Click the MS Office Button in Excel 2007, and scroll down to 'Prepare' and then select 'Properties'.
2. Type in the new values and information you want into the property field boxes under the Document Information Panel. You can choose to change the default title, subject, keywords, category, status, and change the default comments.
3. Click the 'X' on the right of the Document Information Panel to close the properties box and return to your Excel spreadsheet.
Read more ►

Thursday, September 22, 2011

How to Change the Default Comment in MS Excel


1.
To change the username, select 'Tools' from the main menu (generally found across the top of the Excel worksheet). Select 'Options' from the drop-down menu that opens. A pop-up window opens; select the 'General' tab. In the box labeled 'User Name,' type in the desired name. This is the name to which all comments on this workbook will be attributed.
2.
Use the 'Format Comment' pop-up window to change the other parameters of the text box . Right-click on the cell that contains the comment. A drop-down menu opens; select 'Show/Hide Comment.' Place your cursor over the edge of the comment box and right-click. Select 'Format Comment' from the drop-down menu that opens.
3.
To change the comment text font, navigate to the 'Format Comment' pop-up window as described in Step 2. Select the 'Font' tab. Make desired changes to the font, font style, font size, font color and font effects. Click 'OK' to save the changes.
4.
To change the comment text alignment, navigate to the 'Format Comment' pop-up window. Select the 'Alignment' tab. Make the desired changes to the text alignment, the orientation of the text and the text direction. Click 'OK' to save the changes.
5.
To change the comment box lines and colors, select the 'Colors and Lines' tab in the 'Format Comment' pop-up window. Make desired changes to the fill (the color of the inside of the comment text box) and line (what type of border will be around the comment text box). Click 'OK.'
6.
To change the comment box size, select the 'Size' tab in the 'Format Comment' pop-up window. Make the desired changes to the height and width. Click 'OK' to save the changes.
7.
To change the protection of the comment box, select the 'Protection' tab in the 'Format Comment' pop-up window. Change the status of the comment from locked to unlocked, or from lock text to unlock text, by placing a check mark in the appropriate box. Click 'OK' to save the changes.
8.
To change the comment text box properties, select the 'Properties' tab in the 'Format Comment' pop-up window. You can use this tab to determine the positioning of the comment text box. 'Move with Cells' means that, if you copy and paste cells, the comment will be copied and pasted as well. 'Size with cells' means that, if you change the size of the cell (row height), the comment size will change as well. Click 'OK' to save the changes.
9.
To change the comment text box margins, select the 'Margins' tab in the 'Format Comment' pop-up window. Make the desired changes to the left, right, top and bottom margins of the comment text. This affects the comment and the user name to which the comment is attributed. Click 'OK' to save the changes.
10. To change the comment text box web functionality, select the 'Web' tab in the 'Format Comment' pop-up window. If you are linking this workbook and its comment to the web, you can insert alternative text that is viewable by users when the image of the workbook or comment is not available. Click 'OK' to save the changes.
11.
Read more ►

How to Do Line Graphs in Excel 2003


1. Open Microsoft Excel 2003 and create a new spreadsheet.
2. Type the names for each line in the first row. For example, if your data will contain information from months on the calendar, type 'January' in cell A1, 'February' into B1, and so on. Each column will correspond to one line on the graph.
3. Enter the data points for each column. Type the data for the 'January' line in column 'A' starting at 'A2,' for 'February' in column 'B' and continue until you have entered all of the data.
4. Click and drag over all of the data you entered to select it, including the label names in the first row. With the data highlighted, click on 'Insert' and select 'Chart.' This opens the Chart Wizard. In the 'Chart Type' list, select 'Line.' Select one of the 'Chart Sub-Types' from the images on the right. Click 'Next.'
5. Click on the 'Series' tab. Change the names for the labels if you want to and then click 'Next' to open the Chart Wizard's options.
6. Type a title for the graph in the 'Chart Title' field under the 'Titles' tab. Configure the options under the rest of the tabs as needed but you do not have to make anything different other than the default settings. Click 'Finish' to make your line graph.
Read more ►

How to Create Data Entry Forms


1. Click on the 'Forms' button on the left-hand of the screen. Use the Form Wizard for an automated guide through the data entry form creation.
2. Follow the Wizard instructions. When the Wizard asks you which fields you would like to use, either input the fields you would like or use the suggestions. For the purposes of this tutorial select 'All.' Use the double arrow (>>) to perform this action in one step.
3. Choose a layout. You can experiment with several different layouts within the Wizard to see which one is best suited for your needs. The most basic layout is Columnar. Choose 'Columnar' and select 'Next.'
4. Choose the style you would like your form to be in and the title. Select 'Standard' and title your form with a relevant name. For this example, use Client Contacts.
5. Choose 'Open the form to view or enter information.' This is the last screen. Select the 'Finish' button.
6. Add records by using the buttons at the bottom of the table. To customize the form go to Design View by clicking on 'View' at the upper left-hand side of the screen. Use controls like text boxes, labels, and combo boxes to provide the user with predefined information about the kind of data needed for input into the form. If you don't see the Toolbox, click on 'View' and select 'Toolbox.' When finished, the form will automatically save. See Resources for Access data entry form templates.
Read more ►

Wednesday, September 21, 2011

How to Copy an Excel Spreadsheet Into a Word Document


1. Click and hold the left, top cell in your Excel spreadsheet using your mouse and then move it down and right to select all cells you want to copy.
2. Open the Word document and click the 'Home' tab on top.
3. Click 'Paste' and select 'Paste Special.'
4. Select 'Excel Worksheet Object' from the 'As' list and check 'Paste link' in the 'Paste Special' window. Click 'OK.'
Read more ►

How to Insert Pictures Into Excel 2007


1. Click on the 'Insert' tab.
2. Click on 'Picture.'
3. Locate the picture or file on your computer, then click on 'Insert.'
4. Left-click with your mouse on the picture, hold, then drag the picture to the correct position you would like.
Read more ►

How to Apply Functions to Microsoft Excel 2003


1. Click the 'Tools' menu in Excel.
2. Place the mouse-pointer over the 'Macro' icon, and click 'Visual Basic Editor.'
3. Click 'Insert,' and then click 'Module' from the drop-down menu.
4. Select a declaration from the drop-down arrow. Enter the function code in the window.
5. Click 'File' in the menu, and then click 'Close and Return to Microsoft Excel' option from the drop-down menu.
Read more ►

How to Do Scale Breaks on a Graph in Excel


1. Launch Microsoft Excel 2010.
2. Click the 'File' tab at the top-left corner of the window, click 'Open,' then double-click the file containing the graph that you wish to edit.
3. Click the worksheet tab at the bottom of the window on which the graph is displayed.
4. Click anywhere on the graph to open the 'Chart Tools' utility.
5. Click the 'Layout' tab under 'Chart Tools' at the top of the window.
6. Click the 'Axes' drop-down menu in the 'Axes' section of the ribbon at the top of the window, click the axis (horizontal or vertical) containing the scale that you want to adjust, then click 'More Primary Horizontal Axis Options' or 'More Primary Vertical Axis Options,' depending upon which axis you are editing.
7. Click 'Axis Options' at the left side of the window.
8. Click the 'Fixed' option to the right of 'Major Unit,' then change the value to your desired scale break.
9. Click the 'Close' button at the bottom of the window to apply your changes.
Read more ►

Tuesday, September 20, 2011

How to Convert Excel 2003 Macros to Excel 2007


Change the Macro Settings in Excel 2007
1. Click on the 'Office' Button.
2. Select 'Excel Options.'
3. Choose 'Trust Center->Trust Center Settings->Macro Settings.'
4. Click on 'Trusted Locations->Add New Trusted Locations.'
5. Add your workbook's location and then select 'OK.'
6. Open the workbook again in Excel 2007 and check for working macros. If they still don't work, you'll need to copy and paste the macro text over (see Section 2).
Copy and Paste
7. Open the workbook in Excel 2003.
8. Locate the code for your macro. An easy way to locate code for a particular macro is to click on the 'Tools' menu, and then 'Macro->Macros.' Find the macro name and then click 'Edit.'
9. Highlight the entire section of code and then press 'Ctrl' and 'C.'
10. Paste the text into a text file (using a program like Notepad or Wordpad).
11. Open the VBE in Excel 2007 and paste the code into a new code window.
Read more ►

How to Save an Excel Spreadsheet As an Image or Picture


1. Open the Microsoft Excel by clicking on the desktop icon or using the Windows Start menu.
2. Press 'Ctrl' and 'O' on the keyboard to open a spreadsheet file.
3. Select desired cells, columns or rows in the spreadsheet by holding the mouse left button. Or press 'Ctrl' and 'A' to instantly select the entire spreadsheet.
4. Press 'Ctrl' and 'C' to copy the selected spreadsheet.
5. Click 'All Programs' in Windows Start menu and open the folder 'Accessories.'
6. Click on 'Paint' to launch the image editor.
7. Click the Paint menu 'Edit' and then choose 'Paste' to insert the spreadsheet into the Paint window.
8. Click the Paint menu 'File' and then 'Save As.'
9. Select a desired image format using the Save as Type drop-down box. Note that Paint offers a variety of image formats including JPEG, TIFF and BMP.
10. Click 'Save' to save the spreadsheet as an image.
Read more ►

How to Graph Multiple Series Using Excel


1. Type your data that will go on your x-axis in column A. Use row 1 as the title for your data and then a separate cell for each piece of data.
2. Type your first series of data in column B. Use row 1 as the title for your data and then a separate cell for each piece of data.
3. Type your next series of data in column C. Use row 1 as the title for your data and then a separate cell for each piece of data. Continue this pattern for each series.
4. Highlight every cell with a title or data in it. Highlight by going to cell A1, then press and hold shift while moving the cursor with your arrow keys.
5. Click 'Insert.'
6. Select 'Line' or 'Scatter.' The chart will then display on the spreadsheet.
Read more ►

Monday, September 19, 2011

How to Stop Running on Open in Excel Spreadsheets


Disable Macros in Microsoft Excel 2007
1. Click the round Microsoft Office button at the top-left corner of the Excel screen and then select 'Excel Options' from the very bottom of the drop-down menu.
2. Select 'Trust Center' from the menu on the left-hand side of the screen and then click the 'Trust Center Settings' button.
3. Select 'Macro Settings' from the left-hand menu.
4. Select one of two options: 'Disable all macros without notification' or 'Disable all macros with notification.'
5. Click 'OK' on each of the next two screens to close the dialog boxes and save your changes.
Disable Macros in Microsoft Excel 2003 and Earlier
6. Click 'Tools' in the top menu bar, select 'Macro,' then choose 'Security' from the resulting sub-menu.
7. Select 'Medium' or 'High' from the list of options presented within the 'Security Level' tab.
8. Click 'OK' to confirm your changes.
Read more ►

How to Create a Simple Timeline with Excel


1. Open a Web browser. Go to Microsoft.com and conduct a search for 'Excel timeline template.' From the resulting list, find the template titled 'Timeline' for Excel 97 or later.
2. Download the template. Select the template link, which will take you to the template's Web page. Click 'Download Now,' accepting any Web browser prompts regarding downloading a file. Excel should open with the template selected.
3. Modify the template. Select the years along the timeline and change them to relevant dates. Make sure that your dates make sense for the timeline. Copy and paste text boxes to create new events, and modify existing text boxes as necessary. Move them by left-clicking and dragging to the right location. Copy and paste the lines that connect the events to the timeline, and move them as required.
4. Delete the instructions. Once you are familiar with how to manipulate the template, delete the instructions by selecting the text box and hitting 'DEL.'
5. Save the file. Select 'File,' and then 'Save As...', and give the file a name and location. In Excel 2007, select the Office Button and then 'Save As.'
Read more ►

How to Align the Header in Excel 2007


1. Open Excel 2007 and click the 'Insert' tab. Select 'Header And Footer.'
2. Click the 'Go To Header' button. (If you cannot see the 'Go To Header' button, click 'Header Footer Tools' to display the appropriate options.) Notice the three sections provided in the header section.If you type header text in the first section, it will be left aligned. Text in the second section will be centered. Text in the right section will be right-aligned.
3. Navigate to each section by pressing the 'Tab' key on the keyboard. For example, if you want your header text to be centered, press the 'Tab' key once and type your header in the second section. To navigate back to a previous section, press Shift Tab.
Read more ►

How to Make a Column Chart in Excel


1.
Find the file that contains your data and open it in Excel. If you still need to enter your data, open a new workbook instead. Input your data into a spreadsheet in the new workbook. As an example, we will create a small table containing data on the number of books bought at a yard sale. We will categorize the books by genre.
2.
Highlight the cells in the spreadsheet that contain your data. For our example, the cells A1-B8 have been highlighted.
3.
Choose the “Column” chart option from the “Insert” menu on Excel’s toolbar. Pick the type of column chart that you want to create. We will choose the side-by-side, non-stacked 3-D column chart for our example.
4.
Review the column chart that you have just created. Make any desired modifications to the chart. You can change the colors, wording of the title, location in the spreadsheet, and font used in the chart. Don’t be afraid to experiment! If you do make a change that you don’t like, just use the “Undo” option to get rid of the change.
5.
Save your file as an Excel workbook.
Read more ►

Sunday, September 18, 2011

How to Make Box Whisker Plots in Excel 2007


1. Open your Excel data. This instructional will assume that your first data column is in column 'A,' your first data value is in row '1,' and all your columns have 'n' data points.
2. Create new rows for your maximum, upper quartile, median, lower quartile and minimum values.
3. Calculate these statistics by entering the following values under your 'A' column:=MAX(A1:An)=PERCENTILE(A1:An,0.75)=MEDIAN(A1:An)=PERCENTILE(A1:An,0.25)=MIN(A1:An)
4. Copy and paste the same formulas to calculate statistics for your other columns, changing the letter for the corresponding column.
5. Select all the fields with your new statistical calculations. You can press 'CTRL' to select from more than one column.
6. Click the 'Excel Chart Wizard' button.
7. Select the 'Stock Open-High-Low-Close' diagram.
8. Click 'Next.' In the Data Range tab, click on 'Series In: Rows.'
9. Click 'Finish.' A box and whisker chart will display.
Read more ►

How to Create a Column Chart to Show Graphic Representation of Expenses on MS Excel


1. Type your data into a worksheet. Click on a cell, then type your data into the cells. In order for the column chart to work properly, you should enter your data in columns. For example, if you are comparing heights in inches of three people you might put 'John' into cell A1, 'Jane' into cell A2,'Bob' into cell A3 '72' in cell 'B1,' '69' in cell 'B2' and '75' in cell 'B3.'
2. Highlight the cells with your data by left clicking on the top left of the cells then dragging the cursor to the bottom right of the cells.
3. Click on the 'Insert' tab.
4. Click on the column' tab, then click on the column sub-type you want. For example, click on a '2-D Column' icon. Excel will insert a chart into your worksheet.
Read more ►

How to Delete Filtered Items in Excel 2007


1. Open Excel 2007 and then open the workbook containing the data you want to filter.
2. Select the range of cells you want to filter or click inside of a cell that is within the range of cells you want to filter.
3. Go to the 'Data' tab and click 'Advanced' in the 'Sort and Filter' group.
4. Select 'Filter the List, in Place' if you want to filter the list and delete duplicate items within the current worksheet. Choose 'Copy to Another Location' if you want Excel to delete the duplicate items and copy the remaining filtered items to another area on the worksheet.
5. Select the 'Unique Records Only' check box and click 'OK.' Excel will filter the list and delete any duplicate records it finds.
Read more ►

How to Embed a Word Document in Excel


Create from a File Option
1. Open Excel and the spreadsheet to embed the Word document in. Click on the 'Insert' tab, and then click 'Object' in the Text group. The Object dialog box appears.
2. Click on the 'Create from File' tab, and then click the 'Browse' button. Navigate to the file you want to add and click 'Insert.'
3. Choose any optional options. If you want to save room in the spreadsheet, click the 'Display as Icon' checkbox. This inserts a Word document icon. You can double-click on the icon to display the file. If you plan on continuing to work on the Word file in Word, but want the file information to update automatically in the Excel spreadsheet, click the 'Link to File' checkbox.
4. Click 'OK' to add a Word file to Excel.
Create from New
5. Open Excel and a spreadsheet to work with. Click on the 'Insert' tab, and then click 'Object' in the Text group.
6. Select the 'Create from New' tab. Select 'Microsoft Office Word Document' from the Object Type list (you will need to scroll to find it).
7. Click 'OK' to insert a Word document window in the spreadsheet. You can type in the document window as you would in a Word document. Notice that the Excel Ribbon has changed to reflect Word tools instead of Excel tools.
8. Type your document. You can insert graphics, use WordArt, add styles or apply other features available in Word. To return to the Excel spreadsheet, click off the document window. You can expand the Word document window in Excel by selecting the document window and dragging any of the handles. To return to the Word document, double-click the Word document window.
Read more ►

How to Find Hidden Columns on the Excel 2007 Document Inspector


1. Open Microsoft Excel.
2. Click the 'Office' icon located in the upper-left corner of the screen.
3. Select 'Prepare.'
4. Click 'Inspect Document.'
5. Check the box next to 'Hidden Rows and Columns.'
6. Click 'Inspect.'
Read more ►

How to Open a Xlxs File


1. Open your web browser and go to the following URL:
http://us20.trymicrosoftoffice.com/product.aspx?re_ms=oofamily=officeproculture=en-US
2. Download the free 60-day trial version of Microsoft Office Pro 2007, which includes Excel 2007.
3. Follow the onscreen install-wizard instructions to install the software on your computer.
4. Open Microsoft Excel and click on the Office start button in the upper-left corner of the window. From the list of options, select 'Open.'
5. Select the xlxs file that you wish to open and click the 'open' button at the lower right of the window. Excel 2007 will open the file.
Read more ►

Saturday, September 17, 2011

How to Do Error Bars on a Line Graph


1. Open the Excel 2010 spreadsheet that holds your line graph.
2. Click anywhere on the line graph to select it, then click the 'Layout' tab on top of the window, near the right end.
3. Locate the drop-down box in the 'Current Selection' area of the ribbon. Click the box and choose the data series that you want to display with error bars. To display all of your data series with error bars, choose 'Plot Area.'
4. Click the 'Error Bars' button in the 'Analysis' area of the ribbon. From the menu that appears, choose to add error bars for the standard error, for one standard deviation or for a 5 percent error on each value. If you want more precise control, choose 'More Error Bar Options,' then choose a data series from a small window that appears, as you can only add error bars this way for one series at a time. If you choose this option, another window appears.
5. Click the radio button next to 'Both,' 'Minus' or 'Plus' under the 'Direction' heading to determine which way your error bars will appear from the line on the graph. Under the 'End Style' heading, choose either to add or not to add a cap on the end of the bar.
6. Select your method for determining the error bar size under the 'Error Amount' heading. If you choose 'Fixed Amount,' 'Percentage' or 'Standard Deviation,' enter an amount into the box next to the option. You can also choose 'Standard Error,' but this option has no entry box, as the error bar sizes are determined solely by the values.
7. Change the appearance of the error bars from the 'Line Color,' 'Line Style,' 'Shadow' and 'Glow and Soft Edges' menus from the left side of the window. Each of these menus has numerous options to specify the exact look of the bars. Click 'Close' at the bottom of the window when done. Your error bars appear on the line graph.
Read more ►

How to Rank Without Duplicating in Excel 2007


Creating the Formula
1. Start Excel and open up the data range you want to use. For this example, we're assuming that the data values to be ranked are in column A, starting in column A2.
2. Enter the following formula in cell B2: =RANK(A2,$A$2:$A$10) COUNTIF($A$2:A2,A2)-1. This formula ranks the data, using the value in A2 as the seed value, going through the ranks from top to bottom. The COUNTIF in the second part of the formula increments the rank number by 1, EXCEPT if the rank value is the same.
3. Copy the formula in cell B2 through the entire range of cells from B3 to B10.
Read more ►

How to Create a Gantt Chart Using Excel 2003


1. Type a schedule into four columns in Excel. Type the names of milestones for your project in column 'A,' then type the start dates in column 'B,' then type the duration in days in column 'C' and the project end date in column 'D.'
2. Click the 'Chart Wizard' button, then click the 'Bar Chart' on the left-hand side. Click 'Stacked Bar Chart' as the chart subtype.
3. Click the 'Series' tab.
4. Click 'add' to add a series from your spreadsheet. Type a name for one of the series in the 'Series name' text box -- for example, type 'Start Date.' Type the location for your series into the 'Values' box -- for example, if your start dates are in cells 'B1' to 'B10,' type 'B1:B10.'
5. Repeat Step 4 to add the name and location of the 'Duration' series.
6. Click the 'Category Axis' button and highlight the tasks you listed in column 'A.' This adds labels on the left-hand side of your chart. Click the 'Finish' button.
7. Drag the bottom right corner of the chart until the chart fills the screen.
8. Right-click the 'X' axis on the chart. Click 'Format Axis,' then click '10' for the font size. Click 'OK.'
9. Right-click the 'Y' axis on the chart. Click 'Format Axis,' then click '10' for the font size. Click 'OK.'
10. Right-click the blue bars on the chart, then click 'Format Series.' Click the 'None' radio buttons next to Border and Area. Click 'OK.'
11. Right-click on the 'Y-axis' label, then click 'Format Axis.' Click the 'Scale' tab, then click the 'Categories in Reverse Order' check box. Click 'OK.'
Read more ►

How to Use Excel Tornado Diagrams


1. Open your spreadsheet and highlight the data you want to include in your tornado diagram. Go to 'Insert' and select 'Bar,' then 'Clustered Bar.'
2. Right-click on the horizontal axis and select 'Format Axis.' Select 'Axis Value' under 'Vertical Axis Crosses,' then enter your base case value.
3. Select the vertical axis for your diagram, then check next to 'Categories in Reverse Order' and click 'Low' on the 'Axis Labels' menu.
4. Click one of the bars to open a pane labeled 'Series Options' and slide the button under 'Series Overlap' over to the far right. Click 'Finish.'
Read more ►

How to Add Print Preview Ribbon to Main Ribbon


1. Open Microsoft Word 2010 and click the 'File' button, then select 'Options.' Click the 'Customize Ribbon' button on the left side of the Word Options menu.
2. Decide where you want to place this shortcut. You must create a custom group to place your shortcuts. For example, if you plan to add the shortcut to the Home tab, add a new group to this tab. Select 'New Group' on the right side of the Options. Select 'Rename' and type a name for this group. Click 'OK.'
3. Select the 'Choose Commands From' drop-down list, select 'Not On The Ribbon.' Select 'Print Preview Edit Mode.' Click 'Add' to move this command to the newly created group. Click 'OK.'
Read more ►

How to Print Selected Areas of a Worksheet in Excel 2003


1. Open your Excel worksheet and select the area you want to print. To select, hold down the left mouse button and drag your mouse over the area.
2. Right click on the selected area to summon a small pop-up menu of options.
3. Select 'Print' from the menu to bring up the print menu and select 'Print Selected Area.'
4. Click 'Print' to print the selected section.
Read more ►

How to Insert Copied Cells Multiple Times in Excel


Fill Handle
1. Open the Excel 2010 spreadsheet that has the information you want to copy. Click on the cell you want to copy.
2. Move the mouse to the lower right corner of the cell where there is a small square along the border. When you move your mouse over this square, called a fill handle, the mouse pointer turns into a plus sign.
3. Click and hold the mouse button over the fill handle, then drag the mouse in the direction that you want to copy the information. When you reach the last cell where you want to copy the information, release the mouse button. Excel now automatically copies the information into every cell between the first and last ones.
Paste Feature
4. Open the Microsoft Excel 2010 spreadsheet that you want to work with.
5. Click on the cell that you want to copy and press “Ctrl” and “C” to copy the cell’s contents to your clipboard.
6. Click on the first cell where you want to paste the information. To paste the information in a range of cells, click and hold the mouse button over the first cell, then drag the mouse down to the last cell and release the button.
7. Hold the “Ctrl” key and select the second cell, or group of cells, where you want to paste the information. Continue to hold “Ctrl” until you have selected every cell where you want to paste the information.
8. Press “Ctrl” and “V” to paste the information into every cell you have selected.
Read more ►

Friday, September 16, 2011

How to Create a Simple Budget Using Excel 2007


1. Open Excel, double click the tab called 'Sheet 1' and change the name to 'Expenses.' All of your expenses will be listed in this first Expense worksheet.
2. Click cell A1 and type 'Date,' press tab and type 'Expense Name,' press tab and type 'Amount ($)' and press tab one more time and type 'Notes.' These four headings will now be the titles of the first four columns.
3. For every expense you have, record the date of the expense in the 'Date' column, a description of the expense in the 'Expense Name' column, how much you spent in the 'Amount ($)' column and include any notes about the expense that are relevant in the final column.
4. Double-click on the second worksheet tab called 'Sheet 2,' and change the name to 'Income.'
5. Click on cell A1 and type 'Date,' press tab and type 'Income Name,' press tab and type 'Amount ($)' and finally press tab once more and type 'Notes.'
6. Record any income you take in on this worksheet, noting the date of the payment, a description of the income, the amount and any notes about the income in the appropriate columns.
Read more ►

How to Delete Punctuation All Caps in Excel


1. Open the Excel 2010 spreadsheet where you want to remove punctuation and use a different case. Click the 'Developer' tab at the top of the screen. Click the 'Record Macro' button, located on the left end of the Ribbon.
2. Type 'RemovePunctuationCaps' in to the 'Macro name' field. Enter 'q' into the 'Shortcut Key' field. Click 'OK' to close the window. Click the 'Stop' button on the ribbon to stop the macro from recording.
3. Click the 'Visual Basic' button on the ribbon to launch the VBA editor. Double-click 'Module 1' on the left side of the editor. If you have already created a module in this workbook, you will need to click on the last listed module, instead of 'Module 1.' You will see 'Sub RemovePunctuationCaps()' on the right side of the window.
4. Click on the right side of the window. Drag your mouse over the green text between 'Sub Remove...' and 'End Sub.' Delete all this text so that the 'Sub' and 'End Sub' lines are all that exist.
5. Copy and paste the following code between the 'Sub' and 'End Sub' lines in the VBA editor:Dim rng As RangeFor Each rng In Selectionrng.Value = StrConv(rng.Text, vbProperCase)Next rngThis will establish a variable named 'rng' and provide the code needed to change your selection into proper case. If you would rather use lower case, change 'vbProperCase' to 'vbLowerCase.'
6. Enter the following piece of code between the last code and the ''End Sub' line:With CreateObject('vbscript.regexp').Pattern = '[^A-Za-z0-9\ ]'.Global = TrueFor Each rng In Selection.SpecialCells(xlCellTypeConstants)rng.Value = .Replace(rng.Value, vbNullString)Next rngEnd WithThis will remove all punctuation from the selected cells.
7. Click the 'X' in the upper-right corner of the editor to close it. Click on the cell or cells where you have the text you want to convert. Press 'Ctr-Q' and the macro will perform the task.
Read more ►

How to Deselect a Cell


1. Launch Microsoft Excel.
2. Hold down the 'Alt' key and press 'F11.' This will open the Visual Basic editor.
3. Click 'Insert' and click 'Add new module.' Paste the following code in the module on the right and press 'Enter:'Sub UnSelectActiveCell()Dim Rng As RangeDim FullRange As RangeIf Selection.Cells.Count > 1 ThenFor Each Rng In Selection.CellsIf Rng.Address
ActiveCell.Address ThenIf FullRange Is Nothing ThenSet FullRange = RngElseSet FullRange = Application.Union(FullRange, Rng)End IfEnd IfNext RngIf FullRange.Cells.Count > 0 ThenFullRange.SelectEnd IfEnd IfEnd Sub
4. Exit the Visual Basic Editor to get back to Excel.
5. Click 'Tools' >> 'Macro' >> 'Macros' and then double-click 'UnSelectActiveCell.'
6. Hold down the 'Ctrl' key and select the cells that you wish to deselect.
7. Repeat Step 5.
Read more ►

How to Use Form Control Scrollbar in Excel


1. Open the 'Forms' toolbar in Excel 2003 by going to the 'View' menu, pointing to 'Toolbars' and selecting 'Forms.' Click the scroll bar button on this toolbar. Click on the first cell onto which you want to place the scroll bar and drag the mouse to the last cell onto which you want to place the scroll bar. In Excel 2007, go the 'Developer' tab. Click 'Insert' and select 'Scroll Bar' in the 'Form Controls' group. Click on the first cell onto which you want to place the scroll bar and drag the mouse to the last cell onto which you want to place the scroll bar.
2. Right-click the scroll bar on the Excel worksheet. Select 'Format Control.' The 'Format Control' dialog box will open. Go to the 'Control' tab.
3. Enter '1' into the 'Current Value' box to enable the scroll bar and direct the 'Index' formula to refer to the first item in the range or list. Type '1' into the 'Minimum Value' box, which confines the scroll bar to stop at the first item in the list or range. In the 'Maximum Value' box, type the number of entries in the list or range to limit the scroll bar to the last item. Type the number of increments by which you want to be able to scroll in the 'Incremental Change' box. Type an incremental number in the 'Page Change' box, as well. This form control dictates the increments the scroll bar will move if the users clicks the scroll arrows.
4. Type a cell reference into the 'Cell Link' box or click inside the 'Cell Link' box to place the cursor in it and then click on the cell you wish to use to display the number value showing the item selected when the scroll bar is in use.
5. Click 'OK' to save the changes and close the dialog box. Click onto the Excel worksheet anywhere outside of the scroll bar. Your scroll bar will now be functional.
Read more ►

How to Insert a Tab Character In a Cell


1. Open your Excel workbook and navigate to the worksheet you are working with. Click inside the cell that you want to modify.
2. Press the 'Ctrl,' 'Alt,' and 'Tab' keys simultaneously and see if a tab character is added to the active cell. Click the 'Format' button if the key combination does not add the tab character.
3. Click 'Cells' and then navigate to 'Alignment.' Click 'Left Indent' and then click the arrow key to add in an indentation that is the equivalent of a tab character. Click again to add another level of indentation.
Read more ►

How to Use WordArt in Microsoft Excel 2007


1.
On the 'Ribbon,' the group of icons and tools above the work area of the Excel workbook, select the tab called 'Insert.' On this tab you find a group called 'Text'. Within that group select the 'WordArt' option.
2.
A menu of choices will drop down. Select the option you would like to use.
3.
A text box will appear in your worksheet. Notice that the Ribbon has changed to include tools for you to use to tweak your WordArt to appear the way you would like it to.Double click inside the text box and type the text you would like to appear.
4.
If you would like to rotate your WordArt, click and drag the green circle appearing at the top of the text box.
5. To move your WordArt to a different part of the worksheet simply click and drag it to the location of your choice.
6.
The text in WordArt can be changed by clicking on the Home tab of the Ribbon and editing the text the same way traditional text is edited.
7.
Once your WordArt is adjusted to the style and location you choose, you can now print your document. WordArt will appear exactly as you created it on the printed page.
Read more ►

Thursday, September 15, 2011

How to Make a Histogram in Microsoft Excel


1. Open Microsoft Excel and go to 'Tools', 'Add-ins'. Place a check in the box beside 'Analysis ToolPak', click OK.
2.
Create a table with the following columns: Time of Day, Accidents, Range. Enter the data in the following image.
3. Go to 'Tools', 'Data Analysis' or 'Data', 'Data Analysis'. Click on 'Histogram' then click OK.
4. Click the box beside 'Input Range' and highlight the 'Accidents' data. Click the box beside 'Bin Range' and highlight the 'Range' data. The Input Range is the raw data while the Bin Range provides the intervals to separate the data into.
5.
In the 'Output Options' select 'Output Range'. Select an empty cell within the worksheet then press OK. This places the histogram and corresponding table on the same worksheet as the original information.
6.
According to the histogram, the most accidents occur at five hours during the day; when referring to the data table, those hours are 1am, 4am, 2pm (1400 hours), 5pm (1700 hours), and 11pm (2300 hours).
Read more ►

How to Rotate the Page in Excel 2007


1. Open a new or existing document in Excel.
2. Click on the 'Page Layout' tab.
3. Click on the 'Orientation' button in the Page Setup button group, then select 'Landscape' if 'Portrait' is already highlighted, or 'Portrait' if 'Landscape' is already highlighted.
Read more ►

Tuesday, September 13, 2011

How Do I Turn a Microsft Excel Spreadsheet Into a Chart?


Instructions
1. Open Microsoft Excel from the 'Start' menu and wait for the application to load.
2. Enter the data you want to convert into a chart into the cells. Include labels for each column in row 1.
3. Click and drag from the top right cell to the bottom left cell to highlight all the data you want to include in the chart. Highlight the column label headings along with the data.
4. Click the 'Insert' tab on the top of the screen.
5. Choose one of the chart templates from the drop down menus. As soon as you click a chart type it will automatically be inserted into the spreadsheet. You can double-click on the title of the chart to change it.
Read more ►

How to Set Up a Web Query in Excel 2003


1. Launch Excel and open a new spreadsheet.
2. Select any cell and open the 'Data' menu, open the 'Get External Data' sub-menu and click the 'New Web Query' option.
3. Type the URL address of the Web page you would like to analyze in the text box underneath '1'.
4. Choose an option underneath '2'. You can extract data from the entire page, from only tables or from a specific data table.
5. Select a formatting option: 'none', 'rich text formatting' or 'full HTML formatting'. If you just want to acquire data, then your best option is to select 'none'.
6. Click the 'OK' button and click it again when the dialog box pops up. Excel will populate your spreadsheet with the website data and start from the cell you previously selected.
Read more ►

How to Insert a Static Time in Excel


1. Start Microsoft Excel 2007, and open an existing workbook from your files in which you want to insert a static time into one of the cells. Alternatively, you can start a new, blank workbook to insert the static time.
2. Select the cell in the workbook you want to insert the static time into by clicking on it. The selected cell will now have a thick black box around it, indicating that it is selected and ready for the next step.
3. Hold down the 'Ctrl,' 'Shift' and ':' keys at the same time on the keyboard. This keyboard shortcut will instruct Excel to enter a static time into the selected cell.
4. Look at the cell, and see the current time to be inserted into the selected cell. This time will not change at any point unless you manually change it yourself. It will not update on its own at any time.
5. Insert the static time into any other cells in the open Excel workbook using the same procedure that is outlined in the previous steps.
Read more ►

How to Locate the NOW Function in Microsoft Excel


1. Open Excel 2010 and click the 'Formulas' tab. Select the 'Date and Time.' A drop-down list appears.
2. Select 'NOW.' The Function Arguments dialog box appears. Click 'OK.'
3. Review the date and time that has appears in your spreadsheet.
Read more ►

How to Make a Chart Using Excel 2007


1. Open Excel 2007 and open the file that contains the data you want to use to create a chart.
2. Highlight the data you want to include in the chart by left-clicking on one data cell and dragging your mouse across all of the cells you wish to include. The cells are highlighted in blue when selected.
3. Click on the 'Insert' tab of the toolbar and then select what chart you want to create. The chart is automatically created and placed inside the worksheet.
Read more ►

How to Convert Wk1 to Xls


1. Confirm that both the data and the format file for the WK1 file are in the same folder.
2. Open the Excel “File” drop-down menu. Click “Open.” Find and open the WK1 file in the folder tree. Don’t open the formatting file yourself, just the file with the WK1 extension.
3. Open the “File” drop-down menu again. Click “Save.” Select .XLS as the file type in the Save panel. Select “OK.” Excel then converts the workbook into the Excel format.
4. Open the converted .XLS file to check for errors. Search for the phrase “formula failed to convert.” If Excel was not able to convert a formula, it defaults to displaying the value that the formula produced and writes “formula failed” in a cell comment. To search the comments for this phrase, open the Edit drop-down menu and click following sequence: 'Find > Look in: Comments.' Recreate the lost formulas where possible.
5. Print out the new spreadsheets and compare the numbers with printouts of the original Lotus workbook spreadsheets, if they are available. This is an additional check to make sure the conversion was performed correctly.
Read more ►

Monday, September 12, 2011

How to Set Up a Linear Program in Excel 2007


Linear Program Setup
1. Enter a title for your linear program in cell A1. Enter labels for your variables in cells C3 and D3.
2. Type your variable coefficients in cells C5 and D5. List each coefficient in the column that corresponds to its respective variable.
3. Create a border around cells C6, D6 and B7. Cells C6 and D6 will be used to display the optimal solutions for your variables, and cell B7 will be used to show the value of the objective function given the optimal variable values.
4. List your constraints beginning with cell C9 and continuing down one row for each constraint. Use a different cell for each element of your constraints, including the inequality symbols, but excluding the operator symbols, such as the plus or minus symbol.For example, if one of your constraints is 3x1 6x2
5. Type the heading 'LHS' under your constraints in column C, and type the heading 'RHS' beside it in column D. List the right-hand-side values of your constraints vertically under the RHS heading, and create borders for each corresponding cell under the LHS heading. The LHS cells will be used to display the actual left-hand-side values of your constraints for the optimal solution.
6. Enter the formula '=SUMPRODUCT(C5:D5,C6:D6)' in cell B7, the optimal solution output cell. This formula will calculate the value of the objective function given the optimal values of the variables.
7. Enter the formula '=SUMPRODUCT(C9:D9,$C$6:$D$6)' in the first cell under your LHS heading, and copy the formula into each additional left-hand-side value output cell under the LHS heading. This formula will calculate the actual value of your constraints given the optimal values of the variables.
Linear Program Solution
8. Click 'Data -> Solver' to bring up the 'solver parameters' dialog box. Set the target cell to '$B$7,' the objective function value output cell. Set the solver to maximize or minimize the function, based on the purpose of your linear program.
9. Enter the constraints into the solver parameters dialog box. Begin by clicking 'Add' for each individual constraint. For each constraint, enter the cell reference for the corresponding cell under your LHS heading in the cell reference box, choose the proper inequality symbol from the drop-down box in the center, and enter the cell reference for the corresponding cell under your RHS heading.
10. Back in the solver parameters dialog box, click 'Options' to open the solver options dialog box. Click 'Assume Linear Model' and 'Assume Non-Negative,' then click 'OK.'
11. Click 'Solve' in the solver parameters dialog box, and Excel 2007 will fill in the optimal solution, the value of the objective function, and the actual left-hand-side values of your constraints.
Read more ►

How to Change Your Header Margins on Word


1. Double-click the Word document's header.
2. Change the top margin by editing the number--which is measured in inches--in the 'Header from top' setting in the 'Position' section on the Ribbon.
3. Change the bottom margin by editing the number--also measured in inches--in the 'Header from bottom' setting in the 'Position' section on the Ribbon.
4. Click 'Page Layout' on the top of the program. The options in the 'Page Layout' Ribbon lets you change the left and right indentations.
5. Change the left indent by editing the number in the 'Left Indent' listing in the 'Paragraph' section of the Ribbon.
6. Change the right indent by editing the number in the 'Right Indent' listing in the 'Paragraph' section of the Ribbon.
Read more ►

How to Make a T


1. Start Microsoft Excel. Press 'CTRL N' to create a new workbook.
2. Type 'Data1' and 'Data2' in cells 'A1' and 'B1,' respectively. You may replace these with your preferred variable names.
3. Enter your first set of data in column 'A,' starting from cell 'A2.'
4. Enter your second set of data in column 'B,' starting from cell 'B2.' Make sure that both columns have the same number of cells. For instance, if column 'A' contains five entries, column 'B' should also have five entries.
5. Highlight all non-empty cells (including the variables) and then press 'CTRL SHIFT F3.'
6. Select the cell below the last number in column 'A.' Type '=T.TEST' and then press 'CTRL A.' This opens a dialog box where you can enter parameters for the function.
7. Enter 'Data1' and 'Data2' into the 'Array1' and 'Array2' boxes, respectively.
8. Enter 1 into the 'Tail' box if you'd like to use one-tailed distribution. Otherwise, enter 2.
9. Enter the kind of test you'd like to perform in the 'Type' box. Use any of the values below.1 - Paired t-test2 - Two-sample equal variance3 - Two-sample unequal variance
10. Click on 'OK' to view the result of your formula.
Read more ►

How to Reset Shortcut Keys in Microsoft Excel


1. Open Microsoft Excel 2003 and click on the 'Tools' menu. Then click on 'Customize.' Ensure that the toolbar you want to reset is visible by clicking the 'Toolbars' tab in the 'Customize' box and selecting the toolbar of the shortcut keys that you want to reset.
2. Click on 'Customize Keyboard' while inside the 'Customize' box.
3. Choose to 'Reset All' while inside the 'Customize Keyboard' dialog box. Then click on 'Yes' when prompted to 'Reset All.'
4. Click on 'Close' and then 'Close' again to exit out of the 'Customize' box.
5. Change an individual shortcut key by clicking on 'Commands' while inside the 'Customize' box. Then click the button on the toolbar and choose 'Modify Selection.'
6. Type a name for the menu command in the 'Name' box. Use an ampersand before the letter you want to use as the keyboard shortcut. Then press 'Enter.'
Read more ►

Sunday, September 11, 2011

How to Use an Excel Workbook Over a Network


Microsoft Excel 2003
1. Create the workbook and make sure you include any final changes, as certain features in Excel cannot be changed after the workbook is shared. The most important features to create before sharing are conditional formats, subtotals, data tables, worksheet and workbook level protection and macros.
2. Designate user settings by clicking 'Tools' on the menu bar, then clicking 'Share Workbook.' Click the 'Editing' tab in the 'Share Workbook' dialogue box. Click the 'Allow changes by more than one user at the same time' check box.
3. Click the 'Advanced' tab and create settings for when to save the file and how to handle change conflicts between users.
4. Click 'OK,' then save the workbook when Excel prompts.
5. Share your workbook. Click 'File' then 'Save as...' on the menu bar. Navigate to the network location accessible to each user and save the workbook to your network.
Microsoft Excel 2007
6. Create the workbook and make sure you include any final changes, as certain features in Excel cannot be changed after the workbook is shared. The most important features to create before sharing are conditional formats, subtotals, data tables, worksheet and workbook level protection and macros.
7. Click the 'Review' tab, then click 'Share Workbook' in the 'Changes' group. This opens the 'Editing' dialogue box.
8. Create your settings. Click the 'Allow changes by more than one user at the same time' check box in the 'Share Workbook' dialogue box.
9. Click the 'Advanced' tab and create settings for when to save the file and how to handle change conflicts between users.
10. Click 'OK,' then save the workbook when Excel prompts.
11. Share your workbook. Click the Microsoft Office Button, then click 'Save as...' Navigate to the network location accessible to each user and save the workbook to your network.
Read more ►

How to Unhide a Very Hidden Sheet in Excel 2003


1. Open a workbook that you wish to unhide. Press 'Alt' 'F11' to open Visual Basic Editor.
2. Click 'View,' then click 'Project Explorer.' Click 'View' and click 'Properties' to open the 'Project Explorer' and 'Properties' windows that will give you a view of the very hidden worksheets.
3. Select the very hidden sheet in the 'Project Explorer' window and find the very hidden sheet in the 'Properties' window within the 'Visible' tab. Under the 'Properties' window, make the very hidden worksheet visible by selecting this code in the box '-1 --- xlSheetVisible.' You have now unhidden a very hidden sheet in Excel 2003.
Read more ►

How to Balance a Checkbook Using Excel


1. Open the Excel program from your start menu or by double clicking a shortcut on your desktop.
2. Label your headings on the top row and leave open columns between your headings. A1 should be labeled 'Method;' B1 should be blank; C1 should be 'Date;' D1 should be blank; E1 should be 'Description;' F1 should be blank; G1 should be 'Debit;' H1 should be blank; I1 should be 'Credit:' J1 should be blank; K1 should be 'Balance;' L1 should be blank; and M1 should be 'Cleared.'
3. Change your blank column widths to separate the data you will insert later. Click on the first blank column (B), hold the 'Ctrl' button down and click on the other blank columns, (D, F, H, J; L). They will be highlighted in black. Right click your mouse on any black column. A drop down bar will open, click on 'Column Width.' Change to '2' and click 'OK.'
4. Change your other Column Widths that will hold data to the size you desire. The most noticeable change will be the 'Description' column. Change this to a Column Width of '27' so it can hold enough text to record your information.
5. Format cells to hold currency. Click on 'G,' hold down the 'Ctrl' button and click on 'I' and 'K.' Right click on one of the black highlighted columns to see the drop down bar. Select 'Format Cells.' On the 'Number' tab, select 'Currency' and choose your decimal places and dollar sign. This will make your form consistent.
6. Insert your starting balance. On the first row, you want to insert only your starting balance in the 'K2' cell. This will be the number all your debits and credits will be added or subtracted from.
7. Insert your data beginning with Row 3. Check #'s, ATM, Deposit and other methods will be entered in Column A. Insert the date of the transaction (you may format this column by right clicking on 'C,' 'Format Cells' and selecting the date format you prefer). Enter the description and amount in the appropriate columns.
8. Create a running balance. Click on cell 'K3.' On the toolbar, click on the Auto Sum button which appears as a Greek letter 'E.' A dotted, moving block will appear on 'K2,' and you will see a bar under the toolbars with =SUM(K2). Insert your command after the K2: =SUM(K2-G3 I3) and click 'Enter.' You have formatted your cell data.
9. Format the 'Balance' column to update as you enter data. Click on the K3 cell, hold down the 'Ctrl' button and click the letter 'C' on the keyboard. This copies the format of that cell. Click on the K4 cell, hold down the 'Ctrl' button and click the letter 'V' on the keyboard. This pastes the format into that cell. Repeat the paste process as far down as you prefer.
10. Reconcile your Excel spreadsheet to your monthly bank statement. Put an 'R' in the Cleared column to indicate that an entry matches your bank statement and has been added or subtracted to your balance.
11. Verify your balance. Your bank statement may be different from your Excel balance. Certain transactions may not have cleared the bank that you have recorded. Take your Excel balance, and add or subtract any amounts that do not have an 'R' beside them to your Excel balance. This total should match your bank statement balance.
Read more ►

How to Add or Delete an Excel Worksheet


1. Open Microsoft Excel and open the file you want to change.
2. To add a worksheet, open the Insert menu and select Worksheet. The new sheet will appear before the selected sheet in the tab menu at the bottom of the Excel window. It will be numbered.
3. To delete a worksheet, first select the sheet by clicking its tab at the bottom of the Excel window, then open the Edit menu and select Delete Sheet. Click OK to delete the worksheet.
Read more ►

Blogger news