Excel

Browse » Home » All posts

Tuesday, February 28, 2012

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 a name for the file when prompted. Click 'File,' 'Get External Data,' 'Import' and select the Excel format. Locate your spreadsheet subdirectory and import your spreadsheet into Microsoft Access. If you are using Microsoft Access version 7, you can also click the 'External Data' tab at the top of your screen and select 'Excel' as the format to import the spreadsheet data. You can append the data to an existing table or create a new table for your Excel spreadsheet data.
3. Select the worksheet or range you want to import from the Import wizard and click 'Next.' Select the Excel column headings you want to import as field names and verify that you want to create a new table or append data to an existing table. Click 'Next' to continue. You can change field names, skip fields that you do not want to import and select indexing options, including a primary key for sorting your spreadsheet data in Access. Click 'Finish' when you are satisfied with your selections.
4. Review the Table Analyzer wizard results to verify the structure of the database tables that Access has created from your Excel worksheet. The wizard will divide tables containing duplicate information into separate tables so that each category of data is stored only once. Verify field placement within tables, select the primary key used for sorting and correct any typographical errors prior to completing the import process.

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 'Worksheet,' and click 'OK.' Look down at the tabs to verify that the extra sheets were inserted.

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 Commands.' Scroll down the list and then double-click the 'Form' button. Click 'OK' to close the Excel Options dialog box.
5. Click the 'Form' button that you just added to the Quick Access Toolbar. Excel creates a form with the column headers that you may use to add new records to the spreadsheet.

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 buttons.

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 cell 'B1' and then type 'Amount,' then click on cell 'C1' and type 'Expenses' and then click on cell 'D1' and type 'Amount.' If you have other column heading you want to enter, click on cell 'E1' and continue working right through the worksheet.
4. Enter the data into your columns. Click on cell 'A2' and type your first item. In the budget example, this could be 'Mortgage.' Press the 'Right arrow' key to move the cursor to cell 'B2' and then type the corresponding amount. If your mortgage is '$1,000' then type '$1,000' into cell 'B2.'
5. Add up the numbers in a column by using 'Autosum.' Highlight all of the data in a column that you want to add, click the 'Home' tab and then 'Autosum' to add up the entire column. To highlight data in a column, left click on the top left of the first item and then drag the cursor to the bottom right of the column. You can also click on the column identifier to highlight the entire column. The column identifier is the number or letter at the very top of the column.
6. Format your cells by highlighting them, and then clicking on 'Format Cells.' The pop up window gives you a myriad of choices to align cells, choose borders and fill, and select the type of data you want in cells. In the budget example, you may want to make sure all of your entries are in dollars. Click on the 'Number' tab, click on 'Currency' and then choose the type of currency you would like to have by clicking on the down arrow next to 'Currency.'

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 Content.'

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 date.

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 the cell contents to =UPPER(IF(A1>B1,C1D1,A1)).
3. Perform this capitalization conversion on more than one cell at once as follows. For a row of text, say A1 to A100, type in an empty cell, say B1, the following: =UPPER(A1). Highlight B2 to B100. Click 'Home.' Click 'Fill.' Click 'Down.' The B-column cells now range in contents from =UPPER(A1) to =UPPER(A100). Now just perform a special paste of the column as for the single cell in Step 1.

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, you can type 'A$2' inside of a formula. This means that the cell reference will always refer to the second row, no matter where you copy and paste the formula.
5. Use dollar signs before both the column letter and cell number to make the entire cell reference absolute. If you type, '$A$2' inside of a formula than that formula will always refer to cell 'A2.'
6. Continue typing the rest of your formula. You can include dollar signs into other cell references inside your formula if you need to.
7. Press the 'Enter' key on your keyboard to enter the formula into Microsoft Excel and view your calculation result.

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 the three columns again. Click 'Subtotals' from the Data menu.
10. Click the down-arrow in the 'At each change in' drop-down box. Click the second column's heading.
11. Click 'Sum' in the 'Use function' box.
12. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.
13. Uncheck the 'Replace current subtotals' box.
14. Click 'OK.'

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 finish.

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 to insert those items.
9. Click OK to accept the changes.

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 the reference to book2.lsm or book2.xls to the name of the book you are pasting too.Sub Copy_All_Defined_Names()' Loop through all of the defined names in the active' workbook.For Each x In ActiveWorkbook.Names' Add each defined name from the active workbook to' the target workbook ('Book2.xls' or 'Book2.xlsm').' 'x.value' refers to the cell references the' defined name points to.Workbooks('Book2.xls').Names.Add Name:=x.Name, _RefersTo:=x.ValueNext xEnd Sub
5. Close the Visual Basic Editor Window. Click on the 'X' button or hit 'ATL' and 'F11' again to close the window. The macro will automatically save. When you are back at book 1 or the workbook you are copying from, save the workbook. Click on 'File' and choose 'Save' or 'Save as.' Do not change the name of the file in any way.
6. Open the workbook you are pasting to or start a new workbook. Save the workbook as an Excel Macro-Enabled Workbook. Make sure the name is the same as the name for book2.lsm in the code. The spelling capitalization and extension have to be exact.
7. Open the first workbook again and click on the 'tools' menu. Select 'Macro' and click 'Macros.'
8. Click on 'Copy_All_Defined_Names' from the list of macros. Select the 'Run' option. The names will be automatically pasted in the second workbook.
9. Open Book2 or the paste destination workbook. All the names should be pasted.

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 consolidated data to contain links to the original data. If not, leave blank and click OK to do the consolidation.

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 data range mentally and select if you want your series in rows or columns. Select 'Next'.
6. Enter titles for your chart and axis. Choose the location of your chart. Select 'Finish'.

