February 2013 - Excel

Browse » Home » Archives for February 2013

Thursday, February 28, 2013

How to Remove All Hyperlinks Except One in Excel 2003

1. Open on your computer the Microsoft Excel 2003 file that contains the hyperlinks you want to remove.
2. Click any blank cell in your worksheet and type '1' into the cell. Click your mouse outside the cell.
3. Right-click the cell with the '1' in it, and then select the 'Copy' option from the shortcut menu.
4. Hold down the 'CTRL' key on your keyboard while selecting each hyperlink you want to remove. Make sure to not click the one link you want to keep.
5. Select the 'Edit' menu option from the top toolbar, and then click the 'Paste Special' option. Select the 'Multiply' option and then click the 'OK' button. All the hyperlinks you selected will be removed.

How to Change the Chart Axis Using the Graph Standard Toolbar in MS Word

1. Double-click the chart you want to modify in MS Word to make the 'Chart' toolbar visible.
2. Click the 'Category Axis Gridlines' button in the Standard toolbar to make gridlines on the X axis visible.
3. Click the 'Value Axis Gridlines' button in the standard toolbar to make gridlines on the Y axis visible.
4. Select 'Chart' and 'Chart Options' from the menu. The 'Chart Options' dialog box appears.
5. Select the 'Titles' tab to add or modify the titles for the axes on your chart.
6. Go to the text field for the axis' title you want to modify, such as 'Category (X) axis,' 'Series (Y) axis,' and 'Value (Z) axis' and type a title.
7. Select the 'Axes' tab to modify the axis on your chart.
8. Go to the axis you want to modify, such as 'Category (X) axis.' Deselect any axes you want to hide. Select 'Automatic,' 'Category' or 'Time-scale' option for how you want the axis displayed. Click 'OK.'

Wednesday, February 27, 2013

How to Make a Crossword Puzzle Using Excel

1. Open a blank work sheet in Excel.
2. Enter 'Ctrl A' to select all cells. While all cells are selected, place the cursor on the border between two columns of cells and drag them until the cells show a width of 3.75 (35 pixels). Place the cursor between two rows of cells and drag until the row height is 24.75 (35 pixels). This will give you a printable grid with squares that are about 0.3' by 0.3', large enough to write in comfortably.
3. Define the grid area you want to print out. For a 20 cell by 20 cell crossword, select 20 columns by 20 rows. Select all of the cells you want to assign grid lines to. Click on the 'Cell Borders' drop-down (located on the Font area of the tab, with a stylized picture of a grid on it).
4. Enter your first clue in a clear area and give it a number. Type in the answer to the clue on the grid you made earlier. Repeat until you've entered all of your clues and answers in the crossword puzzle.
5. Select all of the cells that don't have letters in them and change their fill color to black. The drop-down for selecting the fill color for a cell is on the Home tab, in the area labeled 'Font.' It is immediately to the right of the drop-down used to select 'Cell Borders' in Step 3. Clicking on it will allow you to select the color.
6. Save the file and print a copy as your answer key, typically as Crossword_Subject_Key.xls. For example, a crossword about Frog Anatomy might be named Crossword_FrogAnatomy_Key.
7. Select all of the cells in the grid (but not your questions) and hit the 'Delete' key. This will delete all of the letters in the cells.
8. Set the font size to 3 points and set the vertical justification to 'top.' (Vertical justifications are the icons next to the font size drop-down in Excel 2007.) Enter the numbers corresponding with each clue in the boxes where each word starts.
9. Save the file (under a different file name, such as Crossword_Subject_Blank) and print out copies. Continuing the example naming convention above, the crossword would be called Crossword_FrogAnatomy_Blank.

How to Locate a Path to MS Excel 2003

1. Click the circular Windows 'Start' button in the bottom-left corner of the desktop. Type 'Excel.exe' in the search box at the bottom of the Start menu. Right-click on the Excel file after it appears at the top of the menu under Programs. Select 'Properties' from the pop-up menu, which opens another dialog box.
2. Select the 'General' tab.
3. Find the 'Location' section in the middle of the tab. The path to the location of Excel 2003 displays similar to the following string of text: 'C:\Program Files\Microsoft Office\Office11.' The Excel 2003 file is within the Office11 folder on the 'C' drive.

How to Extract the Last Word in a String of Text in Microsoft Excel

1. Open the Microsoft Excel worksheet that has the column of text from which you want to extract the last word. As an example, you want to extract the last name from a list of names in column A and insert it into column C.
2. Click in cell “C2.” Enter the following formula and press the “Enter” key:=MID(SUBSTITUTE(A2,' ','^',LEN(A2)-LEN(SUBSTITUTE(A2,' ',''))),FIND('^',SUBSTITUTE(A2,' ','^',LEN(A2)-LEN(SUBSTITUTE(A2,' ','')))) 1,256)You can copy and paste this formula, adjusting the C2 cell reference as necessary. The entire formula goes into one cell.
3. Once you have entered the formula, you can quickly fill the formula down through subsequent cells. To do that, hover your mouse over the black square in the lower right corner of the cell. When the mouse pointer changes to a “ ” symbol, click your mouse and drag down.

Tuesday, February 26, 2013

How to Split Text in Excel 2007

Procedure
1. Highlight the data you want to separate.
2. Click the 'Data' tab at the top of the page, then 'Text to Columns.'
3. Select 'Delimited.' Click 'Next.'
4. Choose your delimiter, such as a space or comma. This is where Excel separates the data. Click 'Next.'
5. Select the 'Column data format' that is appropriate for your data. Click 'Next.'
6. Select where you want the split data to go to and click 'Finish.'

How to Remove Capital Letters in Excel 2007

1. Confirm that you have a block of adjacent empty cells, preferably to the right of your data, that you can use for the reformatted cells. If you don't have room on your spreadsheet, highlight the column of cells directly to the right of your original cells, click 'Insert' on the Ribbon -- not the 'Insert' tab -- and select 'Insert Cells' to add a column of empty cells.
2. Click the cell directly to the right of the cell you want to change. For example, if the text you want to change is in cell A1, click cell B1. Enter '=PROPER(A1)' in cell B1 and hit the 'Enter' key. This will change the text from all caps to initial caps, meaning the text 'CHANGE THE CASE' in cell A1 will appear as 'Change The Case' in cell B1. To convert the text to all lowercase, enter '=LOWER(A1)' in cell B1 and hit 'Enter.' This will convert 'CHANGE THE CASE' to 'change the case.'
3.
Microsoft identifies the version of the software by the year it was released.
Copy the formula. Highlight the cell where you input the formula and click 'Copy' in the 'Clipboard' group on the 'Home' tab. Click the cell to which you want to copy the formula and click 'Paste,' also in the 'Clipboard' group. If you're converting a large number of cells, such as an entire column of text, highlight the formula and any cells to which you want to copy it, and click 'Fill' from the 'Editing' group on the 'Home' tab. Click the corresponding direction to which you want to copy the formula.
4. Highlight the converted cell (or cells) and click 'Copy.' Then highlight the cells where you want to paste the newly formatted data and select 'Paste.' Click 'Paste Special' and then click 'Formulas.' Your correctly formatted text will now be in place.
5. Click the 'Office' button and 'Save' to save your work.

How to Encrypt an Excel 2007 File

1. Open the Excel workbook you want to encrypt.
2. Click the 'Microsoft Office' button in the top-left corner of the screen.
3. Place your mouse cursor over the 'Prepare' entry on the left side of the menu to bring up its options. Click 'Encrypt Document' on the right side of the menu. A password pop-up window will open.
4. Enter a password for the document. Make sure you can remember it since you will not be able to recover the spreadsheet if you forget it.
5. Click 'OK,' re-enter the password and click 'OK' to close the window.

Monday, February 25, 2013

How to Merge Cell Contents in Excel 2007

1. Open the Excel worksheet.
2. Click the 'Home' tab on the command Ribbon.
3. Click and drag on the two or more cells to merge. A heavy black outline will form around the selected cells. The column headers above the outlined cells will turn a different color.
4. Click the down-arrow for the 'Merge Center' command in the 'Alignment' group. The drop-down menu offers three merge formats: Merge Center, Merge Across, Merge Cells. 'Merge Center' centers the text within the larger cell. 'Merge Across' retains the left-aligned text in the row. 'Merge Cells' unites the cells and keeps the data from the upper-left cell.
5. Click on one merge format. The cells will merge to appear as one larger cell. The heavy black outline will disappear.
6. Save this worksheet.

Sunday, February 24, 2013

How to Convert a Workbook in Excel 2007 to a PowerPoint Slide

1. Open the Excel workbook you want to copy to PowerPoint. On the first worksheet press 'Ctrl A' then 'Ctrl C' on the keyboard.
2. Open a PowerPoint presentation. Click on the slide where you want to insert the worksheet. Don't click in a text box, but just a blank area on the slide. Press 'Ctrl V' to paste the table into PowerPoint. Press 'Ctrl M' to create a new slide.
3. Switch back to Excel then click the tab for the next worksheet in the workbook.
4. Select and copy the contents of that sheet and paste them into the new slide in PowerPoint. Repeat this process for each worksheet in the workbook to convert the entire workbook into a PowerPoint presentation.
5. Paste a chart or graph into PowerPoint from Excel by clicking the chart in Excel and pressing 'Ctrl C' to copy it.
6. Switch back to PowerPoint and create a new slide.
7. Press 'Ctrl V' to paste in the chart.
8. Click the 'Paste Options' link next to the chart and choose to link the data or to link to the entire Excel workbook.

