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.

Read more ►

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.

Read more ►

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

Read more ►

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

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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

Read more ►

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.

Read more ►

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.

Read more ►