Friday, February 24, 2012

How to Hide Unhide Cells in Excel

Hide Cells
1. 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 Cells
6. Reopen the Format Cells dialog box.
7. Delete the semicolons in the Type box.
8. Click 'OK.'

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 are satisfied with the adjustment made from the Compatibility Checker. Excel 2007 will save this workbook in the older .xls format. Upon successfully saving this workbook, you have converted it to the 2003 format.

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 gridlines.

How to Print Multiple Excel Spreadsheets on One Page

Changing the Printer Settings
1. 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 Setup
5. Select the 'Page Layout' tab in the File menu and locate the 'Page Setup' group.
6. Click on the small square with an arrow pointing out of the square at the bottom right-hand corner.
7. Look inside the 'Scaling' section in your 'Page Setup' box and choose 'Fit to:' By default, you should see the file set to one page wide and by one page tall.
8. Select how many pages tall or wide you want for your spreadsheet when printed.
9. Click on 'Print Preview' to check the up or scaled version of the spreadsheet when printed. Click on 'Print' if you are satisfied with the preview. Select 'OK' to close your Page Setup box.

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)*1
3. 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 the 'Number' tab. This tells Excel to convert the formula's output into regular calendar dates. Choose the date type (for example, 14 December, 2009 or 14 Dec) and click 'OK.'

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 excel spreadsheets into a master sheet. Another macro called 'Format' sorts the data and adjusts the font type and size.Sub All()Call LinksCall FormatEnd Sub
3. Save the code and go back to the Excel template. Click on the 'View' tab and go to the 'Macros' button. Select the name of the master macro and click on 'Run.' This will run the individual macros that were created in order. In our example, it will run 'Links' first. Once it's completed, it will automatically run 'Format' next.

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,Sales
Dolphin Pub. pub,romance,Amazon,$456
Dolphin Pub. pub,romance,Dynamic Dist. dist,$65
Dolphin Pub. pub,how-to,Ma and Mo dist.,$87
Dolphin Pub. pub,how-to,Keemer and Son dist.,$654
Sanford Pub. pub,romance,Ma and Mo dist.,$123
Sanford Pub. pub,romance,Keemer and Son dist.,$789
Sanford Pub. pub,how-to,Ma and Mo dist.,$432
Sanford Pub. pub,how-to,Aunt May dist.,$767
3. Select the data table, then click the 'Data' tab on Excel's main toolbar. Click the 'Sort' icon to display the dialog box with sorting options.
4. Choose the following parameters for the 'Sort' dialog box, to sort the publishing data by publisher names.Select 'Publisher' in the 'Sort by' drop-down list.
Select 'Values' in the 'Sort on' list.
Select 'A to Z' in the 'Order' list.Press 'OK' to exit the dialog box and sort the data by publisher.
5. Press the 'Data' tab on the Excel toolbar again, then click the 'Subtotal' icon to display the dialog box for configuring summary totals for the publisher data. Choose the following options in the 'Subtotal' dialog box:Select 'Publisher' for the drop-down list next to the text 'At each change in:'
Select 'Sum' for the 'Use function' drop-down list.
Check the 'Sales' checkbox for the 'Add subtotal to' list.
6. Check the following checkboxes at the bottom of the 'Subtotal' dialog box:'Replace current subtotals'
'Summary below data'Press 'OK' to create the summary rows for the publisher data. Notice that Excel has inserted boldface rows into the data, to add up the separate sales figures for each publisher.
7. Notice the small numbered buttons ('1,' '2,' '3') in the left pane of the main Excel window. Press the '2' button to hide the detail sales rows of the publisher data, and display only the summary sale data for the two publishers.
8. Select the publisher data, then press 'Insert' on Excel's toolbar to display an array of icons for graphical objects that can be inserted in a worksheet.
9. Click the 'Pie' icon in the 'Charts' group of icons, then select the first 3D-chart icon under the '3D Pie' heading. Drag the resulting blank chart carefully away from the publisher data, then drag the new blue frame so it surrounds just the two sales data rows for 'Dolphin' and 'Sanford' publishing. Don't include the rows with the 'Grand total' or the 'Sales' column header.
10. Notice the slices of the finished pie chart, which now display the summary sales data for both publishers.