How to Convert Excel Macro to Open Office

1. Find a suitable utility program to convert macros in the Excel Visual Basic language to the CalcBasic code necessary for Open Office. A free option is the online conversion program offered by Business Spreadsheets (see link in 'References'). This convenient web page allows the user to paste existing Visual Basic code into a text box and automatically convert it into CalcBasic. While no automatic conversion between the two programming languages will be thorough, this process is a good primer for the tweaking that is inevitably necessary to ensure full functionality in Open Office. This utility is good for programmers looking to quickly jump start the conversion process so all Open Office users will have access to their macro programs.
2. Install a Visual Basic compatibility package into Open Office. This option is particularly convenient as it allows Open Office to run a Visual Basic program without any conversion. However, this is not ideal for any spreadsheet designed for a mass audience as each user would need to make the update to their Open Office software. There are two options for making this change. The Go-oo program extends Open Office software to a variety of file types allowing users to easily integrate many applications.An additional solution along these lines is a full reconfiguration of the Open Office installation package running on a network. Novell has created a new design of Open Office that supports Visual Basic code, but the installation is complicated. This process also relies on the OOo updates offered by Go-oo but integrates them from the ground up. This is better suited to network environments where many individuals will need this functionality.
3. Acquire a version of Open Office that already has Visual Basic compatibility built in. As of 2006 there are at least 10 versions of Open Office supporting Visual Basic. This obviates the process for more robust Excel macro conversion, but is not suitable for programmers looking to reach a wide audience. Open Office in its native format does not recognize Visual Basic code, so this option is only suitable for programmers making the switch for themselves or a controlled user base.

How to Graph Linear Equations Using Excel

Excel 2007
1. Highlight the data you want to graph by clicking at the top left of the data and dragging the mouse to the bottom right.
2. Click on the 'Insert' tab.
3. Select 'Line graph' and choose '2-D Line.' Excel will draw the graph for the linear equation based on the table of values you input.
Excel 2003
4. Highlight the data you want to graph by clicking at the top left of the data and dragging the mouse to the bottom right.
5. Click on the 'Chart Wizard' in the toolbar.
6. Choose 'Line.'
7. Check the 'Chart subtype' box and click 'Next' three times to move through the rest of the chart wizard.
8. Click on 'Finish.' Excel will graph your linear equation using the values from your table.

How to Unlock a Cell in Excel If Information Is Entered into Another Cell

1. Open the Excel spreadsheet that you want to work with.
2. Select the cell that you want to allow someone to enter information into. Right-click the selected cell, and choose 'Format Cells.' Click the 'Protection' tab at the top of the window, and clear the 'Locked' check box. Click 'OK.'
3. Click the 'Review' tab at the top of the Excel window, and then click the 'Protect Sheet' button. Type in a password, if you need one, and click 'OK.' If you entered a password, you will have to type it in again. Be sure to write the password down so that you won't forget it. All of the cells except for the one you selected are now locked.
4. Press 'Alt' 'F11' to open the Visual Basic console. Select the sheet that you are working on from the list of worksheets.
5. Copy the following code into the open space on the right side of the Visual Basic window:Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As RangeSet KeyCells = Range('A1')If Not Application.Intersect(KeyCells, Range(Target.Address)) _Is Nothing ThenWith Worksheets('Sheet1').Unprotect Password:='qqq'.Range('B1').Locked = False.Protect Password:='qqq'End WithEnd IfEnd Sub
6. Edit the code for your specific worksheet. Change 'A1' to the cell that you want users to be able to alter. Change 'B1' to the cell you that want to automatically unlock when users input data into the first cell. Change 'Sheet1' to the name of your worksheet. Change 'qqq' to whatever password you are using to lock the cells. If you are not using a password, delete both instances of 'Password:='qqq'.
7. Press 'Alt' 'F11' to close the Visual Basic window. Your worksheet will now unlock the specified cell when a user enters information into the original cell. Save your work now, because when you test the worksheet out, the target cell will be unlocked.

Saturday, February 23, 2013

How to Troubleshoot Microsoft Excel

1.
Start Excel from the main program. Sometimes a shortcut to a program can become corrupt and cause the application to have problems opening. Go to the 'Start' menu, click on 'All Programs' and find Microsoft Excel in the program list. If it opens properly, delete the problematic shortcut by right clicking it and selecting 'Delete.' You can make a new shortcut by right clicking the main application and choosing 'Send to Desktop.'
2.
Disable add-ins. Sometimes these optional programs are problematic. In Excel 2007, click the 'Office' button. Click on 'Excel Options' and select 'Add-Ins.' In Excel 2000-2003, go to the 'Tools' menu and select 'Add-Ins.' If you disable them and the problem is solved, enable them one at a time to determine which is corrupted.
3.
Let Excel find and fix the problem. If you are using Excel 2000-2003, go to the 'Help' menu, click on 'Detect and Repair' and then click 'Start.' In Excel 2007, click the 'Office' button, click 'Excel Options' and then click 'Resources.' Click 'Diagnose' and then click 'Continue.' Click the 'Start Diagnostics' button. Excel will look for potential problems and repair them, if possible.
4.
Perform a 'System Restore' on your computer. This will restore your computer to an earlier date and time when Excel was working properly. Click 'Start' and then 'All Programs.' Point to 'Accessories,' and then 'System Tools.' Click 'System Restore,' and follow the wizard's instructions.
5.
Uninstall Microsoft Office and then reinstall it . Go to the 'Start' menu and then to the 'Control Panel.' Select 'Microsoft Office' and uninstall it. Use the Office disk you used to install it originally and reinstall the software.

How to Make a Graph on Excel That Shows Names for the the Y

1. Open a new Microsoft Excel 2010 spreadsheet. Click on cell 'B1' and type in the name of your first data series. This name will appear in your graph's legend. Then click on cell 'C1' and enter the name for your second data series.
2. Click on cell 'A2' and enter the names that will appear down your 'Y' axis into column 'A.' Continue to enter names until you have added them all.
3. Select cell 'B2' and type in the first data point that falls under the column and row names. Then select cell 'C2' and repeat the process. Continue until you have entered all of your data into columns 'B' and 'C.'
4. Click on cell 'B2.' Select the 'Insert' tab at the top of the screen. Click the 'Bar' button in the Charts area of the ribbon, then click one of the available bar graphs that appears in the pop-up menu. All of the available bar graphs will let you have names on the 'Y' axis and a certain number of data points on the 'X' axis. Once you click the graph type your bar graph will appear in the spreadsheet.
5. Click anywhere on the graph to select it, then select the 'Layout' tab at the top of the window. Click the drop-down arrow next to 'Chart Area' and select 'Horizontal (Value) Axis' from the list of choices. Click the 'Format Selection' button located just under the drop-down box and the Format Axis window will appear.
6. Click the radio button next to 'Fixed' in the 'Maximum' category on the right half of the window. Type the maximum value you want for your data points into the box next to the button. Then click on the 'Fixed' radio button in the 'Major Unit' category. Type in the value exactly one-half as large as your maximum value into this box.
7. Click 'Close' to close the Format Axis window. Your graph will now have the only two data points on the horizontal axis.

How Do I Change Data in Vertical Columns to Horizontal Columns in Excel or Access?

Transposing Data in Excel
1. Highlight the columns you want to transpose into rows. Press “Control C” to copy the data.
2. Open a new worksheet for the transposed data. Select an empty worksheet tab from the bottom of the Excel window, or press “Shift F11” to create a new worksheet. Rename the worksheet “Transposed Data.”
3. Right-click on cell “A1” to open the context menu. Select “Paste Special” from the menu to open the “Paste Special” dialog.
4. Check the “Transpose” box at the bottom of the “Paste Special” dialog to transpose the data. Click “OK.” Your data will be pasted into the spreadsheet as rows. Save your workbook.
Transposing Data in Access
5. Open the table you want to transpose. Select “Save As/Export” from the “File” menu. Select “To an External File or Database” in the 'Save As' window and click “OK.”
6. Give your table a name and select the version of Excel installed on your computer from the dialog box. Click “Export.”
7. Open your exported file in Microsoft Excel. Follow Steps 1-4 of “Transposing Data in Excel.” Close Microsoft Excel.
8. Import the transposed data into Microsoft Access. Select “Import” from the “File” menu. Use the “Files of Type” drop-down list and select “Microsoft Excel.”
9. Locate and select the file that contains the transposed data and click “Import.” Click “Show Worksheets” on the first screen of the Import Wizard and select the “Transposed Data” worksheet. Click “Next.”
10. Do not select the “First Row Contains Column Headings” option in the second screen of the Import Wizard; click “Next.” Check the “No Primary Key” option on the third screen of the Import Wizard and click “Finish” to import your data into a new table.

Friday, February 22, 2013

How to Make a Schedule in Excel

