How to Freeze a Row in Microsoft Excel

1. Open the Excel worksheet.
2. Click the top row heading. The row heading displays a number just left of the first column of cells. The selected row appears shaded.
3. Click the 'View' tab on the command ribbon.
4. Click the 'Freeze Panes' button in the 'Window' group. A list of options appears.
5. Click the 'Freeze Top Row' option. A black horizontal line appears on the worksheet. This line indicates the locked row that stays on the screen as you scroll down the worksheet.

Read more ►

How to Add a Title to an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet that contains a chart to which you would like to add a title.
2. Click in the white area of the chart to select the entire chart. There should be a light blue outline surrounding the chart indicating that you have selected the entire chart.
3. Select the 'Layout' tab at the top of the Excel screen to display the layout options for the selected chart.
4. Click the 'Chart Title' button in the 'Labels' section of the 'Layout' ribbon. A drop-down menu will appear that will display the different locations that you can add a title for the selected chart.
5. Choose 'Centered Overlay Title' to add a centered title that lies on top of the existing chart so the chart does not have to be resized. Choose 'Above Chart' to add a centered title that goes above the chart and resizes the rest of the chart so it can fit.
6. Click the chart title you have just added to the chart and move the chart by clicking and dragging it to its new location. You must click on the outline of the chart with a 4-headed arrow before you can successfully move the entire title.

Read more ►

How to Use a Filter in Excel

1. Isolate column headings to one cell. If a column heading spills over into another cell, use text wrapping to place the heading in one cell. Highlight a multi-cell column header, press 'Format' on the menu bar, click 'Row', select 'Autofit and then Format', select 'Cells' and 'Wrap Text' to place headers in one cell.
2. Format the row that contains the column heading differently than the rows that contain data, so Excel recognizes it is a row heading. Embolden characters, change the font color or place a border around the column heading to differentiate from data cells.
3. Ensure each column contains one type of data. For example, a spreadsheet with student data should have a column for test grades, one column for averages, one column for student names and so on.
4. Click any cell inside the spreadsheet you want to filter. If you select an entire column as opposed to a single cell, Excel will present the option to filter that particular column, not all of the columns in the data set.
5. Press 'Data' on the menu bar, scroll down to 'Filter' on the drop-down list and click 'Auto-Filter.' The 'Auto-Filter' drop-down arrows will appear to the right of every column heading of the single column you selected for filtering.
6. Click the drop-down arrow near the column heading for a column of data to display the filter options for the particular column. Observe that Excel displays only the data that applies to your filter selection. Excel hides any rows that do not contain the selected filter.
7. Filter the top or bottom numerical records in a column of data with Excel's 'Top 10' Filter. Click on a data cell in the column and click 'Top 10' at the top of the Auto-Filter drop-down menu in Excel 2003. For Excel 2007, click 'Numbered Filters' and choose 'Top 10.' Select 'Top' or 'Bottom' in the Top 10 Auto-Filter dialog box, choose a number of records from 1 to 500, select 'Items' or 'Percent,' then click 'Apply.'
8. Set custom filters to show records that meet two criteria instead of one. Click the Auto-Filter drop-down menu of the column heading you want to set a filter for and press 'Custom.' Enter two filtering conditions for the column of data. For example, you can see which students scored from 90 to 100 and 60 to 70. Check either the 'And' or 'Or' button or else the results will not display. Click 'OK' to set the custom filter.
9. Click 'Data' on the menu bar, point to Filter and press 'Show All' to turn off the filter and display the hidden data.

Read more ►

How to Put Roman Numerals in Microsoft Office 2003

1. Open Microsoft Office Excel 2003.
2. In the Excel spreadsheet, click on a cell where you wish to put a Roman numeral.
3. Type '=Roman(58)' and press 'Enter.' The Roman numeral 'LVIII' that represents '58' will appear in the cell. Note that you can enter any number from 1 to 3,999 in parentheses.
4. Repeat Step 3 for other cells and/or numbers.
5. Click on a cell with the Roman numeral created in steps 3 or 4, and press 'Ctrl-C' on the keyboard to copy it.
6. Launch Microsoft Office Word 2003. Click the menu 'File' and select 'New' to create a new document, or 'File' and 'Open' to open an existing one.
7. Place the mouse pointer where you wish to insert the Roman numeral and press 'Ctrl-V' on the keyboard.

Read more ►

How to Use a Saved Template for Pivot Charts