How to Import Export Excel Text Into Access

Import in Microsoft Access
1. 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 of type' box. Click on the arrow next to the 'Look in' field and locate the Excel file that contains the text you want to import.
5. Double click the Excel file. Click on the 'Import Data into a New Table' option and then allow time for the importing process to complete. The Excel text will then be imported into your Access database.
Export in Microsoft Access
6. Open the Microsoft Access program on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.
7. Click on the 'Open' option and locate the Access database that you want use for import or exporting Excel text. Click on the database file and then click on the 'Open' button.
8. Click on the 'File' or 'Microsoft Office' button again. Click on the 'Export' option and then the Export dialog box will then appear on the screen.
9. Click on the 'Microsoft Excel 97-2003' option from the 'Save as type' box. Click on the arrow next to the 'Save in' box and select the folder you want to save your exported data to.
10. Enter a name for your file into the 'File name' box and the click on the box next to the 'Save formatted' field so that it's selected. Click on the 'Export' button and the data will be exported.

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 layout of your timeline by right-clicking on the timeline and selecting 'Change Layout.' Any of the SmartArt layouts are available. Your text will remain the same from layout to layout so you can adjust the design without changing the content.
4. Animate the presentation of your timeline to highlight individual events. Click on the 'Animations' tab, then click on 'Animate' and select 'One by One' so that each item on your timeline will be revealed separately during the slideshow. You will need to click your mouse for each event listed on your timeline if you choose this animation style.

How do I Add Information on Excel Worksheets to a Summary Sheet?

Excel 2003
1. 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 2007
4. 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 Summary' option and enter the range of cells you wish to summarize. You can select the cells by highlighting the first one and dragging your cursor to the last one.

How to Protect Sales in Excel 2007

Protect an Entire Worksheet
1. 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 Worksheet
6. Click on the bottom tab of the worksheet you want to protect.
7. Click-and-drag the cursor to select the cells containing the data you wish leave unprotected. You can also hold the 'Ctrl' key down and click on multiple individual cells. Entire rows or columns can also be selected.
8. Click the 'Home' tab and then click 'Format Cells.' You can also right-click on a selected cell and select 'Format Cells' from the contextual menu.
9. Click 'Protection' and then uncheck the 'Locked' option.
10. Click 'OK' to apply the changes and close the dialog window.
11. Click on the 'Review' tab and then click on 'Protect Sheet.'
12. Enter a password for the worksheet if desired. Leave the input box blank if you don't want to password-protect the worksheet cells.
13. Click the 'OK' button. All cells except the ones selected in Step One will be protected from future changes.

Wednesday, February 22, 2012

How to Use Freeze Panes in Excel

Freeze the Top Row or Left Column
1. 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 3 through 7 if using this choice.
8. Unfreeze panes by repeating Steps 3 through 5 and selecting 'Unfreeze Panes' or sequentially press the keys 'ALT, W, F, F.'
Freeze Rows and Columns, Multiple Rows, Multiple Columns, or Multiple Rows and Columns
9. Open the Excel spreadsheet.
10. Freeze column(s) and row(s) at the same time by selecting the cell to the right of and below the location you want to freeze.
11. Freeze multiple rows only by selecting the cell in the left (first) column below the rows you want to freeze.
12. Freeze multiple columns only by selecting the cell in the top row to the right of the columns you want to freeze.
13. Navigate to the 'View' tab on the top menu.
14. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.
15. Select the 'Freeze Panes' option. You have now frozen the columns or rows, or columns and rows you designated.
16. Freeze panes using the keyboard by sequentially pressing the keys, 'ALT, W, F, F.' Ignore Steps 5 through 8 if using this choice.
17. Unfreeze panes by repeating Steps 5 through 7 and selecting 'Unfreeze Panes' or sequentially press the keys, 'ALT, W, F, F.'

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 result.

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 on the command button that you just created and select 'Properties.' Select 'Text' to add a caption to the button. Select 'Size' to edit the size and position of the command button. Select 'General' to edit the function of the button. Choose 'AutoLoad' if you want the button activated every time the workbook is opened. Choose 'Placement' to edit the movement of the button on the workbook page. Click 'OK' to save the the command button properties.
6. Right-click on the command button and choose 'Assign Macro.' Select the action that you want the button to perform from the macro list. Click 'OK' to save the command button.