1. Open Microsoft Excel. click the 'File' menu and choose 'New.'
2. Go to the 'Available Templates' section and the Office.com Templates area.
3. Preview a schedule template by clicking on it. Once you have found the one that fits your requirements, click the 'Download' button. The schedule will open as a new Excel worksheet.
4. Change existing text in the schedule template by double-clicking on the cell that contains the text you want to alter. Select the existing text using your mouse or the keyboard shortcut 'Ctrl-A.' Type over the existing sample text with the information you need on the schedule.
5. Modify the color of a cell by clicking the cell to select it. Right-click and choose 'Format Cells.' Go to the 'Patterns' tab and choose the hue you want in the Cell Shading Color section. Click the color and click 'OK' to apply it to the cell.
6. Adjust the font in any cell by clicking the cell to activate it, and right-clicking and choosing 'Format Cells.' Go to the 'Fonts' tab and choose a font type, style, size and color, and apply any effects you choose. Click 'OK' to apply the changes.
7. Press 'Ctrl-S' to save your schedule. Provide a name for the worksheet and navigate to the location where you want it saved.

How to Activate the Excel Help Button

1. Open Microsoft Excel.
2. Click the 'Help' button, which is the blue circle with a question mark in the middle of it in the upper-right corner of the worksheet just below the 'Maximize'and 'Minimize' buttons. The 'Excel Help' window is activated and pops up on the screen.
3. Click one of the links in the 'Getting started with Excel 2010' area or type a search term into the help text box at the top of the window. Close the help box by clicking the 'X' in its top-right corner.

How to Stop Auto Recalculation Upon Data Entry in Excel

1. Open Microsoft Excel, and then open a new or previously saved workbook.
2. Click 'File' at the top of the screen. Click on 'Options' in the drop-down menu that appears. The 'Excel Options' window opens.
3. Click 'Formulas' on the left side of the window. Under 'Workbook Calculation,' click 'Manual.'
4. Click the 'Recalculate workbook before saving' check box if you want to turn this option off as well. Click 'OK' to close the window.

How to Format Dates in Descending Order in Microsoft Excel

1. Confirm that the dates exist in a single column, running vertically. Excel can only sort data in the columns, not the horizontal rows, of a spreadsheet.
2. Highlight the data you want to sort.
3. Right click any highlighted cell and select 'Format Cells.'
4. Select 'Date' from the 'Category' column, choose your preferred format from the adjacent 'Type' column, and then click 'OK.'
5. Click 'Data' in the top menu bar and select 'Sort.' If you use Excel 2007 or later, select the 'Data' tab near the top of the window and click the 'Sort' icon instead.
6. Click the drop-down menu below the 'Order' heading and select 'Oldest to Newest.'
7. Click 'OK' to sort your dates in descending order.

How to Use V

1. Open Excel and open a spreadsheet that you plan to use the vlookup on. Make sure the spreadsheet has column headers and at least three columns. Each column should have at least 10 lines of varying data so you can see how vlookup works. For example, your spreadsheet could have three columns with the title of 'Employee ID number,' 'Name,' and 'Hire Date.' The first column, employee ID, should be sorted in ascending order. This is a requirement of Vlookups.
2. Review the components of a vlookup. Your vlookup will be comprised of four parts: lookup value, table array, column index number, and range lookup. The lookup value is the value that you will enter in the vlookup. Based on this value, the vlookup will give you the result from another column. For example, in this vlookup example with our current data, we will find the hire date when someone enters the lookup value of the employee ID number.The table array is the table that contains the data the vlookup will be searching for the result. In our example, we will use our three columns as our data. The column index is the column that contains the result we are looking for. In our example, if we are looking for the hire date when we enter the employee ID number, our column index refers to the third column or column C. In our formula we enter 3 to represent column C since it is the third column in the table.Lastly, in the range lookup, we indicate if we are looking for a close enough match or exact match. If it is close enough, enter TRUE. If you want an exact match, enter FALSE. In our example, we want an exact match because when we enter the employee ID number, we want to know that employee's hire date.
3. Enter your vlookup in the first cell of the fourth column. On the Formulas tab, click on the 'Lookup button.' Select 'Vlookup.' The function arguments window opens. In the Lookup Value box, enter the 'employee ID number.' In the table array, click on the red box at the end of the 'Table Array' field. Highlight your table and click on the red box again. In the column index number, enter the '3' because the hire dates are stored in the third column. Since we are looking for an exact match, enter 'FALSE' in the Range Lookup field. Click 'OK.'

How to Make Microsoft Excel 2007 Talk

1. Open the spreadsheet that you want Excel to dictate.
2. Click the 'Customize Quick Access Toolbar' drop-down arrow located next to the 'Quick Access Toolbar.' The 'Quick Access Toolbar' contains the 'Save,' 'Undo' and 'Redo' buttons.
3. Click 'More Commands' and click 'All Commands' from under the 'Choose Commands From' drop-down list.
4. Scroll down the list, click 'Speak Cells' and click the 'Add' button.
5. Click 'Speak Cells -- Stop Speaking Cells' and click the 'Add' button. Click 'OK.'
6. Highlight the cells that you want Excel to dictate and click the 'Speak Cells' button in the 'Quick Access Toolbar.' To stop using the 'Speak Cells' function, click the 'Speak Cells -- Stop Speaking Cells' button in the 'Quick Access Toolbar.'

Thursday, February 21, 2013

How to Open Excel 2003 in Safe Mode

1. Locate the icon for opening Excel 2003 by clicking the 'Start' button and selecting 'All Programs,' then 'Microsoft Office,' then 'Microsoft Office Excel 2003.' Don't click the icon yet.
2. Hold down the 'Control' key on your keyboard while you left-click 'Microsoft Office Excel 2003.' You should click the left mouse button normally, but continue to hold down the 'Control' key until you see a Windows message prompt.
3. Select 'Yes' when Windows prompts you for confirmation of running Excel in safe mode. Excel will open after you click 'Yes.'

How to Make a Combo Box in Excel 2003

1. Open Excel on your computer, and open a new spreadsheet. Somewhere in the spreadsheet, enter the data that will appear in the combo box. For example, you might enter data between cells C4 to C10.
2. Go to the 'Forms' toolbar, and select 'Combo Box.'
3. Drag your mouse over the cells that you want the combo box to encompass. Once you release your mouse click, the combo box will appear.
4. Right-click on the new combo box,, and choose 'Format Control' from the menu. Specify the cell range of your data in the 'Input Range' field; in the above example, you would enter C4:C10, because those are the cells that hold your data.
5. Go to the 'Cell Link Box,' and enter the number of the cell where you want the index value of items selected in the combo box to appear. Click 'OK.'

How to Execute a Macro in Excel 2003

1. Open Excel 2003 and select the workbook that will use the macro. Click 'File' on the menu bar and select 'Open.' Browse your files and locate the workbook. Click the workbook and select 'Open.'
2. Click 'Tools' on the menu bar and select 'Macro.' Select 'Macros.' Select your macro from the list of available macros.
3. Click 'Run.' The macro is executed on the workbook.

How to Calculate the Percent of a Quarter Passed in Excel

1. Open a new Microsoft Excel spreadsheet.
2. Click on cell 'A1' and enter the first day of the quarter. Then click on cell 'A2' and enter the last day of the quarter. Finally, click on cell 'A3' and enter in the date with which you want to determine the percentage of the quarter passed.
3. Click cell 'B1' and enter in the following:=A3-A1This will tell you how many days into the quarter you are. Click cell 'B2' and enter the following:=A2-A1This will tell you how many total days there are in the quarter.
4. Click on cell 'B3' and enter the following:=B1/B2This will give you your percentage in decimal form. Right-click the cell and choose 'Format cells.' Select 'Percentage' from the list on the left side and click 'OK.' Your result will now display as a percentage.

How to Make the Lines Print When Working With Excel 2003

1. Open the spreadsheet you're working on in Excel 2003 and highlight the cells you want to print.
2. Go to the 'Formatting' toolbar and click the arrow beside the 'Borders' button to expand it. Select 'All Borders' from the list. If the 'Borders' button is not visible in the 'Formatting' toolbar, click the 'Toolbar Options' arrow at the end of the toolbar to expand it. Choose 'Add or Remove Buttons,' 'Formatting' and 'Borders.' Alternatively, you can select 'Format' and 'Cells' from the menu to open the 'Format Cells' dialog box. Select the 'Border' tab and click 'Inside' from 'Presets.' Select the single, solid line style from the 'Line' section. Go to the 'Border' section and click the top, middle, left and right borders. The thumbnail in the dialog box now shows borders around and between the selected cells. Click 'OK' to apply them.
3. Click the 'Print Preview' button in the 'Standard' toolbar. The border lines are displayed in print preview, signifying how the spreadsheet will look when it's printed.
4. Click the 'Print' button at the top of the window. The 'Print' dialog box opens. Choose 'Selection' in the 'Print what' section and click 'OK.'

Wednesday, February 20, 2013

How to Set Page Break Views in Microsoft Excel 2003

1. Create a page break. To create a page break, left-click on the cell rows that you wish for a page break to occur by clicking on the corresponding row number to the left; the entire row will become highlighted when you left-click on the row number.
2. Access the “View” menu. Scroll to the “File” tab on the command bar and then select “Page Break Preview.”
3. Use the page break preview menu. Once inside of the page break preview menu, you will be able to see where page break occurs on the spreadsheet, because it will have a double grey line highlighting it.
4. Adjust where page breaks are located. To adjust page breaks, simply left-click on the grey page break link and hold the mouse button in as you drag the page break where desired.

