1. Create the information you want to appear on the map, such as statistics for a region. Enter the name of the place (such as city, state, or country) in column A and the number or statistic in column B.2. Once you have your data for your map, click File and Save. Highlight and select all your data rows and columns by holding the left button down and dragging it until all your data is selected.3. Go to the top of the screen and use your mouse to click Insert, followed by Map. Move your cursor (which will no longer flash now) and move it the part of the Excel page where you want your map...
Browse » Home » Archives for February 2012
Tuesday, February 28, 2012
How to Remove Scroll Bars in Pages
1. Open the document or spreadsheet from which you would like to remove the scroll bars.2. Select 'Tools' from the toolbar menu. Next, choose 'Options,' then click on the 'View' tab.3. Uncheck the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Then click 'OK.' (These options are in the 'Show' area under the 'View' tab in Microsoft Word; they are located in the 'Window Options' area under the 'View' tab in Microsoft Excel.)4. To show the scroll bars again, repeat steps 1 and 2. Next, place checks in the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Click...
How to Calculate Percentages in Excel
1. Type the amount in a cell. For example, a student received a score of 48 out of 60 on a test and wants to find his percentage. The student would type 48 in cell A1.2. Type the total in an adjacent cell. In the example, the student would type 60 in cell B1.3. Type '=amount cell/total cell' in an adjacent cell to the total cell. Replace amount cell and total cell with the cell addresses of the amount and total. In our example, the student would type, '=A1/B1' in cell C1. The total will display 0.8 in cell C1.4. Highlight the cell with the decimal, and click '%' button under the 'Home'...
How to Create Macros in Excel 2003
1. Open the Microsoft Excel 2003 application on your computer. Click on the 'Tools' option from the top toolbar menu and then click on the 'Options' button.2. Click on the 'Security' tab and then click on the 'Marco Security' option. Click on the 'Security Level' tab and choose either the 'Medium' or 'Low' option.3. Click on the 'Tools' option and then scroll over the 'Marco' option. Click on the 'Record New Macro' button.4. Enter a name for your macro in the 'Macro name' box. The first character in the name must be a letter and not a number.5. Choose where you want to store the macro...
How to Merge Sheets in Excel 2007
1. Click the Windows 'Start' button. Type 'excel' in the search text box. Press 'Enter' to open the Excel software.2. Press 'Ctrl' and 'O.' In the dialog window that opens, double-click the XLSX file that contains multiple sheets you want to combine.3. Click the 'DigDB' menu item at the top, then click the 'Table' sub-menu item, then click 'Append Tables in this File.' A configuration window opens.4. Click each sheet you want to combine, and click the 'Add' button. If you want to combine all sheets together, select 'All Sheets,' and then click 'Add.'5. Click 'OK' to combine the spreadsheets....
How to Convert Date to Day of Week in Excel
Convert Replace Date with Day of Week1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format.2. Left click with the mouse to select the column or row that contains the range of date data.3. Select 'Format' from the menu.4. Select 'Cells' from the drop-down list.5. Select the 'Number' tab on the 'Format Cells' pop-up window.6. Select 'Custom' in the 'Category' list.7. Enter 'dddd' in the 'Type'...
How to Import Excel Spreadsheets Into Access
1. Save your spreadsheet data in Excel using the 'File,' 'Save' menu. Although Access recognizes a range of alternate formats, including dBASE and text delimited, it is best to use the native Excel format for your saved file. Access is configured to import Excel files directly and provides options for continuing to work with the data in both products after the import process.2. Start Microsoft Access and open the database into which you want to import your Excel data. You can create a new, empty database in Access by clicking 'File,' 'New' and selecting the 'Blank Database' option. Provide...
How to Add Multiple Sheets to a Workbook in Excel 2007
1. Open Excel 2007. At the bottom of the page, there are tabs you can use to select which sheet you want to view. These tabs are labeled 'Sheet 1,' 'Sheet 2,' and so on.2. Right-click over one of the sheet tabs to insert a sheet. Click 'Insert,' select 'Worksheet,' and click 'OK.' Look down at the tabs to verify that a sheet was inserted. Repeat this to insert multiple sheets.3. Hold down the 'Shift' key. Drag your mouse over the spreadsheet tabs to highlight the number of sheets you want to insert. You can insert multiple sheets by highlighting more than one tab.4. Click 'Insert,' select...
Monday, February 27, 2012
How to Create Forms in Excel 2007
1. Open an Excel 2007 spreadsheet. Type in column headers in the top row for each column you will enter data under.2. Click and drag the mouse over the column headers only to select them. Do not highlight any other columns or select the entire row, including the blank cells to the right of the column headers or else Excel will not know which columns to use to create the form.3. Click on the arrow button beside the Quick Access Toolbar at the top of the screen, then click 'More Commands' from the drop-down list that appears.4. Click the 'Choose commands from' drop-down menu and click 'All...
How to Show Formulas in Excel 2003
1. Click on the square in the upper-left corner of the Excel 2003 spreadsheet to select all cells.2. Press and hold the 'Ctrl' key on your keyboard after all the spreadsheet cells are selected.3. Press '`,' the grave accent key, located in the upper-left hand corner of your keyboard below 'Esc.'4. Release both butto...
Step
1. Start Microsoft Excel. Double click on the Desktop icon and Excel will open with a blank spreadsheet. If you cannot locate the Desktop icon, click on the 'Start' or 'Microsoft' button, and then click on 'All Programs.' Click on 'Microsoft Office,' and then click on 'Microsoft Excel.'2. Click on cell 'A1' and enter a name for the column. For example, if you are making a budget, then you might type 'Income.' Data in Excel are entered in columns for easy formatting and insertion of formulas and functions.3. Enter your other column headings in row 1. In the budget sheet example, click on...
How to Fix Broken Links in Excel 2007
1. Close all the workbooks that are open.2. Open the workbook with the broken link.3. Click 'Options' on the trust bar, and then click 'Enable this Conten...
Sunday, February 26, 2012
How to Convert Date to String in Excel
1. Open Microsoft Excel, then open the document where you want to convert a date to string.2. Type '=TEXT(' in the cell where you want to place the string.3. Click on the cell with the date. This should put the cell reference (like A1) in the initial cell.4. Type 0, 'MM/DD/YYYY') after the cell reference. Exchange 'MM/DD/YYYY' with the format of your date. In this case MM is the month, DD is the date, and YYYY is the year. Press enter and the cell should have a text (string) version of the da...
How to Convert Text to Upper Case in Excel 2007
1. Convert text in a cell, say cell A1, into fully capitalized text in another cell, say B1, with the UPPER function. For example, type in B1 the following: =UPPER(A1). You can even copy the contents of B1 into A1 by right-clicking the mouse on B1, selecting Copy, then right-clicking A1, and selecting “Paste Special” and “Value.” (If you insert the formula instead, the reference will be circular and you’ll get an error.)2. Convert the returned value of a formula using the following syntax: =UPPER(function). For example, if the original function in a cell is =IF(A1>B1,C1D1,A1), then change...
How to Make a Cell Reference Absolute in Excel
1. Start Microsoft Excel 2007 and open an existing workbook from your files or start a new blank workbook.2. Click to select the cell where you would like to enter a formula where the cell reference is absolute.3. Begin typing the formula into the cell. Type a dollar sign right before the column reference in the formula to make it absolute. For instance, you may type '$A1' into a formula. This means that wherever you copy and paste this formula, Excel will always keep the reference in column A.4. Type a dollar sign right before the row number to make the row number absolute. For example,...
Saturday, February 25, 2012
How to Nest Subtotals in Excel 2003
1. Click and drag your mouse to highlight the three columns---two columns of headings and the third of values.2. Click the toolbar's 'sort' button. The Sort window will open.3. Sort the table first by the first column and then by the second column.4. Click 'Subtotals' from the Data menu.5. Click the down-arrow in the 'At each change in' drop-down box. Click the first column's heading.6. Click 'Sum' in the 'Use function' box.7. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.8. Click 'OK.'9. Click and drag your mouse to highlight...
How to Change the X
1. Open Excel and navigate to the chart for which you wish to change the x-axis range.2. Move your cursor so that it is directly over the x-axis of the chart, and right-click with your mouse.3. Select 'Format Axis...' from the menu that appears. The Format Axis dialog box will open.4. Click so that 'Fixed' is selected instead of 'Automatic' for both the Minimum and Maximum under Axis Options.5. Select the values you wish to use as the minimum and maximum for the x-axis and enter them in the appropriate boxes. Click 'Close' to fini...
How to Add Headers and Footers to Excel Documents
1. Start Microsoft Excel and open the file you want to change.2. Open the View menu and select Header and Footer option.3. In the Page Setup dialog box, click the Custom Header or Custom Footer button.4. Click inside the Left section, Center section or the Right section.5. Click the A icon to change the font styles.6. Click the # icon to insert page numbers.7. Click the icon to insert the number of pages in the document (so if you wanted the header or footer to read 'Page X of 25,' you'd click the # icon followed by the icon).8. Click the date, time, file name or tab icon...
How To Copy Name Ranges in Excel 2003 to Another Workbook
1. Open the workbook you will be copying the range names from. If you do not already have the names set, define the range names. To do so, click on the 'Insert' menu, select 'Name' and then choose 'Define' from the list. Find the names in the workbook box.2. Locate the name of the second workbook. Make sure that workbook is saved as a Macro-enabled Workbook. The file extension should be .xlsm or .lsm.3. Hold down the 'Alt' and 'F11' keys. The Visual Basic Editor will open. Click 'Module' in the 'Insert' menu. The module window will pop-up.4. Type the following code into the window. Change...
How to Merge Information From Several Excel Spreadsheets
1. Open the workbook with the spreadsheets you want to consolidate. If you the spreadsheet is not in the workbook, create a duplicate spreadsheet within the workbook by doing a copy and paste..2. Chose Data and then Consolidate. Excel displays the Consolidate dialog box.3. Specify the range with the Reference box. After you specify the range (or the area of cells on the spreadsheet you want to consolidate) click Add. The reference will appear in the All References list. Continue to add ranges until you have added them all.4. Click on the Create Links to Source Data box if you want the...
How to Create a Bar Graph in Excel 2003
1. Log on to your computer and pull up your Excel worksheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.2. Arrange desired data in columns or rows. You only need to enter the numbers at this time.3. Highlight the cells containing desired data. To select separated data, highlight the first piece. Then hold 'Ctrl' while clicking on the remaining data.4. Select 'Chart' from the 'Insert' menu or click on the 'Chart Wizard' icon on the toolbar.5. Choose your chart type. Click 'Next'. Confirm the...
Friday, February 24, 2012
How to Hide Unhide Cells in Excel
Hide Cells1. Select the cells you want to hide.2. Click the 'Format' tab and then click 'Format Cells.'3. Click the 'Number' tab.4. Click 'Custom' from the Category List.5. Type three semicolons ';;;' into the 'Type' text box and then click 'OK.' The cell data will be hidden.Unhide Cells6. Reopen the Format Cells dialog box.7. Delete the semicolons in the Type box.8. Click 'O...
How do I Convert Exel 2007 to 2003?
1. Open Excel 2007 and open an existing Excel 2007 spreadsheet. Select the 'Office' button then 'Open.' Browse your computer for files saved in the .xlsx format. Select the file and click 'Open.' The file with the new Excel 2007 naming format will open.2. Select the 'Office' button then select 'Save As.' Select the format of 'Excel 97-2003.' If any conflicts appear, the Compatibility Checker will display any losses of fidelity and how many times this has occurred. Click 'Find' to see where these instances occur or 'Help' to see how to work around these issues.3. Click 'Continue' once you...
How to Remove Lines Around Each Cell in Excel 2003
1. Open Microsoft Excel 2003.2. Click 'Tools' in the top menu bar and select 'Options.'3. Click the 'View' tab in the Options window.4. Uncheck 'Gridlines' in the Windows Options section.5. Click 'OK' to apply your changes and remove the gridlin...
How to Print Multiple Excel Spreadsheets on One Page
Changing the Printer Settings1. Select 'Print' from your Microsoft Excel's File menu to display the Print dialog box.2. Choose the option for 'Entire Workbook' in the dialog box's 'Print What' area.3. Click the 'Properties' button for Excel to display its 'Properties' dialog box for your printer, along with the selected 'Layout' tab.4. Set the 'Pages Per Sheet' control depending on how many spreadsheets you want printed on the page. Click 'OK' to close the 'Properties' dialog box.Changing the Page Setup5. Select the 'Page Layout' tab in the File menu and locate the 'Page Setup' group.6....
How to Convert a Julian Date in Excel
1. Click on the cell were you would like your output data to start. For example, if you have a list of Julian dates in column A, you might want your regular dates to start on column B, so click on cell B1.2. Enter the following formula into the cell. =('1/1/'(IF(LEFT(A2,2)*13. Copy the formula into as many cells as you want dates outputted. For example, if you have Julian dates in cells A1 through A50, copy the formula 50 times from cells B1 to B50.4. Highlight the cells you created in Step 3.5. Right-click on the highlighted cells and choose 'Format Cells.'6. Click on 'Date' from...
Thursday, February 23, 2012
How to Code a VBA Excel 2007 Macro Consolidation
1. Go to the 'View' tab in the Microsoft Excel toolbar. Click on the 'Macros' button and select 'View Macros' Enter a new macro name for the master macro such as 'All' and click on the 'Create' button to launch the Visual Basic editor.2. Go to the second line in between the macro name and end of the macro. In our example, it would appear as 'Sub All()' and 'End Sub'. Type 'Call' followed by each of the individual macro names you would like to run in preferential order. For example, we could have a macro named 'Links' that goes through a range of links and copies and pastes data from linked...
How to Create a Summary Chart in Excel
1. Open Microsoft Excel and create a new workbook by pressing 'CNTL-N.' Press 'CNTL-S' to save the workbook, and name it 'SummaryChartOfBookSalesByPublisher.xls' when prompted.2. Create several rows of sample data representing the book sales of different publishers, across different types of books. Type or paste the following data into any worksheet in the workbook. Wherever you see a comma in this data, press the 'Tab' key, which will place each item in its own cell.Publisher,Genre,Distributor,SalesDolphin Pub. pub,romance,Amazon,$456Dolphin Pub. pub,romance,Dynamic Dist. dist,$65Dolphin...
How to Import Export Excel Text Into Access
Import in Microsoft Access1. Open the Microsoft Access application on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.2. Click on the 'Open' option and locate the Access database that you want to import and export data into. Select the database and then click on the 'Open' button.3. Click on the 'File' or 'Microsoft Office' button again. Move your mouse over the 'Get External Data' option and then click on the 'Import' option. The Import dialog box will then appear on the screen.4. Select the 'Microsoft Excel' option from the 'Files...
How to Make a Timeline on Microsoft PowerPoint
1. Click on the 'Insert' tab, then click on the 'SmartArt' button in the 'Illustrations' area. Click on 'Process' and select one of the 'Timeline' layouts available. Add text to your timeline by clicking any place that says '[Text]' and typing or pasting in the text you want.2. Add items to your timeline. Click on the timeline to select it, click on the 'Design' tab, click on 'SmartArt Tools' and click on 'Add Shape.' Delete an unwanted entry by clicking on it and pressing the 'Delete' key on your keyboard. Move an entry by clicking and dragging it to the location you desire.3. Change the...
How do I Add Information on Excel Worksheets to a Summary Sheet?
Excel 20031. Open Microsoft Excel and open the sheet you wish to summarize. Click on the 'Tools' menu and choose 'Scenario.'2. Click on the 'Summary' option and choose 'Scenario Summary.' Go to the 'Result cells' tab.3. Enter the cells you wish to summarize. You can simply place your cursor on the first cell and drag it to the last cell to create your range.Excel 20074. Open Microsoft Excel and find the spreadsheet you want to summarize. Go to the 'Data' tab.5. Go to the 'Data Tools' group and then the 'What-If Analysis-Scenario Manager.' Click the 'Summary' button.6. Choose the 'Scenario...
How to Protect Sales in Excel 2007
Protect an Entire Worksheet1. Click on the bottom tab to select the worksheet that you wish to protect.2. Click on the 'Review' tab on the menu ribbon.3. Click 'Protect Sheet' in the 'Changes' panel of the 'Review' tab.4. Enter a password if you want the worksheet to be password-protected. If not, leave the 'Password to unprotect sheet' box blank.5. Click the 'OK' button and the entire worksheet will be protected from future changes.Protect Cells Within a Worksheet6. Click on the bottom tab of the worksheet you want to protect.7. Click-and-drag the cursor to select the cells containing...
Wednesday, February 22, 2012
How to Use Freeze Panes in Excel
Freeze the Top Row or Left Column1. Open the Excel spreadsheet.2. Navigate to the 'View' tab on the top menu.3. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.4. Select the 'Freeze Top Row' option to freeze the top row.5. Select the 'Freeze Left Column' or 'Freeze First Column' option to freeze the left column.6. Freeze the top row by using the keyboard and sequentially pressing the keys 'ALT, W, F, R.' Ignore Steps 3 through 7 if using this choice.7. Freeze the left column using the keyboard by sequentially pressing the keys 'ALT, W, F, C.' Ignore Steps...
How to Do a Paired Two Sample T
1. Enter your first set of data in column A. Enter your second set of data in column B. Make sure you only put one data result in each cell.2. In cell C1 type '=TTEST(.' Do not hit 'Enter.'3. Highlight your first data set and type ',.'4. Highlight your second data set and type ',.'5. Type '2,1).' Press 'Enter.' You formula in the cell should look like '=TTEST(A1:A5,B1:B5,2,1).' The cell returns the t Test resu...
How to Create a Command Button in Microsoft Excel
1. Open the Excel worksheet in which you will be installing the command button.2. Click the Microsoft Office logo located in the top left-hand corner of the screen and choose 'Excel Options.' Check 'Show Developer Tab' under the Top Options tab, and click 'OK.'3. Click the 'Developer' tab on the main menu ribbon. Open the 'Insert' drop-down menu, and click 'Command Button.' Select the area of the worksheet where you want to insert the command button, and click on it. The command button will appear.4. Click the 'Developer' tab on the main menu ribbon and select 'Design Mode.'5. Right-click...
How to Put Error Bars on Points in Excel
Office 20031. Right-click the data series (the points on the graph) and click 'Format Data Series.'2. Click the tabs at the top of the window that read 'X Error Bars' or 'Y Error Bars' as necessary.3. Select the error bar style under 'Display' and set the error amount as a fixed value, a percentage or a standard deviation.4. Click 'OK' to apply the error bars.Office 20075. Click on the graph and the 'Chart Tools' toolbar will appear at the top of the screen.6. Select the 'Layout' tab and click the 'Error Bars' button found on the right side.7. Click 'More Error Bars Options' and...
How to Recover MS Excel Files After Closing
Enable AutoRecover1. Open the Excel program. A new worksheet appears.2. Click the 'File' tab. A list of basic commands appears.3. Click 'Options' under 'Help.'4. Click 'Save.' A dialog box opens.5. Select the check box for 'Save AutoRecover Information Every X Minutes.'6. Type the number of minutes, such as every 10 minutes, for the frequency of saves.7. Select the check box for 'Keep the Last Autosaved Version if I Close Without Saving.' The Excel files will save at least one version.View the Document Recovery Pane8. Open the Excel file. A Document Recovery task pane opens next...
How to Change Column Width in Microsoft Excel 2003
1. Select the column that you wish to resize. To select a column, left-click on the column letter, located at the top of the column in the spreadsheet and labeled alphabetically from left to right. Once you click on the column letter it will highlight the entire column.2. Resize the column using the drag arrows on the column head. Hold the cursor over the left or right side of the column header . An arrow will appear. Simply left-click and you can drag the column to the desired width.3. Set the column width using the column properties menu. Once you have highlighted the column, right-click...
How to Create Sequential Numbers on an Excel Document With Software
1. Type the first number of your sequence into the cell corresponding to the top position of your list. For example, if you wanted to start your numbering sequence in the top left corner of the spreadsheet, you could type '1' into cell 'A1.'2. Type the second number of your sequence into the cell corresponding to the second position of your list. For example, you could type '2' into cell 'A2.' Add more numbers to your sequence if the numbering pattern isn't recognizable after two numbers.3. Click on the top cell in your sequence and drag your mouse to select the rest of your starting values.4....
How to Rename Columns in Microsoft Excel Queries
Customize Column Labels1. Open Microsoft Excel.2. Click on the column you wish to rename to highlight it.3. Click 'Insert' on the toolbar at the top of the screen to open a drop-down menu.4. Click 'Name.'5. Click 'Define.'6. Type the name you wish to give the column.7. Click 'OK' to save the new label. Repeat these steps for each column you wish to rename.Rename Column Labels With Numbers8. Open Microsoft Excel.9. Click 'Tools' to open a drop-down menu.10. Click 'Options.' A new window will open.11. Click on the 'General' tab to display a list of options.12. Uncheck the...
Tuesday, February 21, 2012
How to Delete Commas From Excel 2007
1. Double-click on the cell that you want to edit.2. Position the cursor directly behind the comma that you want to delete. Click the mouse once in this spot.3. Click the 'Backspace' button on the keyboard to remove the comma. If there are multiple commas in the cell that you want to delete, use the arrow keys to move over to them and hit 'Backspace' to delete them.4. Click 'Enter' when you are finished making changes to the cell.5. Repeat the process for any additional cells you wish to ed...
Monday, February 20, 2012
How to Insert Copy a Formula in Excel 2007
1. Open Excel and the worksheet in question. Position the cursor in the cell in which you wish to insert the formula.2. Click on the Formulas tab at the top of the Excel window. Choose the proper category for the formula you will be using, and then select the formula that you wish to insert from the list that appears.3. Enter the cell references as prompted in the Function Arguments dialog box that appears.4. Copy any of your existing formulas by first selecting the cell that contains the formula you wish to copy.5. Click on the Home tab at the top of the Excel window, and select the...
How to Enable a Ribbon Customizer
1. Launch the Microsoft Office application for which you want to customize the Ribbon interface.2. Right click anywhere on the Office Ribbon, then click 'Customize the Ribbon.'3. Click the 'New Tab' button at the bottom of the right side of the 'Options' screen. The application creates a new section labeled 'My Tab' in the right pane of the window.4. Drag and drop desired command functions and options for the Ribbon Tab from the drop-down menu on the left side of the 'Options' screen into the 'My Tab' section of the right-side window pane.5. Click the 'OK' button at the bottom of the...
How to Make a Time Line in Excel 2003
1. Open a blank worksheet in Microsoft Excel. Click 'View' on the toolbar and click 'Header and Footer'. The 'Page Setup' box will open. Click the 'Custom Header' button and enter the header you want printed on your page. Format it using the 'Font' button. Click 'OK' to close the 'Header' box, but don't close the 'Page Setup' box.2. Click the 'Page' tab of the 'Page Setup' box. Under 'Orientation', select the 'Landscape' button. Click 'OK' to close the 'Page Setup' box.3. Select all of the cells on your current page in row 7 using your mouse. Click on the 'Fill Color' button on your toolbar...
How to Do a Frequency Distribution in Excel 2007
1. Click the 'Data' tab and then click 'Data Analysis.'2. Click 'Histogram' and then click 'OK.'3. Type 'A1:A2' into the 'Input Range' box, where A1 and A2 represent the actual range of cells on your worksheet where your input data is. For example, if your range of data is in A1 to A10, type 'A1:A10'4. Type 'B1:B2' into the 'Bin Range' box where B1 and B2 represent the range of cells where your bin data is located. Bins represent the groups you want your data to fall into. For example, if you want your data to fall into bins of 1-10 and 11-20 and those values are located in cells C1 and...
How to Turn on the Developer Tab in Excel 2007
1. Open the Microsoft Excel 2007 file for which you want to open the Developer tab.2. Click on the 'Microsoft Office' button in the top-right corner of the application.3. Click on the 'Excel Options.'4. Click on the 'Popular' option and then click on the box next to the 'Show Developer Tab in the Ribbon' field so that it's selected.5. Close the dialog box and then click on the Developer tab in the ribbon at the top of the application. You now have the Developer options available to y...
Sunday, February 19, 2012
How to Freeze (Lock) Column Headings in Microsoft Excel
1. Launch Excel and open your spreadsheet.2. Select the 'View' tab at the top of the Excel window.3. Click the 'Freeze Panes' button. This will open a drop-down menu.4. Click 'Freeze Top Ro...
How to Span Columns in MS Excel
1. Highlight the cells you want to merge by holding 'Shift' and moving the arrow keys to highlight the data.2. Right-click on a highlighted cells and select 'Format Cells.'3. Click 'Alignment.'4. Check the box next to 'Merge Cell...
Location of Excel 2003 Commands in Excel 2007
1. Click the Microsoft Interactive: Excel 2003 to Excel 2007 command reference guide.2. Click 'Start the Guide.'3. Click 'Start.'4. Click the Excel 2003 command that you want to find in Excel 2007. For example, click the 'Autosum' command. The program will open a pop-up window showing you where you can find the command in Excel 20...
How to Use Excel's YEARFRAC Function
1. Install the Analysis ToolPak, if needed. If YEARFRAC returns the #NAME? error value, select Add-Ins from the Tools menu. Mark the checkbox next to Analysis ToolPak and click on the OK button to install it.2. Learn the syntax of YEARFRAC. It is YEARFRAC(start_date, end_date[, basis]) where start_date is the first date and end_date is the last date of the desired date range. Basis is an optional argument that specifies the method to use for counting the days.3. Enter start_date and end_date using the DATE function or some other function that returns a date to prevent the problems that can...
How to Remove the Contribute Toolbar
Disabling the Contribute toolbar in Internet Explorer and Firefox1. Click the \'Tools\' menu and click \'Manage Add-ons\' (Internet Explorer) or \'Add-ons\' (Firefox). A new window launches.2. Select \'Adobe Toolbar\' and select \'Disable\' in the settings section if you are using Internet Explorer. Select \'Disable\' in he extensions section if you are using Firefox. Select \'ContributeBHO Class\' if you are using Contribute CS3, and set it to \'Disable\' (Internet Explorer) or click the \'Disable\' button (Firefox). Contribute disables the contributeplugin.dll file.3. Click \'OK\' to save...
How to Insert a Static Date in Excel
1. Open Microsoft Excel 2007, and start a new spreadsheet that you would like to insert a static date into or open an existing spreadsheet from your files into which you want to insert a static date.2. Click to select the cell to which you want to insert the static date. The cell will then be surrounded by a thick, black outline indicating it is ready for you to insert the static date into it.3. Use your keyboard to enter the shortcut of 'CTRL' ';' into the selected cell. Both the 'CTRL' key and the semicolon key must be pressed at the same time. This keyboard shortcut will tell Excel...
Saturday, February 18, 2012
How to Group and Outline Excel Data
1. Open your Excel spreadsheet. Organize the data to be outlined. Make sure the fields you want to group are adjacent to each other. Insert summary rows or columns beneath or beside the fields that you will be outlining. Summary rows or columns should contain formulas to average or sum up the fields to be outlined.2. Highlight the series of columns or rows you want to group. Select rows or columns by clicking on the gray letter or number field at the side or top of the spreadsheet.3. Choose 'Data' from the menu. Choose 'Group and outline.' Select 'Group.' You'll see a gray box on the left...
How to Make a Graph for Density in Excel With Microsoft
1. Select the two Excel spreadsheet columns that contain density graph data, and then click the 'Insert' tab on the Excel main menu.2. Set the Excel graph to a “Scatter with only Markers” type by selecting this option from the drop-down menu on the 'Charts: Scatter' section of the Excel Ribbon menu. This is the best option for a density graph, not only because you are comparing pairs of values, but also because each pair represents a separate measurement.3. Right-click anywhere on the graph, and choose “Select Data” from the resulting pop-up window. The 'Select Data Source' window will open,...
Thursday, February 16, 2012
How to Use Multiple Data Sources in One PivotTable
1. Data with the same headers can be stacked in longer columns.Prepare the data to consolidate by making sure the source spreadsheets all have the same column headers and row names. This will enable you to place the correct data in the correct column during the consolidation (see Resources).2. The Pivot and Chart Wizard can expedite tasks.Click a blank cell in the Excel spreadsheet where you will create the consolidation.Hold down 'Alt' 'D' 'P' to open the 'PivotTable and PivotChart Wizard.' If you plan to use this wizard often you can add it to the 'QuickAccess' toolbar by clicking the...
How to Delete Duplicates in Excel
1. Place your list into Excel. Whether you type it or import it from another location, make sure it is in Excel and each entry is in its own row. This will let the program automatically delete duplicate entries.2. Click the 'Data' tab on the top of the screen. This will bring up the 'Data' menu. Look for the 'Data' tools section of the toolbar.3. Select the range of cells containing duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data.4. Click on the 'Remove Duplicates' button. A dialog box will come up asking which column you want...
How to Add a Trend Line on an Excel Spreadsheet
Excel 20031. Create a chart from the data with which you want to make a trend line. Press the 'F11' key to create a chart using your default chart type, or click the 'Chart Wizard' button on the 'Standard' toolbar and follow the steps to create a basic chart.2. Select the data series in the chart from which you want to create a trend line.3. Go to the 'Chart' menu and select 'Add Trendline' The 'Add Trendline' dialog box will open.4. Go to the 'Type' tab. Select the type of trend line you want to use and click 'OK.'Excel 2007 or 20105. Open the Excel spreadsheet with the data from which...
How to Master Excel 2007 Pivot Tables
1. Open or create an Excel spreadsheet. The sheet needs to contain 20 or more rows of data plus column headings, which are required to create pivot tables.2. Select the data you want to include in your pivot table. Either highlight the data before creating the pivot table or select the data during the pivot table creation process (See Step 3.)3. Select the “Tables” group and choose “Insert.”4. Select “PivotTable” and select “PivotTable” again.5. Follow the prompts to create a pivot table. If you want to use data from an external source, choose “Use an external data source.”The default...
How to Save a Workbook With a Password in Excel
Password Protect Workbook1. Double-click on the Excel document to open it up.2. Click on the 'File' tab at the top of the screen. Select 'Info' from the menu that covers the left side of the window.3. Click on the 'Protect Workbook' button in the 'Permissions' area of the screen. Choose 'Encrypt with password' from the list of options. Enter in your desired password and click 'OK.' Enter the password again to confirm it, and press 'OK.'Protect Document Elements4. Double-click on the Excel file to open it up. Select the worksheet that you want to protect from the list of worksheets at the...
How to Move a Column in Microsoft Excel
1. Open the Excel workbook to the worksheet containing the column you want to move. If you are moving it to a different workbook, open the second workbook to the target worksheet as well.2. Click in the column header for the column you want to move. Use the built-in Excel column header with the pre-assigned letter, not a user-entered column header. Clicking the built-in header will select the entire column.3. Press the 'Ctrl' and 'X' keys simultaneously to cut the column from its original location.4. Click the column header to the right of where you want the column inserted, whether it...
Wednesday, February 15, 2012
How to Create a Curved Line Graph in Excel or Word
Microsoft Excel1. Open Microsoft Excel. Highlight the data you want to insert in a graph.2. Click the 'Insert' tab from the menu at the top of the page. You can insert pictures, clips and graphs here.3. Look to the 'Charts' section on the drop-down menu. Select 'Line' and then choose either a 2-D or a 3-D line chart depending on your preferences. Your data will appear in a line graph.4. Right-click on the line in the graph and select 'Format Data Series.' This is where you will choose options to make the line in the graph curved.5. Select 'Marker Line Style' from the options at...
How to Zip an Excel File
1. Open 'My Documents' in Microsoft Windows XP or earlier. If you are running Windows Vista, open 'Documents.' Both of these are usually found on the 'Start' menu.2. Navigate through the folders until you locate the Excel file you want to zip.3. Right-click on the file icon or name you want to zip and select 'Send To' from the pop up menu that appears.4. Select 'Compressed (zipped) folder' from the sub-menu. A new zipped file will be created in the same folder. It will keep the same file name, but with a '.zip' extension, as your Excel file. This is the default, but you can change it just...
How to Use Excel for Correlation of Data From Three Sensors
1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.2. In column A, in cells A1 to A10, enter the following numbers:0.330.050.770.900.250.660.300.120.050.853. In column B, in cells B1 to B10, enter the following numbers:0.540.180.290.010.600.050.010.890.020.364. In column C, in cells C1 to C10, enter these numbers:0.320.950.500.520.910.750.830.010.110.155. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then...
How to Copy and Paste Multiple Cell Contents in Microsoft Excel 2003
1. Highlight the cells that you wish to copy. Simply left-click on a cell and hold the mouse button as you drag the cursor over the multiple cells you wish to copy. Then let go of the mouse button and the cells will stay highlighted.2. Copy the cells. To do this, scroll to the “Edit” tab on the command bar and select “Copy.”3. Highlight the cells you wish to paste into. Find the group of cells that you wish to paste into; they must number the same as the cells you copied. Highlight the cells that you wish to paste into by following the directions in Step 1.4. Paste into the cells. To paste...
Tuesday, February 14, 2012
How to Create a Mirrored Image in Excel
1. Select or create the image you wish to manipulate in just about any software program you want. The image can be simple or intricate, homemade or an object that you found in an obscure photo album.2. Click the image so that the entire object is highlighted. This will ensure that whole image will be copied to Excel.3. Right click and select 'Copy.' Open or go to the Excel window and click on the cell where you want the image to be.4. Pick 'View' from the top of the Excel window. If there is not a check mark next to the Drawing toolbar option, click 'View,' 'Toolbars' and 'Drawing.' A...
How to Construct a Categorical Frequency Table in Excel 2007
1. Enter your data into one column. For example, if you have a list of 10 house prices that you want to categorize, enter them into cells A1 to A10.2. Enter your bin values into the next column. In our example, you would enter your bin values (for example, 100,000, 200,000, 300,000) into column 2. Bin values are optional: If you do not enter values for bins, Excel will choose bins for you.3. Click on 'Data Analysis' from the 'Data' tab.4. Click on 'histogram' from the list box.5. Enter the range for your chart data into the 'Input Range' box. For example, enter 'A1:A10.'6. Enter the...
How to Change Decimal Formatting Placement Within Cells in Microsoft Excel 2003
1. Open and access your spreadsheet file by clicking on the “File” tab and scrolling to “Open.”2. Activate the cell or cells in which you wish to change the decimal formatting placement. To activate the cells that you wish to change, either left-click on a single cell or left-click and hold as you drag the cursor over multiple cells to highlight them.3. Open the cell properties box. To do this, right-click on the cell or cells that you wish to change the properties for. A sub-menu will open. Select “Format Cells.”4. Change the cell decimal formatting placement. Under the cells properties...
Monday, February 13, 2012
How to Calculate a Weighted Average in Excel 2007
1. Enter your two sets of values that you wish to find the weighed average for into your spreadsheet, separating them into two columns. For the sake of this example, assume that the values in the first column stretch from A1 to A5 and the values in column B stretch from B1 to B5.2. Type the following formula into a blank cell, cell C1, without quotes: '=SUM(B1:B5)' This formula will calculate the sum of the second column of numbers.3. Type the following formula into another blank cell, cell C2, without quotes: '=SUMPRODUCT(A1:A5,B1:B5)' This formula calculates the total amount paid into...
How to Use Percentages in MS Excel
1. Use MS Excel to display decimals as percentages.Format a cell to display a percentage. In Excel, type a decimal value into cell A1, such as 0.3. Press 'Enter.' On the 'Home' tab of the ribbon, in the 'Number' section, click '%' to display 30%.'2. Calculate percentages by dividing an amount into the total.Calculate a percentage. If you have 100 boxes and 12 of them are red, find the percentage of red boxes by dividing the amount,12, by the total, 100. In Excel, enter the following:Cell A1: 12Cell A2: 100Cell A3: =A1/A2In cell A3, press 'Enter' to view the result, 0.12. Format cell A3 as...
How to Get to VBA in Excel 2007
1. Click the Microsoft Office icon at the top-left corner of the Excel spreadsheet. Select the 'Excel Options' button at the bottom of the drop-down menu.2. Navigate to the 'Popular' tab at the left side of the window. Locate the 'Top Options For Working With Excel' heading.3. Check the check box labeled 'Show Developer Tab In The Ribbon' underneath the 'Top Options' heading. Click 'OK.'4. Click the 'Developer' tab at the top of the screen. Select the 'Visual Basic' option underneath the Microsoft Office button to bring up the VBA screen.5. Double-click the 'Sheet1' entry at the left...
How to Center a Worksheet Horizontally in Excel 2007
1. Select the Office Button and click 'Open.' Double-click the file you want to modify in the 'Open' dialog box. It loads on your page.2. Select the worksheet you want to align in the workbook. To center each worksheet in your file, right-click on any worksheet tab (at the bottom of your page) and choose 'Select All Sheets' from the pop-up list.3. Select the 'Page Layout' tab. Click the arrow on the right of the 'Page Setup' group.4. Select the 'Margins' tab in the 'Page Setup' dialog box.5. Go to the 'Center on page' section and check 'Horizontally.' Click 'O...
Sunday, February 12, 2012
How to Put a Picture in the Background on an Excel Spreadsheet
Microsoft Excel 20031. Open your spreadsheet. Click on the tab labeled 'Format.'2. Scroll over 'Sheet,' and then select 'Background.'3. Browse through your pictures to find the one you want to add. Click on the file name, then click 'Insert.'Microsoft Excel 20074. Open your spreadsheet. Click on 'Layout' in the top menu bar.5. Click 'Background,' and then browse through your pictures.6. Select the picture you want to set as the background and click 'Inser...
How to Create a Drop
1. Click in the cell that you want to have a drop-down 'pick' list. Select the 'Data' tab. In the Data Tools group, click 'Data Validation.' The Data Validation dialog box appears.2. Select the 'Settings' tab. In the Allow box, select 'List.'3. If you have the items typed in a range (one word per cell, either across or down) and in the same worksheet, then enter the range in the Source box (or click the icon to select the range from the worksheet). Alternatively, type the items in the Source box, separating them by a comma. When you are finished, click 'OK.'4. Navigate to your worksheet....
How to Create a Run Chart
1. Enter column headers. In Excel, put 'Date' in cell A1, the name of the variable you are tracking (such as weight/value) in cell B1 and 'median' in cell C1.2. Add dates. Add the dates that you recorded data on in column A. For example, you might have 10/1, 10/2, 10/3, 10/7, 10/8. Usually, though, you will have a longer series.3. Add the values. Add the values you observed in column B. For example, you might have 150, 151, 152, 151, 150.4. Find the median. At the bottom of column B, click 'Formulas,' then 'More functions,' then 'Statistical' and 'Median.' Copy this number into...
Saturday, February 11, 2012
How to Make a Loan Amortization Table in Excel
1. Install the Amortization Schedule for Excel product by Vertex42. This is a free download through CNET. You can quickly build a table that reflects all the different parameters of a loan. While most loans are based on a set payment schedule, this Vertex42 template shows how occasional changes to the payments affects the overall loan. If a payment is missed, or the occasional payment is increased, this table reflects the consequences of these actions on the final payback amount. Simply download the file and open it in Excel, and the interface is straightforward from there. No prior financial...
How to Default to Excel 2003 and Not 2007
1. Close any open spreadsheet programs on your computer.2. Click 'Start' in the lower left corner and then 'Documents' to open the directory containing your spreadsheet files.3. Find a spreadsheet file that you want to associate with Excel 2003. The file will have a spreadsheet extension such as xls, csv or xlsx. Right-click the file name and select 'Properties' to view the file extension if it is not otherwise visible.4. Right-click the file name and scroll to 'Open with' in the dialog box that appears. Then click 'Choose default program' to open another dialog box.5. Click the Excel...
How to Create a Break Even Graph in Excel
1. In cell A1, type 'Fixed Cost,' and in B1 enter the dollar amount of your fixed costs. For example, the supplier of mylar balloons requires that you pay $100 membership fee to be a buyer, and you are charged that amount no matter how many balloons you buy. In that case you would type '100' into B1.2. In cell A2, type 'Cost per Unit,' and in B2 enter the dollar amount of the cost per unit. For example, each balloon cost $1. You would enter '1' into B2.3. In cell A3, type 'Revenue per Unit,' and in B3 enter the dollars amount of the revenue per unit. If you plan to sell your balloons at...
How to Create a Pivot Table to Consolidate Multiple Data Ranges
1. Open the Microsoft Excel application that contains that data you want to create a PivotTable for on your computer. Click on any blank cell within your worksheet.2. Click on the 'Data' option and then click the 'PivotTable and PivotChart Report' option for Excel 2003. Press the 'ALT', 'D' and 'P' keys at the same time for Excel 2007. The PivotTable and PivotChart Wizard will then appear.3. Click on the 'Multiple consolidation ranges' option and then click on the 'Next' button. Click on the 'I will create the page fields' option and then click on the 'Next' button.4. Click on the 'Collapse...
How to Label the Columns of Microsoft Excel 2007 with Letters
Windows version of Excel 2007 or higher1. Open Excel. You do not need to bring up a specific file. Just click to open the program, which will automatically open a blank spreadsheet.2. Select the Office symbol button in the top left corner of Excel to open the menu. At the bottom right, click on the 'Excel Options' button. A new window will appear.3. Click on 'Formulas' from the menu on the left and look mid-page under the heading 'Working with Formulas.' Uncheck the first item ('R1C1 Reference Style'), and you will return to columns labeled with letters. To change back to numbers, simply...
How to Use Vlookup in Excel 2007
1. Open MS Excel 2007. Click on the Windows icon on the top left of the screen. Scroll to and click 'Open.' Search your computer for the file you need to find values on. Click on it to select it and click on the 'Open' button to open the file.2. Make sure there are definite options of things to search for. For example, if you want to list the city of people who contacted your organization, but you only have the cities listed by code, you will use a VLOOKUP to look up which city corresponds to that code.3. Build a table array which lists your options somewhere else on the spreadsheet. In...
How to Find the Percentage of Two Cells in Microsoft Excel
1. Open Microsoft Excel.2. Create your first data point. For the purpose of this example, assume you want to find out what percentage of your total business income was earned in the months of March and April. Enter 'March' (without quotation marks) in cell A1, then press the 'Tab' key and enter '8,500' (again without quotation marks) in cell B1.3. Create your second data point. Press the 'Enter' key to move your cursor to cell A2. Enter 'April,' press the 'Tab' key, then press '8,750' in cell B2.4. Add your data points. Move your cursor to cell B3, directly below your two number values....
Subscribe to:
Posts (Atom)