How to Put Error Bars on Points in Excel

Office 2003
1. 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 2007
5. 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 choose the style of error bar, and enter the error amount as a fixed value, percentage or standard deviation.
8. Click 'OK' to apply the error bars.

How to Recover MS Excel Files After Closing

Enable AutoRecover
1. 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 Pane
8. Open the Excel file. A Document Recovery task pane opens next to the worksheet.
9. Click the arrow next to the recovered file icon in the 'Available Files' text box. A list of options appears: 'Open,' 'Save As' or 'Delete.'
10. Click 'Open' to review the version of the file. Click 'Save As' to rename and create a new copy of the workbook. Click 'Delete' to delete this version.

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 to access the column properties menu.
4. Implement column width changes using the column properties menu. Enter in the desired width of the column in pixels into the column properties menu width box and click “Okay” to implement.

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. Click the 'fill handle' in the bottom right corner of the bottom cell, then drag the handle down until all of your required cells are included within the selection. For example, if you entered '1' and '2' into cells 'A1' and 'A2' respectively and wanted to create a 20-number sequence, you would drag the handle to cell 'A20.'
5. Release the left mouse button to fill your selected cells with a list of sequential numbers.

How to Rename Columns in Microsoft Excel Queries

Customize Column Labels
1. 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 Numbers
8. 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 box next to 'R1C1 reference' to rename the columns with numbers instead of letters. To return to using alphabetical labels, check the box.

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 edit.

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 Copy icon. Select Paste if you would like to paste both the formula and the formatting, and click on Paste Special if you want to paste the formula only.
6. Copy your formula into adjacent cells by using the Excel fill handle. This is a small black square that is located at the lower-right corner of the cell or group of cells you have selected. Move your mouse over it, and when the pointer turns to a black cross, left-click and drag the fill handle over the range of cells into which you wish to copy the formula.

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 screen after you finish dropping desired function and option commands into your new customized Ribbon Tab. Once you return to the main Office application screen, the new tab is immediately available for use within the program.

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 (which looks like a pouring paint can) and choose a fill color for those cells.
4. Select all of the cells in row 6 by clicking on the number 6 with your mouse. Right-click and choose 'Format Cells'. Click the 'Alignment' tab of the 'Format Cells' box. Under 'Orientation', move the 'Text' to 90 degrees. If desired, click the 'Font' tab and make changes to the font for those cells. Repeat these steps for row 8, except change the 'Text Orientation' to -90 degrees. This will align your text up and down from the timeline bar.
5. Enter times in the bar itself and enter data in the corresponding cells in rows 6 and 8. If desired, you can add clip art pictures by going to the 'Insert' menu, pointing to 'Pictures' and clicking on 'Clip Art'. Search and enter pictures that suit your timeline. When you are finished, you can print your personalized timeline.

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 C2, then type 'C1:C2' into the Bin Range box.
5. Click 'New Workbook' under 'Output Options,' then click the 'Chart Output' check box. Click 'OK.' Excel will create your frequency distribution table in a new workbook along with an embedded chart.

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 you.

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 Row.'

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 Cells.'

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 2007.

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 occur when a date is entered as text. YEARFRAC will return the #VALUE! error value if any of its arguments is not a valid date.
4. Specify basis, if needed. The default is zero and indicates the days in the month are to be counted with the United States' 30-day method and a 360-day year. A 1 uses the actual number of days in the year. A 2 uses the number of days in the month with a 360-day year while a 3 assumes a 365-day year. A 4 is the same as 1 but uses the European 30-day method.
5. Study an example of YEARFRAC. =YEARFRAC(DATE(2007,1,1),DATE(2007,7,30),2) returns 0.5833333333. There are 210 days separating these two dates and with a 360-day year, we have a year fraction of 210/360 = 7/12 or 0.5833333333.

How to Remove the Contribute Toolbar