How to Convert Excel 2007 to Dbf

1. Download a converter program designed to save Excel 2007 files to dbf format. There are a number of such programs on the market. Some of them are free and others are available at a reasonable cost. Please see the Resources section for links to some of these converter programs.
2. Open Microsoft Excel 2007 and create the spreadsheet you want to convert. Save the file in Excel 2007 format to your hard drive or to your network shared drive.
3. Close the Excel spreadsheet and open your conversion program. Choose 'import data into database.' You can choose to import the data from the Excel 2007 spreadsheet into a database you have already opened, or you can import the data into a database file on your hard drive or network share.
4. Click 'OK' to begin the import process. Choose the appropriate format for each field in the spreadsheet, i.e. text for labels and notes and currency for pricing data.
5. Close the converter program and open your database program. You can open the dbf file you have created in Microsoft Access or dBase. Review the data to make sure all of the fields were imported successfully.

How to Make an Invoice in Access

1. Open Microsoft Access. Go to 'File,' 'Time Billing' under the 'Office.com Templates' section. You can also type “Office.com Templates” in the search box, in the upper right corner.
2. Open the 'Time Billing' folder. Select the 'Time Billing' template to show the 'Project List' window.
3. Select the “Reports” drop-down menu and click “Invoice.” A new window will open with Microsoft Access default information. Delete or add pertinent information relating to your organization in the fields to create your invoice.

How to Connect Excel to SQL Using Visual Basic

1. Open Excel, then type a list of names in one column and ages in an adjacent column. Label the tops of these columns with the text 'Names' and 'Ages.' This step creates a database of sample data for your SQL program to fetch. You can create a table with different data if you'd like.
2. Drag a selection region around the table, then type 'MyTable' in the text box to the left of the formula bar. This creates a range name for the sample data, which your program will need to access the data.
3. Save the file as 'C:\MyDatabase.xlsx,' then close the file.
4. Press 'Control,' followed by 'N' to create a new spreadsheet. You'll store your SQL program in this sheet.
5. Press 'Alt,' followed by 'F11' to enter the VB development environment, commonly abbreviated as the 'IDE.' Developers write and sometimes execute VB programs in this IDE.
6. Click the 'Insert' menu, then click the 'Module' item. This action creates a new code window for you to enter your SQL program listing.
7. Click the 'Tools' menu heading, then click 'References.' Place a check in the checkbox labeled 'Microsoft ActiveX Data Objects.' This action makes visible the database objects that Excel needs to run the SQL query.
8. Paste the following program into the new code window.Sub sqlVBAExample()Dim objConnection As ADODB.ConnectionDim objRecSet As ADODB.RecordsetSet objConnection = New ADODB.ConnectionobjConnection.ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myDatabase.xlsx;Extended Properties=''Excel 12.0 Xml;HDR=YES'';'objConnection.OpenSet objRecSet = New ADODB.RecordsetobjRecSet.ActiveConnection = objConnectionobjRecSet.Source = 'Select * From myTable'objRecSet.OpenRange('D10').CopyFromRecordset objRecSetobjRecSet.CloseobjConnection.CloseSet objRecSet = NothingSet objConnection = NothingEnd Sub
9. Click one of the program's statements, then press 'F5' to run the program.
10. Press 'Alt,' and then 'F11' to return to the Excel spreadsheet. The spreadsheet will display the results of the SQL connection that your program established. The results include the database table you created earlier.

Tuesday, February 19, 2013

How to Make Column Titles in Excel 2007

1. Open an Excel worksheet.
2. Click on a cell above a column. The selected cell will show a heavy black outline. The column and row headers will change to a different color.
3. Enter the column title.
4. Edit or format the title for a custom look. For example, you can change the font, font color, size and alignment. Click on the 'Home' tab and use the commands in the 'Font' and 'Alignment' groups to change the format of your column title.
5. Save the worksheet.

How to Use Radio Buttons in Excel

Using Radio Buttons in Excel 2003
1. Open the worksheet to which you want to add radio buttons. Open the 'Control Toolbox' by going to the 'View' menu, pointing to 'Toolbars' and selecting 'Control Toolbox.' The toolbox appears as a floating toolbar.
2. Click the radio button control in the 'Control Toolbox.' If you are not sure which control this is, hover your mouse over each button in the 'Control Toolbox' and click on the one that says 'Option Button.'
3. Click onto the worksheet where you want to place the first radio button and drag the box to 'draw' the size you want it to be.
4. Right-click the radio button, point to 'Option Button Object' and click 'Edit.' Delete the 'Option Button' text and type in your desired text for the first radio button. Click outside of the box to stop editing and apply your text.
5. Right-click the radio button and select 'View Code' to add a macro to the radio button. Right-click the radio button and select 'Properties' to change other properties of the button. Click the 'Exit Design Mode' button in the 'Control Toolbox' to leave design mode and enable the radio button.
Using Radio Buttons in Excel 2007
6. Display the 'Developer Tab.' Click the 'Office Button' and click 'Excel Options.' Select the 'Show Developer Tab in the Ribbon' check box under 'Top Options for Working With Excel' and click 'OK.'
7. Go to the 'Developer' tab and click 'Insert' in the 'Controls' group. Click 'Option Button' under 'ActiveX Controls.'
8. Click onto the Excel worksheet and drag the mouse to draw the radio button onto the page.
9. Click 'Design Mode' in the 'Controls' group of the 'Developer' tab. Right-click the radio button, point to 'Option Button Object' and click 'Edit.' Delete the 'Option Button' text and type in your desired text for the first radio button. Click outside of the box to stop editing and apply your text.
10. Right-click the radio button and select 'View Code' to add a macro to the radio button. Right-click the radio button and select 'Properties' to change other properties of the button. Click the 'Exit Design Mode' button in the 'Control Toolbox' to leave design mode and enable the radio button.

Monday, February 18, 2013

How to Merge Two Columns in Excel 2007

1. Open Excel and search for a spreadsheet that contains two columns you want to merge. Select 'Open' on the Quick Access Toolbar and search for the spreadsheet on your computer. Locate the spreadsheet and click 'Open.'
2. Insert a blank column beside the columns you want to merge. Select the column beside the columns you want to merge and click on the column header. For example, if you want to combine Column A and B, click on the column header C. Once the column header is selected, the entire column is highlighted. Right click on the column header, and select 'Insert.'
3. Use the concatenate function in the first cell of the new column. Click on the cell that will display the first set of merged data. Click on the 'Formulas' tab on the ribbon and select 'Text.' Select 'Concatenate' from the list of available functions. The Function Arguments window opens.
4. Click in the 'Text1' box and select the first cell in the first column that you want merged. The cell data will appear in the 'Text1' field. Click in the 'Text2' box and select the first cell in the first column that you want merged. The cell data will appear in the 'Text2' field. Click 'OK.' The data is now merged.
5. Copy this formula from the first cell to the remaining cells that you want merged. Copy the first formula by right clicking on the cell and selecting 'Copy.' Highlight the remaining cells in the newly copied column and select 'Paste.' Your data is now merged.

How to Calculate Daily Percentage Change in Excel 2007

1. Type 'Beginning of Day' in cell A1, 'End of Day' in cell B1 and 'Percent Change' in cell C1 in Excel 2007.
2. Type the day's beginning value in cell A2. For example, if a stock's price is $40 at the start the day, type '40.'
3. Type the day's ending value in cell B2. In the example, assume the stock closed at $45. In this case, type '45' in cell B2.
4. Type '=(B2-A2)/A2)' in cell C2. In the example, you get the result '0.125.'
5. Select the 'Home' tab, and click the 'Number' box.
6. Select 'Percentage' under 'Category.'
7. Choose the amount of decimal places you want your percentage to have, and click 'OK.'

Sunday, February 17, 2013

How to Make a Alphabetized Mailing List in Microsoft Excel

1. Launch Microsoft Excel 2010.
2. Type 'First Name' in the top cell in the first column (Cell 'A1') and press 'Tab' on the keyboard to save the contents of the cell and move to the top cell in the second column (Cell 'B1').
3. Type 'Last Name' in the top cell in the second column and press 'Tab' to save the contents of the cell and move to the top cell in the third column. Continue this pattern for 'Address 1,' 'Address 2,' 'City,' 'State' and 'Zip.' Add other fields as needed and add their title to the top row.
4. Right-click on the letter at the top of the column which contains the zip code. Click 'Format Cells' from the context menu. Click 'Special' in the 'Category' column and click 'Zip Code' or 'Zip Code 4' in 'Type.' Change the locale if necessary and press 'OK' to save the changes.
5. Click or navigate to the second cell in the first column (Cell 'A2') and type in the first name of the first person or business on the mailing list. Press 'Tab' to move across the columns and fill in all of the available information. Press 'Tab' twice to leave a cell blank if necessary.
6. Fill in all of the addresses and click 'File' and 'Save As' to name and save the file.
7. Click the letter 'A' above the first column and hold down the mouse button. Drag the mouse across the top letter of each column that contains data to select them.
8. Click 'Data' from the ribbon at the top of the program and click 'Sort' from the 'Sort Filter' group.
9. Click the arrow in the 'Sort By' and change the selection to reflect the column which is to be sorted by, such as 'Last Name' or 'Zip Code.'
10. Click the arrow in the 'Order' group and choose whether to sort in ascending or descending mode. Click 'OK' to sort all selected cells as a group.