1. Click 'Start' and 'All Programs.'
2. Navigate to the 'Microsoft Office' folder, click it once to display the folder contents and then click 'Microsoft Excel' to launch the program.
3. Click the 'Office' button in the upper left corner, and then select 'New' to create a new document. Or click 'Open' to locate and open an existing Excel spreadsheet.
4. Click and hold your mouse on the uppermost cell containing your target data, and then drag the mouse until all of your target data has been selected.
5. Click the 'Insert' tab at the top of the screen. Then click on the 'PivotTable' icon, and select the 'PivotChart' option.
6. Select whether you would like the PivotChart to be inserted into the existing worksheet or into a new worksheet. Click 'OK.'
7. Locate the 'PivotChart Tools' section at the top of the screen, and click the 'Design' tab in this section.
8. Click the 'Change Chart Type' icon at the top of the screen.
9. Select the 'Templates' option in the left column, select your saved template and click the 'OK' button.

Read more ►

How to Create a Calendar in a Pull

Create a Calendar Using Excel 2007
1. Enable the 'Developer' tab. The Excel 'Developer' tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the 'Developer' tab, click the 'Office' button in the top-left corner of Excel. Locate and click the 'Excel Options' button along the bottom right of the menu. Select and click the 'Show Developer in Ribbon' check-box from the pop-up menu, then click 'OK' to exit.
2. Insert a pull-down calendar. Click the 'Developer' tab from the Excel main menu ribbon. Click 'Insert' to display a menu of options and then click the 'More Controls' icon from the ActiveX controls section. The 'More Controls' icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an 'X' pattern. A long list of additional controls will appear, so scroll down until the 'Microsoft Date and Time Picker 6.0' option appears. Click the option and then click 'OK' to return to your spreadsheet.
3. Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.
4. Test the calendar. Deselect the 'Design Mode' tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to 'Design Mode.'
Link Calendar to Another Cell
5. Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in '=B5' (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.
6. Create a link between the calendar and the update cell. Click 'Design Mode' and then click on the drop-down calendar to select it. Select 'Properties' option from the options box next to the 'Design Mode' button and locate the 'Linked Cell' option, and in the 'Linked Cell' information box, type in 'H9' (or whichever cell you select for the date update). Close the 'Properties' box and click on 'Design Mode' to deselect.
7. Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.

Read more ►

How to Make a Dashboard in Excel

1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.

Read more ►

How to Make a Graph in MS Excel That You Change Every Day

1. Open the Microsoft Excel 2010 spreadsheet that contains the data you want to make into a dynamic graph.
2. Click anywhere in your data, then click the 'Insert' tab at the top of the screen. Click the chart button that corresponds to the type of graph you wan to use, then select your desired graph from the popup menu to make it appear on the spreadsheet.
3. Click the 'Formulas' tab at the top of the screen, then click the 'Define Name' button in the middle of the ribbon. Type a name for the first data series in your chart. This is normally the same text as appears in the header above the data series on your spreadsheet, but can be anything you desire.
4. Place your cursor into the 'Refers to:' box. Delete whatever is in the box, then type in '=OFFSET(' and click on the first cell in the column or row where the data for this series is located. Type in a comma, the number one, another comma, then number zero, then another comma. Then type in 'COUNTA($A:$A)-1)' but change each 'A' after the dollar signs to the column letter or row number where your data is located.Your final formula should look similar to this:=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)Click 'OK' to close the Define Name window. Click the 'Define Name' button again and repeat the entire process for each data series in your graph.
5. Right-click on your graph and choose 'Select Data' from the popup menu. Click on your first data series, listed on the left side of the window that appears, then click 'Edit.'
6. Place your cursor into the 'Series values' text box and press backspace several times to delete everything after the exclamation point. Type in the name of the range you created for this set of data.
7. Click 'OK' to go back to the Select Data Source window. Click on a different data series and repeat the process. Continue until you have entered your names for each data series, then click 'OK' to close the Select Data Source window. Your chart now changes as you add new data each day.

Read more ►

How to Make an Address Book on a Computer