Disabling the Contribute toolbar in Internet Explorer and Firefox
1. 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 the changes. You should no longer see the Contribute toolbar when you launch Internet Explorer or Firefox.
4. Click the Windows \'Start\' menu and click the \'Control Panel\' if you want to uninstall the Contribute toolbar completely if you are using Contribute 4. Double-click \'Add or Remove Programs\' to launch the programs window if you are using Windows XP. For Windows Vista and 7 users, click \'Programs\' and click \'Programs and Features\' to launch the programs window.
5. Select \'Adobe Contribute 4\' and click \'Remove/Uninstall.\' The uninstallation wizard launches. Click \'Next.\' Select \'Modify for Contribute 4\' and click \'Next\' again. Deselect the Contribute toolbar and click \'Next.\' Click \'Next\' again and click \'Install\' to remove the Contribute toolbar.
Disabling the Contribute toolbar in Microsoft Office
6. Close Contribute and any Microsoft Office programs.
7. Double-click \'My Computer\' on the desktop. Navigate to the \'Program Files\\Adobe|Contribute 4\' if you are using Contribute 4. Navigate to \'Program Files\\Adobe\\Adobe Contribute CS3\' if you are using Contribute CS3.
8. Right-click the \'OfficePlugin.dll\' file and click \'Rename.\' Rename the file \'OfficePlugin.old\' (without quotes).

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 to enter a static date into the selected cell.
4. Watch as the current date appears in the selected cell. This date will remain static, not being updated at any time during the life of the spreadsheet.
5. Utilize the procedure that is outlined above to insert the static date into any other cells in the spreadsheet where you would like to display a static date.

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 side of the spreadsheet, with a line beside the rows of grouped Excel data. If you grouped columns, you will see the gray box and the line at the top of the spreadsheet.
4. Click the '-' to collapse or hide the grouped fields, and the ' ' to expand or display them.
5. Select the rows and columns that you have grouped. Click 'Data,' 'Group and outline' and 'Ungroup' to remove the grouping. To remove all groups and outlining, click 'Data.' Click 'Group and outline' and choose 'Clear outline.'

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, and 'Series 1' will appear as the only entry in the Legend Series column on the left.
4. Click the Series 1 entry, and then click the Edit button to close the 'Select Data Source' window, open the 'Edit Series' window, and enable Chart Tools on the Excel Ribbon. Verify that mass data is on the y-axis and volume data is on the x-axis.
5. Locate and open the set of Chart Layout types -- the third section from the left on the Ribbon -- by clicking the down-arrow button. Select Layout 9.
6. Double-click on the Chart Title to change the default title to one that is more appropriate. Click on and change the vertical y-axis title to Mass (g) and the horizontal x-axis title to Volume (ml), substituting units of measurement if yours are something other than grams and milliliters.
7. Draw a straight line -- called a trendline -- through the data by right-clicking on any data point and selecting 'Add Trendline' from the resulting pop-up box.
8. Select 'Linear Regression,' check the Set Intercept box, and set the intercept to zero. This action will ensure the trendline is the best fit for your data. By default, Excel will display the equation you are working with on the graph as well as its R2 value. Uncheck these boxes if you do not want to display this information.
9. Make final adjustments to your density graph, using 'Format Axis' and 'Format Size' options. These allow you to change line thickness and color, and increase the size of the graph.

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 tiny arrow at the very top of Excel to the right of the 'Save' icon. Click 'More Commands,' then under 'Choose Commands From,' select 'All Commands.' Click 'PivotTable and PivotChart Wizard,' then click 'Add,' then click 'OK.'
3. Click 'Multiple Consolidation Ranges' in the 'PivotTable and PivotChart Wizard' then click 'Next.' On 'Step 2a' click 'I will create the page fields,'' then click ''Next.' On ''Step 2b'' first open the first worksheet that contains the data. Click ''Collapse Dialogue,'' if it appears, for each cell range you select in the next step. Select the cell range, then click ''Expand Dialogue,'' then click ''Add.' Where the wizard asks 'How many page fields do you want?' click ''0,'' then click ''Next.''
4. In ''Step 3'' you will be asked to select a location for the PivotTable. Select a location for the first spreadsheet, then click ''Finish.''
5. Repeat this process for each cell range in each of the multiple spreadsheets, appending them by the column names which should be the same on each spreadsheet. When the last spreadsheet is consolidated you are ready to begin using the multiple data sources in the PivotTable.

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 to remove the duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data. Make sure the correct column is chosen. If you are looking to delete a duplicate that corresponds to an entire row, make sure you only choose the one row or column with the data you want to delete.
5. Click 'OK.' The duplicate data will be deleted and a dialogue box will appear informing you of how many duplicate entries were deleted and how many unique values remain. Click 'OK' to return to the spreadsheet to continue working.

Subscribe to: Posts (Atom)