How to Highlight Alternating Rows in Excel

Using Excel Table Style
1. Open the Excel worksheet with the data you want to highlight.
2. Click the 'Home' tab on the ribbon, then click 'Format as Table.' Excel asks you where the data for your table is. Click 'OK' to accept the range Excel highlighted, or enter the range manually. Click back to 'Home' if the 'Design' tab opens. Click 'Format as Table' again.
3. Select the alternate shading that you want. You can select from the 'Light,' 'Medium' or 'Dark' shading groups. Your table should now have alternating rows highlighted.
Using Conditional Formatting
4. Open the Excel worksheet with the data you want to highlight.
5. Select the range you want to have alternating rows. To select the entire sheet, click the little triangle to the left of 'column A' and above 'row 1.' To select some rows instead of the whole sheet, place your cursor over the first row number you want to select until you see a black arrow. Click and drag down until you have selected all the rows you want.
6. Click the 'Home' tab on the ribbon, then click 'Conditional Formatting.' Select 'New Rule' from the bottom of the menu.
7. Select the last rule in the top window, 'Use a formula to determine which cells to format.' You should see an empty line pop up in the middle of the pane.
8. Enter the following under 'Format values where this formula is true':=MOD(ROW(),2)=0
9. Click 'Format.'
10. Select the 'Fill' tab and choose a color from the color grid. Click 'OK.' Your color appears in the preview pane where you typed the rule.
11. Click 'OK.'

How to select non

1. Select your first cell by clicking your mouse button on it. The cell will now be shaded grey. If you want to select an entire row or column, click its heading (such as 'B' or '13') in the frame around the spreadsheet.
2. Hold the control key (command key on an Apple computer) and click the mouse button on another cell. Both cells will now be shaded grey. Any action, such as copy, will now apply to both cells. An unlimited number of cells can be selected as long as the control key is held down.
3. Re-click an already-selected cell with the control key pressed down to unselect it without affecting the selection of other cells. Letting go of the control key and clicking an unselected cell with select it and remove any existing selections from the clipboard.

How to Extract Information From Cells in Excel 2003

1. Open your Excel 2003 workbook file.
2. Highlight the cells from which you want to extract data. You can do this by either placing the cell in one corner of the block you want to work with, holding the 'Shift' key down and using the arrow keys to expand the block; or you can click and drag across the block.
3. Click 'Edit' and 'Copy' using the top menu. You have now extracted a copy of all the data in the cells including formulas, formatting and values (the actual numerical values that show on your spreadsheet).
4. Navigate to where you want to place and review the extracted information. Click 'Edit' and 'Paste Special' from the top menu.
5. Choose 'Values' from the pop up box to paste the numerical values of the extracted cells. This choice will ignore the formulas that produce the values.
6. Choose 'Formulas' to copy the formulas for each cell. This will ignore the values of each cell, and produce new values when you insert new data into the cells that apply to the formulas.
7. Choose 'Formats' to copy shading, borders and other formatting features from cells and ignore the contents of the cells.

How to Create a Drop

1. Create the list of selections that you want listed in the drop-down box. Select a tab other than the one on which the drop-down box will appear. Type each selection in an individual cell. The cells in which the selections are listed should be sequential. For example, selections 'A,' 'B' and 'C' would be listed in cells A1, A2 and A3. A list of selections may also be created in the same tab on which the drop-down box will be created.
2. Select the cell in which you want to create the drop-down box. Click the 'Data' tab and locate the 'Data Tools' group. Select the 'Data Validation' option. This action will open a separate dialog window.
3. Click the 'Settings' tab on the Data Validation window. Select the 'List' option from the 'Allow' menu. Click the 'In-Cell Drop-Down' check box.
4. Click the 'Source' field. Click the tab that contains the list of options you created in Step 1. Click once in the first cell of the selection string and drag to the last cell while holding down the mouse button. Click the 'OK' button on the Data Validation window.

How to Sort a Table in Word 2003

1. Open Microsoft Word 2003.
2. Create a table or modify an existing table. It should contain several columns with every cell filled.
3. Place your cursor inside the table and left-click anywhere on it.
4. Click 'Table,' and then 'Sort.' You may have to click the arrow to expand your options.
5. Choose a column heading to sort in the drop-down menu under 'Sort.'
6. Choose a type. 'Text' is for sorting alphabetically. With 'Number,' you sort by value. 'Date' lets you sort chronologically.
7. Choose ascending or descending. Ascending will arrange text from A to Z, numbers from least to greatest, and dates from earliest to latest. Descending does the opposite.
8. Choose 'Using,' and then select which fields or words you want to sort by.
9. Repeat steps 5 through 8 for 'Then by.' Do this if you only want to include more than one column as a basis for sorting.
10. Indicate whether you have a header column. Select 'Header row' to exclude the first row in sorting.

How to Switch to Manual Calculation in Excel

1. Open Microsoft Excel and start a new blank workbook or open an existing workbook from your files.
2. Choose the 'Formulas' tab to display the 'Formulas' ribbon at the top of the screen.
3. Click the 'Calculation Options' button in the 'Calculation' section of the 'Formulas' ribbon to view the drop-down list of options. Choose 'Manual' from the list of options to switch to manual calculation. This will tell Excel that you want to switch from automatic calculation to manual calculation.
4. Enter your formulas into your Excel workbook as usual. When you press the 'Enter' key after typing a formula, it will no longer automatically calculate your formula without you specifically instruction it to.
5. Select the 'Calculate Now' button in the 'Calculations' section of the 'Formulas' ribbon. Every formula that you have entered into the current Excel workbook will now be calculated.

Saturday, February 16, 2013

How to Make a Calendar in Excel 2007

Add Calendar Control to Worksheet
1. Display the 'Developer' tab of the ribbon. Click the 'Office Button.' Click the 'Excel Options' button. Select the 'Show Developer Tab in the Ribbon' check box under 'Popular Options.' Click 'OK.'
2. Go to the 'Developer' tab. Click Insert and choose 'ActiveX Controls.'
3. Click 'More Controls' and select 'Calendar Control' in the 'More Controls' list. Click 'OK.' Excel inserts a calendar control in 'Design View,' which allows you to edit and customize the control.
4. Right-click the Calendar Control and click 'Calendar Object.' Select 'Properties' to open the 'Calendar Controls Properties' dialog box. Select options for the font, color and other aspects of the control. Click onto the worksheet when finished.
5. Click on the calendar control and click on the date you want to add to the worksheet. Excel automatically enters the date into the selected cell.
Make Printable Calendar from Template
6. Click the 'Office Button' and select 'New.'
7. Type 'calendar' into the 'Search Templates on Office Online' box. Press the 'Enter' key. A gallery of template options opens.
8. Select the calendar template you want to use. Click the 'Download' button. The calendar opens as a new Excel file.
9. Click inside of a date on the calendar to which you want to add a note or reminder. Type directly into the empty space on that date. Go to the 'Page Layout' tab. Click 'Themes.' Click the 'Colors' drop-down arrow to select a different color scheme. Select 'Fonts' to change the fonts on the calendar.
10. Click the 'Save' button on the 'Quick Access Toolbar,' enter a name for the calendar and click 'Save.' Print the calendar if desired.

How to Make a Drop

1. Open Excel and select a range of cells to which you want to apply the data validation. For example, select cells A1 through A10. With the cells selected, click the “Data” menu and choose “Validation.”
2. Change the Allow criteria to “List” in the validation criteria. In the Source field, add the values you want to see in the drop-down list. Enter the values separated by a comma only. Do not include spaces. For example, enter “Yes,No,Maybe” to see those three options in the drop-down list.
3. Modify the input message so other users see a friendly reminder telling them what you are looking for in these cells. Click on the “Input Message” tab. Make sure the “Show Input Message When Cell is Selected” option is checked. In the title field, add a title for your custom message. Type your custom message in the “Input Message” field.
4. Modify the error alert message so users see a error alert if they input the wrong type of information. Click on the “Error Alert” tab. Place a check mark in the “Show Error Alert After Invalid Data Is Entered” option. Change the style to “Stop” if you want the message to stop users from inputting invalid data. In the title field, add a title for your custom message. Type your custom message in the “Error Message” field. Click “OK.”
5. Test your data validation by clicking on cell A1. You will see a drop-down list appear with the options Yes, No and Maybe. You should see the input message you specified. Try typing in the word “PDF.” The custom error message will appear. The invalid value will not be retained and you will get the prompt to “Retry” and enter a correct value.

How to Wrap Text in Microsoft Excel 2003

1. Highlight the cells in which you want to wrap the text. If you want more than one cell, drag your cursor to the cells.
2. Find the 'Format' tab on the menu bar and click 'Cells.' Then click the 'Alignment' tab.
3. Click on 'Text control' and choose the 'Wrap text' option. Click 'OK.'

How to Print Two Excel Worksheets on Opposite Sides of a Paper