1. Open up Microsoft Excel on your computer. You will see that a spreadsheet opens up with predetermined rows and columns. You will use these rows and columns to set up your address book.
2. Add a title to the top of the spreadsheet. This will help you remember what information is in a particular address book. You may need to set up different address books for various purposes--for example, one for members of a book club and one for family members. Having a clear title will help you keep everything organized. Make the title bold and increase the size of the font, using the tools on the top toolbar.
3. Skip a couple of lines after the title and enter headings for the columns in the address book. The following column headings will be very helpful: Name, Address, City, State, ZIP Code, Phone Number, Fax Number and E-mail Address. Type in these headings into columns A to H, respectively. It is very important that each item is entered into a separate column.
4. Center and bold the column headings. To do this, click on the row number to the left of the row of headings. This will highlight, or select, the entire row. And, then, use the bold and center tools on the top toolbar.
5. Enter the contact information in each column. Information for each contact should be entered on a separate row. Depending on how many contacts you have, this could take some time. But, remember, you will only need to do this one time. In the future, you will just have to add new contacts and update information. If you do not have certain items of information for certain contacts, leave the cell blank. Remember to use the two-letter state abbreviations that the post office requires.
6. Format the text in the ZIP Code column to make all of the ZIP codes look the same. Highlight all of the text in the column, except for the column heading. Right-click on the highlighted cells and select 'Format Cells.' Click on the 'Number' tab, and then click on 'Special' in the 'Category' box. Click on 'Zip Code' or 'Zip Code 4' in the 'Type' box. Click 'OK.' This will make all of the ZIP codes look the same.
7. Format the text in the Phone Number column so that all of the phone numbers in the column look the same. Highlight the text in the Phone Number column, except for the column heading. Right-click on it and select 'Format Cells.' In the 'Number' tab, select 'Special' from the 'Category' box. Click on 'Phone Number' in the 'Type' box. Click 'OK.'

Read more ►

How to Draw a Histogram With MS Excel

1. Type your data into column A of your worksheet. For example, click on cell 'A1' and type your first data item, click on cell 'A2' and type your second data item. Continue down the column, clicking on cells and typing your data in until all of your values are on the worksheet.
2. Place your bin widths into column 'B.' Type the top value of each bin, starting in cell B1. For example, if your bin widths were 0 to 4 feet, 5 to 6 feet and 6 feet to 8 feet, type '4' into cell B1, '6' into cell B2 and '8' into cell B3.
3. Click on the 'Data' tab, then click 'Data Analysis.'
4. Click on 'Histogram,' then click on 'OK.'
5. Type the range of your inputs ito the 'Input Range' box. Your inputs are the values you entered into column A. For example, if your inputs are in cells A1 to A10, type A1:A10.
6. Click on the 'Bin Range' box and type the location of the bin ranges. For example, type B1:B3 to indicate the bin ranges are in cells B1 to B3.
7. Click 'New Workbook' under 'Output Options' then click on the 'Chart Output' check box.
8. Click on 'OK' to create the histogram.

Read more ►

How to Insert an Excel Spreadsheet to a VB Form

1. Open Microsoft Excel and type 'A' in 'A1,' 'B' in 'B1,' 'Column A' in 'A2,' and 'Column B' in 'B2.' Save your spreadsheet to 'C:\' as 'ExcelFile.xlsx.'
2. Open Microsoft Visual Basic 2010 Express, click the 'File' menu and select 'New Project.' Click 'Installed Templates,' select 'Windows Forms Application' and click 'OK.'
3. Press 'Ctrl' 'Alt' 'X' to open the 'Toolbox' window. Double-click 'DataGridView' to add a new Data Grid View control to 'Form1.' Double-click 'Button' in 'Toolbox' to add a new button to 'Form1.'
4. Double-click 'Button1' to open the 'Form1.vb' module. Type the following above 'Public Class Form1':Imports System.Data.OleDb
5. Type the following inside 'Private Sub Button1_Click' to declare a 'DataSet' and define the Excel connection:Dim ds As New DataSet()Dim connectionString As String = 'Provider=Microsoft.ACE.OLEDB.12.0;' _'Data Source=C:\ExcelFile.xlsx ;' _'Extended Properties=Excel 12.0;'
6. Type the following to connect to the 'ExceFile.xlsx' file and fill the 'DataSet':Dim excelData As New OleDbDataAdapter('SELECT * FROM [Sheet1$]', connectionString)excelData.TableMappings.Add('Table', 'ExcelSheet')excelData.Fill(ds)
7. Type the following to display the spreadsheet in your Data Grid View:Me.DataGridView1.DataSource = ds.Tables(0)Me.Refresh()Press 'F5' to run your program and press 'Button1' to import the Excel spreadsheet.

Read more ►

How to Tell If a Worksheet Is Protected in Microsoft Excel 2003

