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....
Browse » Home » Archives for September 2011
Wednesday, September 28, 2011
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...
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...
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 chang...
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 'O...
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...
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 'O...
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...
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 additio...
How to Close the Header Footer Screens in Excel 2007
Reset Normal View1. 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 View4. 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...
How to Scatter Plot Data on Excel
Scatter Plot Data in Excel 20031. 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...
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...
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.'...
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...
Sunday, September 25, 2011
How to Use Negative Numbers to Calculate in Excel
Calculating Negative Numbers1. 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...
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...
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 selecti...
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...
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 ce...
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...
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 skewne...
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 resul...
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 cha...
How to Convert a Microsoft Excel File to a PDF File
Converting Excel Worksheet to PDF1. 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 PDF4. 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...
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...
How to Filter Using Keystrokes
Microsoft Excel1. 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...
How to Create a Data Entry Form for an Excel Spreadsheet
Restricting the Type of Data Entered1. 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...
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 spreadshe...
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...
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...
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...
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 'O...
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 li...
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 me...
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...
Tuesday, September 20, 2011
How to Convert Excel 2003 Macros to Excel 2007
Change the Macro Settings in Excel 20071. 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 Paste7. 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...
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...
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...
Monday, September 19, 2011
How to Stop Running on Open in Excel Spreadsheets
Disable Macros in Microsoft Excel 20071. 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...
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...
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...
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...
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...
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...
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...
How to Embed a Word Document in Excel
Create from a File Option1. 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...
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 'Inspec...
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-US2. 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...
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...
How to Rank Without Duplicating in Excel 2007
Creating the Formula1. 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 B...
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'...
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 'Finis...
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'...
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 secti...
How to Insert Copied Cells Multiple Times in Excel
Fill Handle1. 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...
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...
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...
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 Sub4. Exit...
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...
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 indentati...
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...
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'...
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 highlight...
Tuesday, September 13, 2011
How Do I Turn a Microsft Excel Spreadsheet Into a Chart?
Instructions1. 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...
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...
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...
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 spreadshe...
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 workshe...
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...
Monday, September 12, 2011
How to Set Up a Linear Program in Excel 2007
Linear Program Setup1. 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...
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...
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...
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'...
Sunday, September 11, 2011
How to Use an Excel Workbook Over a Network
Microsoft Excel 20031. 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...
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...
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...
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 workshe...
Subscribe to:
Posts (Atom)