1. Click the 'Start' button in the bottom-left of the desktop. Click 'All Programs' and then click 'Microsoft Excel 2007' to launch the program.
2. Click the 'Microsoft Office Button' from the upper-left of the Excel program and then click 'Open.' Click the Excel workbook of choice and then click 'Open.'
3. Click the 'Microsoft Office Button,' and then select 'Print' from the drop-down menu. Then click 'Print' again, and click 'Properties.'
4. Click the 'Layout' tab and click 'Duplex' or turn on whatever two-sided printing option is available.
5. Click 'OK' to print.
6. Repeat the steps for each worksheet that you wish to print duplex in Excel.

How to Insert Functions in Excel 2007

1. Insert a function into an Excel cell manually by clicking in the cell, typing it in and pressing the 'Enter' key. The AutoComplete menu will pop up as you begin to type the formula. If you do not need it, ignore it. If you are unsure how to enter a function without assistance, use the 'Formulas' menu.
2. Insert a function into an Excel cell by selecting the 'Formulas' tab on the Top menu. This is especially helpful if you are unsure what function to use.
3. Click the button that most closely describes the action you are trying to perform with the function. A pull-down list of functions opens. Hover over each function with your mouse and a box describing the function appears.
4. Left-click the function to automatically insert it in the cell. A box opens that tells you exactly what the function does and how to properly structure the variables. Enter the variables and select the 'OK' button.
5. Click in the cell to make any changes to the function. A structure guide for the formula will automatically appear below the cell as long as you have the Formula AutoComplete tool turned on.
6. Turn on the Formula AutoComplete option if it is not already on. Click the 'Microsoft Office Button,' click 'Excel Options' and then click the 'Formulas' category. Under 'Working with formulas,' check the box next to 'Formula Auto Complete' and click the 'OK' button. You can enter a formula with the assistance of the Formula AutoComplete tool.
7. Start typing the formula. When you reach the first letter of the function you would like to use, it triggers Formula AutoComplete. Using the SUM function, for the example, when you type in '=s,' Excel filters the Formula AutoComplete list by all the functions and active cell references that begin with 's.' Typing in the next letter ('=su') filters the list to all options that begin with 'su' and the filtering continues as you type in more letters.
8. Navigate the AutoComplete menu using the arrows on your keyboard or selecting each one by left-clicking with your mouse. As you land on each option, Excel displays a Screen Tip designed to help you choose the best option.
9. Double-click your selection and the function will be entered with the left parenthesis in place ('=sum(') and a description of what the formula should look like underneath. For the SUM function, this looks like 'SUM(number 1, [number 2], [number 3], ...).' Review the description if necessary to ensure that your formula is correctly constructed.
10. Now enter your data and close the formula with the right parenthesis: ').' Using the SUM function, your function could look like this: '=sum(5, B1:B25, c23, c1, b1 b2).' You have inserted a function into Excel.
11. Understand your formula.This formula states, 'five (5) plus the sum value of the range b1 to b25 (B1:B25) plus the value in cell C23 (C23) plus the value of cell B1 plus the value of cell B25 (B1 B25).This formula demonstrates some of the structures defined as numbers by Excel.

Friday, February 15, 2013

How do I Recover an Overwritten Excel File?

1. Locate the document that overwrote the old one you want to retrieve. Right-click the file name, then select 'Rename.' A box will pop up with the file name. Add the word 'new' or today's date to make it easier to tell it apart from the file you want to recover. Once you've edited the name, click 'OK' or 'Continue.'
2. Download XLS Regenerator (see 'Resources'). This program is made specifically to recover Excel files. Start with the free trial by clicking 'Free trial' on the main page, then following the instructions to download for your operating system. Once it's downloaded, double-click on the icon to install.
3. Run the program XLS Regenerator by double-clicking on the icon once it's installed. When the main screen pops up, select 'Recover files,' then 'Recover overwritten files.' The program will now ask for information on the file to be recovered. If you remember the date it was last open, select the date. Then type in whatever you remember from the file name. Click 'Start.'
4. Wait for the program to search your computer for overwritten files. Usually this is in the form of an automatic save--often a hidden file--made before the first overwrite. When it finishes searching, look at the list for the document or documents you want to retrieve. Select all of the documents you want (avoiding the newly named file from step 1) and click 'Retrieve' in the lower right corner.
5. Move the retrieved files to a new folder once they've finished. Open each file to see if it has the correct information you need. If not, repeat steps 3 and 4 with different search parameters until you locate the overwritten documents.

How to Name a Column in Excel 2007

Using Name Box
1. Click the column heading, such as 'A,' 'B' or 'C,' to select the column you want to name in Excel 2007.
2. Go to the 'Name Box' just above column A.
3. Type the column's name into the box and press 'Enter.' The column's name displays when you select the column header.
Using R1C1 Reference Style
4. Click the Customize Quick Access Toolbar button and select 'More Commands' in Excel 2007.
5. Select the 'Formulas' tab in the Excel Options window.
6. Check 'R1C1 reference style' under the 'Working with formulas' section. Click 'OK.'

How to Split Excel Data Into Two Columns

Prepare Your Columns
1. Open the Excel file with data that you want to split.
2. Insert a blank column to the right of the column with data you want to split. For example, if the column you want to split is column F and data exist in column G that you do not want overwritten, insert a new column between columns F and G. To insert a column, click any cell in the column to the right of your data column.
3. Click the 'Home' tab on the ribbon.
4. Click the 'Insert' button in the Cells group.
5. Select 'Insert Sheet Columns' to create a blank column to the left of your cursor, that is, to the right of your data column. For example, if your data reside in column F, you would click on any cell in column G before inserting the column. Excel creates a new column to the left of column G.
Split Your Column
6. Click the 'Data' tab in the ribbon.
7. Select the column you want to split by highlighting the column. To highlight the column, click on the column letter.
8. Click the 'Data' tab on the ribbon. Click 'Text to Columns.' You will see a wizard pop up that will guide you through the process.
9. Select either 'Delimited' or 'Fixed Width,' and click 'Next.'
10. Select the kind of delimiters you want if you selected 'Delimited' in the previous screen. The most common delimiter is a space. Select as many delimiters as apply. Excel will use this information to determine where one piece of data ends and another begins so it can split the data appropriately. If you selected 'Fixed Width,' you will see a ruler above your data. Click on the ruler at the desired points to create a break.
11. Click 'Next.' You will see the third step in the process.
12. Select a format for each of your two columns if you want. However, this is not necessary, and you can always adjust the formats later. Excel will assign the General format for each column as the default.
13. Click 'Finish.'

How to Do a Comparative Chart in Excel

1. Open the Excel worksheet that contains the data for the chart.
2. Click and drag to select the data values and categories.
3. Click the “Insert” tab on the command ribbon.
4.
A column chart can display different categories or data seriesin different colors.
Click the arrow for a preferred chart type in the 'Charts' group. For example, a “2-D Column” chart type can display two or more data series as different colors or shades. A gallery of chart sub-types appears.
5. Click the preferred chart sample. The worksheet data converts to the selected chart type. The chart appears with the worksheet. Each data series displays a different color. The “Chart Tools” ribbon displays.
6. Customize the chart with the “Chart Tools” command. For example, click the “Design” tab to display the “Chart Layouts” and “Chart Styles.” Click the “Chart Styles” to convert the colors of the data series.

Excel Graph Tutorials

1. Open the Excel 2010 spreadsheet that contains the data that you want to use in a graph.
2. Organize your data so that you have headers at the top of each column, and, if necessary, at the left end of each row. If you need to create a header row or column, just right-click the top or leftmost row or column and click 'Insert' from the pop-up menu. A blank row or column will appear, and you can use that to enter your column or row headers.
3. Click on any cell in the data field, and then click the 'Insert' tab at the top of the screen. Click the small button in the lower-right corner of the 'Charts' area of the ribbon to open up a new window will all of the Excel chart options.
4. Click the type of chart that you want to use from the list on the left side of the window. Excel offers 11 different chart types, ranging from a standard column or bar chart to more exotic doughnut and radar charts.
5. Click the exact chart that you want from the list on the right side of the screen. Each chart type has several different variants that have slightly different appearances and may handle information in a slightly different way. Next, click 'OK.' Excel automatically expands your selection on the spreadsheet to include all the cells adjacent to your selected cell and the chart will appear on the spreadsheet.
6. Click the chart and then click the 'Design' tab at the right side of the ribbon. This tab gives you access to buttons that will change the color and shape of the chart. You can also click the 'Select Data' button to reselect the data that will make up the chart.
7. Click the 'Layout' tab at the top of the screen to change the chart's appearance. The buttons in the 'Labels' section will change how the axis titles, chart title and data labels appear. The 'Axes' area holds buttons that will add or remove axes labels and grid lines on the chart. You can also click the 'Insert' button to add a picture, text box or shape to your chart.
8. Click the 'Format' tab at the top of the screen to get access to the controls that change the overall look of the chart. The 'Shape' area contains buttons that change the appearance of the chart window, including the color of the border and any effects on the border. The 'WordArt Styles' area contains buttons that change the color and effects for the text within the chart. Finally, the 'Size' area contains the length and width of the chart, which you can change to your liking.

How to Highlight Minimum Maximum Values in Excel