Single Worksheet
1. Click on the 'Tools' menu.
2. Select 'Protection->Protect Sheet.'
3. Read the dialog box. If 'Protect worksheet and contents of locked cells' is checked, the sheet is protected.
Workbook (All Worksheets)
4. Open the workbook. If you cannot open the workbook, it is protected at the file level.
5. Add another worksheet to the workbook. If you can't add another sheet, the workbook is protected.
6. Resize the Excel window. If you can't, the workbook is protected.

Read more ►

How to Fill a Series in Excel 2007

1. Type out a series of numbers in the cells you want the start your series. For example, type '1' in cell A1, '2' in cell A2, then '3' in cell A3.
2. Click on the first number in your series, then press 'Shift' and using the arrow buttons highlight the remainder of the series you typed. In the example, highlight cells A1 through A3
3. Click and hold the click in the lower, right-hand corner of the highlighted cells, then move the mouse down to highlight the empty cells where you want to continue your series. In the example, click and hold on the bottom right of cell A3, then drag the mouse down to cell A6. Microsoft Excel will fill cell A4 in with '4,' A5 with '5' and A6 with '6.'

Read more ►

How to Make Custom Receipts

Excel 2010
1. Select the 'File' tab and select 'New.' Click 'Receipts' in the left task pane. Review the receipts that appear. Click a receipt to see a preview in the right task pane. Download a receipt by clicking the receipt image and the 'Download' button in the right task pane. The template opens in Excel 2010.
2. Update the logo section by right-clicking on the default logo and selecting 'Change Picture.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
3. Highlight the default text and type your customized information it. This includes the address, product details, and customer messages. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents template gallery. Type 'Receipt' in the search box. Click 'Search Templates.' Review the templates that appear. Download a receipt by clicking the 'Use This Template' button. The template opens in Google Documents.
5. Delete the default logo by clicking the logo image and pressing the 'Delete' key on the keyboard. Add a new logo by clicking 'Insert' and 'Image.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
6. Highlight the default text and type your customized information it. This can include the address, product details, and customer messages. Save your changes by clicking the 'Save' icon on the menu.
OpenOffice
7. Access the OpenOffice template gallery. Type 'Receipt' in the search box. Review the templates that appear. Download a receipt by clicking the 'Use This' button. The template opens in OpenOffice Calc.
8. Delete the default logo by clicking the logo image and pressing the 'Delete' key on the keyboard. Add a new logo by clicking 'Insert' and 'Image.' Select 'From File.' Browse your PC for the logo you want to use. Click the logo and the 'Open' button. Your logo replaces the default logo.
9. Highlight the default text and type your customized information it. This can include the address, product details, and customer messages. Save your changes by clicking 'Save' on the menu.

Read more ►

How to Delete Every Other Row of an Excel Spreadsheet

1. Open the Excel Visual Basic Editor. With your spreadsheet open in Excel, click 'Tools' from the menu near the top of your screen, then select 'Macro' and 'Visual Basic Editor.' If you use Excel 2003 or older, go on to the next step.Excel 2007 users will notice that the previous command does not exist in the current version of Excel, and that no macro editing tools appear by default. Turn on access to these tools by clicking the Office button at the top-left corner of your screen, then clicking the 'Excel Options' button at the bottom of the menu. Locate the heading labeled 'Top options for working with Excel' in the resulting dialog box, then check the box next to the 'Show Developer tab in the Ribbon' label. Click 'OK.' Select the Developer tab, then click the 'Visual Basic' button.
2. Add macro code. Click 'Insert' from the menu near the top of the Microsoft Visual Basic editor window and choose 'Module.' Copy all of the code listed below, then paste it into this module:Sub Delete_Every_Other_Row()
' Dimension variables.
Y = False ' Change this to True if you want to
' delete rows 1, 3, 5, and so on.
I = 1
Set xRng = Selection
' Loop once for every row in the selection.
For xCounter = 1 To xRng.Rows.Count
' If Y is True, then...
If Y = True Then
' ...delete an entire row of cells.
xRng.Cells(I).EntireRow.Delete
' Otherwise...
Else
' ...increment I by one so we can cycle through range.
I = I 1
End If
' If Y is True, make it False; if Y is False, make it True.
Y = Not Y
Next xCounter
End Sub
3. Run the macro. Return to your spreadsheet without closing the Visual Basic editor. Highlight the rows for which alternating row should be removed. In Excel 2003 and earlier, click the 'Tools' menu option, then select 'Macro', followed by the 'Macros' option. In Excel 2007, select the Developer tab, then click 'Macros.' To run the macro in either version, select the macro labeled 'Delete_Every_Other_Row', then click 'Run.' Every even-numbered row of your spreadsheet deletes.

Read more ►