1. Click the cell at one end of the range that you are analyzing.
2. Drag the mouse to the cell at the other end, selecting the entire range.
3. Click 'Conditional Formatting' from the 'Styles' tab of the 'Home' ribbon. A drop-down menu will open.
4. Click 'New Rule...' from the bottom of the list.
5. Click 'Format only top or bottom ranked values' from the 'Select a Rule Type' pane.
6. Select 'Top' from the first of the drop-down boxes in the 'Edit the Rule Description' pane.
7. Type '1' into the text box in the 'Edit the Rule Description' pane.
8. Click the 'Format' button, which opens the 'Format Cells' dialog box.
9. Click the color that you want to use to highlight the maximum value. Click 'OK.'
10. Click 'OK' in the 'New Formatting Rule' dialog box. The range's maximum value will now appear highlighted in the color you chose.
11. Repeat Steps 1 through 5.
12. Select 'Bottom' from the first of the drop-down boxes in the 'Edit the Rule Description' pane.
13. Repeat Steps 7 and 8.
14. Choose a different color to represent the range's minimum value. Click 'OK.'
15. Click 'OK' in the 'New Formatting Rule' dialog box. The range's minimum value will now appear highlighted as well.

Thursday, February 14, 2013

How to Make a Coordinate Plane in MS Excel

1. Open a new, blank Excel document. Click the rectangle in the upper-left corner of the spreadsheet located at the intersection of column A and row 1. This will select the entire spreadsheet. Click the View tab. In the Show/Hide group, deselect 'Gridlines'.
2. Place your cursor on a line between any two column headers. Your cursor will change to a vertical line crossed by a horizontal arrow. Drag the line left until the column width is exactly 20 pixels. When you release the mouse, all cells will be square. Click in cell A1 to remove the highlighting.
3. Click cell C3 and drag and highlight the 400-cell area to cell V22. In the Font group on the Home tab, click the arrow on the border tool. Select the border that resembles a window with four panes.
4. Click the Insert tab. In the Illustrations group, click the arrow on Shapes. Select the line with two arrow heads.
5. Draw the x-axis between row 12 and row 13. To make a straight line, hold the 'Shift' key while you click and drag. Draw the y-axis between columns L and M.

How to Create a Stacked Chart in Excel 2003

1. Enter your data in column 'A,' with the label for the data in cell 'A1,' and each data value put into cells 'A2,' 'A3' and so on, until you've entered all of the data needed for the chart.
2. Select the range of cells from cell 'A1' through the last cell with a data value in it.
3. Click the 'Insert' menu and select 'Chart.' This will start the Chart Wizard.
4. Click 'Column' for the Chart Wizard from the options under 'Chart Type.' A 'Chart sub-type' menu will show up.
5. Click '100% Stacked Column' and click 'Next.' This selects the stacked chart subtype.
6. Click 'Rows' on the next window of the wizard; this tells where the series data will be imported. Click 'Next.'
7. Clear the check box for 'Show Legend' and add your labels for the chart. Click 'Finish.'

How to Use Indirect in Excel to Link to an External Spreadsheet

1. Create two spreadsheets. One will be named PullData.xlsx. The other will be ReviewData.xlsx.
2. Enter the following into cell A1 of Sheet1 of PullData.xlsx: 'Shazam!'
3. Enter the following formula into cell A2 of PullData.xlsx: '=27 54'
4. Switch to ReviewData.xlsx. Enter the following text into the following cells: In cell B1, enter 'PullData.xslx'. In cell B2, enter 'Sheet1'. In cell B3, enter 'A1' and in cell B4, enter 'A2'.
5. Enter the following formula in cell A1 on ReviewData.xlsx: '=INDIRECT(''['B1']'B2''!'B3)'. Note that there are both double quotes (') and single quotes (') in that formula; they're there to ensure that the formula works even if the file name or the sheet name inside the file have spaces.
6. Hit Enter. Cell A1 in ReviewData.xlsx should now say 'Shazam!' If you change the B3 in the last part of the INDIRECT formula to B4, cell A1 ReviewData.xlsx will show '81'.

How to Insert a Title Into a Table in Excel 2007

1. Click on the table.
2. Click 'Design.' This is a tab near the top of Microsoft Excel.
3. Select 'Table Name' in the 'Properties' group.
4. Type your title in the text box that appears.

How to Lock Specific Cells in Excel 2007

1. Highlight the entire sheet by clicking the 'Select All' button in the top-right corner of the sheet. It's just to the left of the A column and immediately above the 1 line.
2. Right-click anywhere on the sheet and select 'Format Cells.' The Format Cells pop-up window will open.
3. Click the 'Protection' tap at the top of the window.
4. Click the block next to 'Locked' to clear the check mark, and click 'OK' to close the window.
5. Click anywhere on the sheet to remove the highlighting. Select all the cells you want to lock. You can drag to select cells that are connected. To select individual cells, you can press the 'Ctrl' key as you click them one by one.
6. Right-click any of the highlighted cells, and select 'Format Cells.' Click the Protection tab and click the box next to 'Locked' to check it. Click 'OK' to exit the window.
7. Click the 'Review' menu at the top of the window. Click the 'Protect Sheet' icon on the right side of the menu options. Make sure the box next to 'Protect worksheet and contents of locked cells' is checked. You can also enter a password so other users cannot unlock the cells. Click 'OK' to finish.

How to Create a Histogram in Microsoft Word

1.
Open a blank Word document, or open the document in which you want to create a histogram. Insert a table onto the document. In Word 2007, go to the 'Insert' tab, click the 'Table' dropdown and select 'Insert Table.' In earlier versions of Word, go to the 'Table' menu, point to 'Insert' and click on 'Table.' Choose the number of columns you need in your chart, including a column to label the rows, and enough rows to present your data.
2.
Enter the y-axis data for the histogram into the first column of the table. For example, if you are making a histogram showing how much money employees earned annually, you would put a number range for employees in each cell of the first column. Enter the x-axis data in the bottom row.
3.
Highlight the cells in the first column to show the number of employees who earn that salary (as in the example). Right click these cells and select 'Borders and Shading.' Go to the 'Shading' tab.
4.
Select a fill color for the first column. Select the correct number of cells for the second column, right click it, select 'Borders and Shading,' and choose a different fill color for that column. Repeat with the remaining columns, filling each one with a different fill color.
5.
Insert a text box to the left of the table. The text box should be the same height as the column, but very narrow. To insert a text box in Word 2007, go to the 'Insert' menu and click 'Text Box.' In previous versions, select 'Text Box' from the 'Insert' menu. Type the label for the y-axis column into the text box. Type the label for the x-axis data under the table and the histogram is complete.

How to Create an Amortization Table in Excel

1. Use the loan APR, amount and term in Excel's 'PMT' function to calculate the monthly payment. If you have a 30-year mortgage for $200,000 at a 6 percent APR, type the following in cell A1.
=-PMT(0.06/12,30*12,200000)
2. Type in column headings for the amortization table. In cell A3, type 'Unpaid Balance.' In cell B3, type 'Principal.' In cell C3, type 'Interest.' Adjust the column widths as necessary.
3. In cell A4, type the initial loan value. For the previous example, you would type '200000.'
4. Calculate the interest on the first month's payment. In cell C4, based on the previous example, type the formula:
=A4*(.06/12)
This multiplies the unpaid balance by the monthly portion of the interest rate.
5. Calculate the principal on the first payment. In cell B4, type the formula:
=$A$1-C4
This subtracts the calculated interest from the calculated monthly payment.
6. Calculate the second month's unpaid balance. In cell A5, type the formula:
=A5-B4
This subtracts the principal from the previous month's payment from the loan balance.
7. Copy the formulas down each column until the table is complete. A 30-year mortgage, for example, will have 360 rows.

Wednesday, February 13, 2013

How to Do Multicolor Column Charts in Excel 2010

1. Open the Excel worksheet that contains the data for the column chart.
2. Click and drag to select the categories and data values for the column chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the arrow for the “Column” chart in the “Charts” group.
5. Click the preferred column chart to add a chart representing the selected worksheet data.
6. Click in the chart area to display the Chart Tools ribbon.
7. Click the “Format” tab in the Chart Tools ribbon.
8. Click one column in the plot area. All the columns will appear highlighted.
9. Click the same column once again. This selected column appears outlined.
10. Click the “More” arrow in the “Shape Styles” gallery. A larger gallery of visual styles appears.
11. Mouse over the color samples to preview the effect on the selected column. For example, “Moderate Effect -- Purple, Accent 4.”
12. Click the preferred color sample. The column converts to the selected color. The legend also displays the updated color.

How to Compare Columns With VBA

1. Click the 'Developer' tab in Excel, and then click 'Visual Basic.'
2. Click 'Insert.' Click 'Module' to open a new module window.
3. Cut and paste the following code into the window:Sub CompareColumns()Dim Column1 As RangeDim Column2 As Range'Prompt user for the first column range to compare...'----------------------------------------------------Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column1.Columns.Count > 1 ThenDo Until Column1.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)LoopEnd If'Prompt user for the second column range to compare...'----------------------------------------------------Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column2.Columns.Count > 1 ThenDo Until Column2.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'Check both column ranges are the same size...'---------------------------------------------If Column2.Rows.Count
Column1.Rows.Count ThenDo Until Column2.Rows.Count = Column1.Rows.CountMsgBox 'The second column must be the same size as the first'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'If entire columns have been selected (e.g. $AA), limit the range sizes to the'UsedRange of the active sheet. This stops the routine checking the entire sheet'unnecessarily.'-------------------------------------------------------------------------------If Column1.Rows.Count = 65536 ThenSet Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))End If'Perform the comparison and set cells that are the same to yellow'----------------------------------------------------------------Dim intCell As LongFor intCell = 1 To Column1.Rows.CountIf Column1.Cells(intCell) = Column2.Cells(intCell) ThenColumn1.Cells(intCell).Interior.Color = vbYellowColumn2.Cells(intCell).Interior.Color = vbYellowEnd IfNextEnd Sub
4. Press 'F5' to run the routine and compare the columns.

How to Unlock Excel 2007 Password Protection

Unlock Document Password
1. Open the Excel file in Excel 2007. You will need to enter the correct password to open the file.
2. Click on the Microsoft Office Button, located in the upper right corner of the Excel 2007 program. Move your mouse over 'Prepare' on the left hand side of the window. Choose 'Encrypt Document' from the list of options on the right.
3. Place your cursor to the right of the password in the box. Use the backspace key to erase the password. Click 'OK' when you are done. The Excel 2007 document is no longer password protected.
Unlock Worksheet Password
4. Open the Excel 2007 file that you need to work on. If needed, select the correct locked worksheet from the list of sheets at the bottom of the Excel document.
5. Select the 'Review' tab at the top of the screen.
6. Locate the 'Change' area and click on either 'Unprotect Sheet' or 'Unprotect Workbook.'
7. Type in your password into the pop-up window. Click 'OK' when you are done. The worksheet, or workbook, is no longer protected.

How to Change the Color Scheme in an Excel Chart

Change Chart Colors in Excel 2007
1. Open the spreadsheet containing the chart you want to edit.
2. Single-click the chart once to select it.
3. Single-click the individual piece of the chart you want to change to select the piece.
4. Right-click the selected piece and choose the 'Format Data Point' option.
5. Click 'Fill' in the left-hand menu, and then choose the fill option that meets your design needs. Select your desired color, gradient, picture or texture and the selected chart shape is updated immediately. Click 'Close' to close the Format Data point window.
Change Chart Colors in Excel 2003 and earlier
6. Open the spreadsheet containing the chart you want to edit.
7. Single-click the chart once to select it.
8. Single-click the individual piece of the chart you want to change to select the piece.
9. Right-click the selected piece and choose the 'Format Data Point' option.
10. Click the 'Patterns' tab. Choose the fill option that meets your design needs by selecting your desired color, or by clicking the 'Fill Effects' button to view additional options. Click 'OK' to confirm your change and close the Format Data Point menu.

Tuesday, February 12, 2013

How to Remove Security From an Excel Spreadsheet

1. Open the Excel spreadsheet and enter your password.
2. Click the 'Review' tab on the Excel toolbar.
3. Click the 'Unprotect Sheet' button to remove security from the spreadsheet. Make sure you do not click 'Unprotect Workbook' unless you want to remove security from the entire workbook.
4. Enter the password for the spreadsheet if prompted and click 'OK'.

How to Create a Distribution List to Import Into Outlook 2007

1. Open a blank Excel worksheet. Enter 'First Name' into cell A1. Enter 'Last Name' into cell B1. Enter 'Email Address' into cell C1.
2. Enter the first name of the first person you want to add to the distribution list into cell A2. Enter that person's last name and email address into B2 and C2. Enter the names and email addresses of all of the contacts you want to add to the distribution list into the cells in the appropriate columns.
3. Click the 'File' tab in Excel 2010. Click 'Save As.' Enter a name for the file, such as 'Distribution List,' into the File Name field. Change the 'Save as Type' to 'CSV (Comma Delimited).' Click 'Save.'
Import the List
4. Open Microsoft Outlook and select 'Contacts' in the navigation pane on the left. Right-click your contact list and select 'New Folder.' Enter a name that relates to your distribution list and click 'OK.' Double-click the new folder to open it.
5. Click the 'File' tab, select 'Open' and choose 'Import.'
6. Select 'Import from Another Program or File' and choose 'Next.' Select 'Comma Separated Values (Windows)' and click 'Next.' Click the 'Browse' button. Find and select the Excel file you created and click 'Open.'
7. Choose whether you want duplicates allowed and click 'Next.' Select the folder you created in your contact list and click 'Next.' Click 'Finish' to import your distribution list.
Create the Distribution List
8. Select the folder containing the list you imported.
9. Click on 'New' and select 'Distribution List' or 'Contact Group.'
10. Type a name for the distribution list into the Name field.
11. Click 'Select Members' or 'Add Members.' Select the new folder in the Show Names From list.
12. Click on the contact at the top of the list. Press and hold the 'Shift' key and select the contact at the bottom of the list to select all of the contacts. Click 'Members,' click 'OK' and click 'Save and Close' to save your new distribution list.

How to Split a Cell Diagonally

Microsoft Word
1. Click anywhere within the table to make the 'Design' tab visible in Microsoft Word.
2. Click the 'Design' tab under 'Table Tools.'
3. Go to the 'Draw Borders' group and select 'Draw Table.'
4. Place your cursor at the top corner of the cell you want to split.
5. Click, hold and drag your mouse from the top, right corner of the cell to the opposite corner to split it diagonally.
6. Click the 'Draw Table' button to deactivate the 'Draw Table' option.
Microsoft Excel or PowerPoint
7. Click the 'Insert' tab in Microsoft Excel.
8. Go to the 'Illustrations' group and click 'Shapes' to expand its options.
9. Select the 'Line' option from the 'Lines' section.
10. Place your cursor on the top, left corner of the cell you want to split.
11. Click, hold and drag your mouse towards the opposite corner to split the cell diagonally.

How to Add a Second Y

1.
Create an Excel graph for all the rows and columns. At the end of this step you will have a graph with some very high lines and the rest flat along the bottom.
2.
Select one of the lines which are causing the skewing of the graph. If more than one line is the culprit, select the highest.
3.
With the lines selected, right click on the graph and select 'Format Data Series.' This will open another box. In the new box, select 'Secondary Axis.' Then click the Close button.
4.
Your graph will now display the second Y-axis on the right of the graph and your lines will be spaced out.

How to Disable Cell Drag in Excel

1. Open Excel and click the 'File' menu tab in the top-left corner of the screen.
2. Click 'Options' on the bottom-left of the menu to open the Excel Options window.
3. Click 'Advanced' in the left window pane.
4. Click the box next to 'Enable Fill Handle and Cell Drag-and-Drop' to clear the check mark.
5. Click 'OK' to close the window and save the changes.

How to Move Data on an Excel 2007 Worksheet

1. Open the worksheet you want to change. Find the document in your saved documents and select it--double click twice on it. The worksheet will then open in Excel.
2. Decide what actions you want to do. For example, you may have made a file that is your address book. When you first put the file together, you put in columns in the following order: first name, last name, phone number, address, city, state and zip code. You now see that it would be easier if you had arranged the data this way: last name, first name, address, city, state, zip code and phone number.
3. Add a column. Put your cursor anywhere in column A. Go to 'Insert' and choose 'Column.' A new column will appear and your data will now start in column B.
4. Select the column with last names in it by clicking on the letter that has your data in it. This will select the whole column of data--for example, if your last names are in column C by selecting C at the top of the column all the data will be highlighted.
5. Right click your mouse and a menu will come up where you can 'Cut' the data. This will make the data disappear from column C. Move your cursor to column A row 1 and right click. Select 'Paste' and your data will be moved to that column.
6. Delete column C to reposition the data. Your data now appears this way: last name, first name, phone number, address, city, state and zip code.
7. Repeat Steps 4 to 6 to move phone number from column C to column G and the data will be in the proper columns.
8. Save your data by either selecting 'Save' or 'Save as.'

Monday, February 11, 2013

How to Retrieve Values From Another Excel 2007 Workbook

1. Determine the location of the workbook you want to retrieve values from. For example, the workbook might be in the same directory as the one you want to display the values in, and it might be named 'other_workbook.xlsx.'
2. Find the the row and column of the value you want to bring in from the other workbook. For example, you may be looking for a 'total' that is sitting in cell G4 on Sheet1 in other_workbook.xlsx.
3. Type the following formula into the cell you want to display the value from the other workbook on:
=[other_workbook.xlsx]Sheet1!A1
4. Change 'A1' in the formula from Step 3 to the cell you determined in Step 2. In our example you'd change 'A1' to 'G4'.

How to Extract Pivot Table Data

1. Type the '=' sign, without the quotes, into an empty cell.
2. Click on a cell in the pivot table. A formula will appear next to the equal sign.
3. Press the 'Enter' key. The cell will now display the data from the Pivot table.

How to Wrap Text in a Cell

1. Highlight the cells you want the text to be wrapped in. Use your computer's mouse to left-click in the first cell you wish to select and continue to drag it over the desired cells. Release it when the cells are highlighted.
2. Click on 'Format' in the Excel task-bar or right-click on the cell block you have highlighted.
3. Click on 'Format Cells' and a pop-up window will appear.
4. Click on the 'Alignment' tab and check the 'Wrap Text' box on the left-hand side.
5. Click 'OK' to close the 'Format Cells' pop-up window. The highlighted cells should now wrap when you resize your cell box.

Subscribe to: Posts (Atom)