How to Insert a Static Date in Excel

1. Open Microsoft Excel 2007, and start a new spreadsheet that you would like to insert a static date into or open an existing spreadsheet from your files into which you want to insert a static date.
2. Click to select the cell to which you want to insert the static date. The cell will then be surrounded by a thick, black outline indicating it is ready for you to insert the static date into it.
3. Use your keyboard to enter the shortcut of 'CTRL' ';' into the selected cell. Both the 'CTRL' key and the semicolon key must be pressed at the same time. This keyboard shortcut will tell Excel to enter a static date into the selected cell.
4. Watch as the current date appears in the selected cell. This date will remain static, not being updated at any time during the life of the spreadsheet.
5. Utilize the procedure that is outlined above to insert the static date into any other cells in the spreadsheet where you would like to display a static date.

Read more ►

How to Group and Outline Excel Data

1. Open your Excel spreadsheet. Organize the data to be outlined. Make sure the fields you want to group are adjacent to each other. Insert summary rows or columns beneath or beside the fields that you will be outlining. Summary rows or columns should contain formulas to average or sum up the fields to be outlined.
2. Highlight the series of columns or rows you want to group. Select rows or columns by clicking on the gray letter or number field at the side or top of the spreadsheet.
3. Choose 'Data' from the menu. Choose 'Group and outline.' Select 'Group.' You'll see a gray box on the left side of the spreadsheet, with a line beside the rows of grouped Excel data. If you grouped columns, you will see the gray box and the line at the top of the spreadsheet.
4. Click the '-' to collapse or hide the grouped fields, and the ' ' to expand or display them.
5. Select the rows and columns that you have grouped. Click 'Data,' 'Group and outline' and 'Ungroup' to remove the grouping. To remove all groups and outlining, click 'Data.' Click 'Group and outline' and choose 'Clear outline.'

Read more ►

How to Make a Graph for Density in Excel With Microsoft

1. Select the two Excel spreadsheet columns that contain density graph data, and then click the 'Insert' tab on the Excel main menu.
2. Set the Excel graph to a “Scatter with only Markers” type by selecting this option from the drop-down menu on the 'Charts: Scatter' section of the Excel Ribbon menu. This is the best option for a density graph, not only because you are comparing pairs of values, but also because each pair represents a separate measurement.
3. Right-click anywhere on the graph, and choose “Select Data” from the resulting pop-up window. The 'Select Data Source' window will open, and 'Series 1' will appear as the only entry in the Legend Series column on the left.
4. Click the Series 1 entry, and then click the Edit button to close the 'Select Data Source' window, open the 'Edit Series' window, and enable Chart Tools on the Excel Ribbon. Verify that mass data is on the y-axis and volume data is on the x-axis.
5. Locate and open the set of Chart Layout types -- the third section from the left on the Ribbon -- by clicking the down-arrow button. Select Layout 9.
6. Double-click on the Chart Title to change the default title to one that is more appropriate. Click on and change the vertical y-axis title to Mass (g) and the horizontal x-axis title to Volume (ml), substituting units of measurement if yours are something other than grams and milliliters.
7. Draw a straight line -- called a trendline -- through the data by right-clicking on any data point and selecting 'Add Trendline' from the resulting pop-up box.
8. Select 'Linear Regression,' check the Set Intercept box, and set the intercept to zero. This action will ensure the trendline is the best fit for your data. By default, Excel will display the equation you are working with on the graph as well as its R2 value. Uncheck these boxes if you do not want to display this information.
9. Make final adjustments to your density graph, using 'Format Axis' and 'Format Size' options. These allow you to change line thickness and color, and increase the size of the graph.

Read more ►

How to Use Multiple Data Sources in One PivotTable

1.
Data with the same headers can be stacked in longer columns.
Prepare the data to consolidate by making sure the source spreadsheets all have the same column headers and row names. This will enable you to place the correct data in the correct column during the consolidation (see Resources).
2.
The Pivot and Chart Wizard can expedite tasks.
Click a blank cell in the Excel spreadsheet where you will create the consolidation.Hold down 'Alt' 'D' 'P' to open the 'PivotTable and PivotChart Wizard.' If you plan to use this wizard often you can add it to the 'QuickAccess' toolbar by clicking the tiny arrow at the very top of Excel to the right of the 'Save' icon. Click 'More Commands,' then under 'Choose Commands From,' select 'All Commands.' Click 'PivotTable and PivotChart Wizard,' then click 'Add,' then click 'OK.'
3. Click 'Multiple Consolidation Ranges' in the 'PivotTable and PivotChart Wizard' then click 'Next.' On 'Step 2a' click 'I will create the page fields,'' then click ''Next.' On ''Step 2b'' first open the first worksheet that contains the data. Click ''Collapse Dialogue,'' if it appears, for each cell range you select in the next step. Select the cell range, then click ''Expand Dialogue,'' then click ''Add.' Where the wizard asks 'How many page fields do you want?' click ''0,'' then click ''Next.''
4. In ''Step 3'' you will be asked to select a location for the PivotTable. Select a location for the first spreadsheet, then click ''Finish.''
5. Repeat this process for each cell range in each of the multiple spreadsheets, appending them by the column names which should be the same on each spreadsheet. When the last spreadsheet is consolidated you are ready to begin using the multiple data sources in the PivotTable.

Read more ►

How to Delete Duplicates in Excel

1. Place your list into Excel. Whether you type it or import it from another location, make sure it is in Excel and each entry is in its own row. This will let the program automatically delete duplicate entries.
2. Click the 'Data' tab on the top of the screen. This will bring up the 'Data' menu. Look for the 'Data' tools section of the toolbar.
3. Select the range of cells containing duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data.
4. Click on the 'Remove Duplicates' button. A dialog box will come up asking which column you want to remove the duplicates. If there are data in adjacent rows or columns, Excel will ask if you want to include that data. Make sure the correct column is chosen. If you are looking to delete a duplicate that corresponds to an entire row, make sure you only choose the one row or column with the data you want to delete.
5. Click 'OK.' The duplicate data will be deleted and a dialogue box will appear informing you of how many duplicate entries were deleted and how many unique values remain. Click 'OK' to return to the spreadsheet to continue working.

Read more ►

How to Add a Trend Line on an Excel Spreadsheet

Excel 2003
1. Create a chart from the data with which you want to make a trend line. Press the 'F11' key to create a chart using your default chart type, or click the 'Chart Wizard' button on the 'Standard' toolbar and follow the steps to create a basic chart.
2. Select the data series in the chart from which you want to create a trend line.
3. Go to the 'Chart' menu and select 'Add Trendline' The 'Add Trendline' dialog box will open.
4. Go to the 'Type' tab. Select the type of trend line you want to use and click 'OK.'
Excel 2007 or 2010
5. Open the Excel spreadsheet with the data from which you want to create a trend line. Select the data and make a basic chart. Press the 'F11' key to create a chart using your default chart type, or go to the 'Insert' tab and select a bar or column chart type in the 'Chart' group.
6. Click on the chart area to activate Excel's 'Chart Tools.'
7. Go to the 'Layout' tab under 'Chart Tools.'
8. Click the 'Trendline' button in the 'Analysis' group. Select the type of trend line you want to add.

Read more ►

How to Master Excel 2007 Pivot Tables

1. Open or create an Excel spreadsheet. The sheet needs to contain 20 or more rows of data plus column headings, which are required to create pivot tables.
2. Select the data you want to include in your pivot table. Either highlight the data before creating the pivot table or select the data during the pivot table creation process (See Step 3.)
3. Select the “Tables” group and choose “Insert.”
4. Select “PivotTable” and select “PivotTable” again.
5. Follow the prompts to create a pivot table. If you want to use data from an external source, choose “Use an external data source.”The default is to use data from the existing spreadsheet. Type in the cell range you want to include (not needed if you highlighted data beforehand) and finish the pivot table wizard to create a pivot table along with a provided field list based upon the range you chose.
6. Drag field names to the corresponding sections in the field list to organize the data you want summarized. The four main areas include Report Filter, Column Label, Row Label and Values.
7. Check each field name you want added to the pivot table. If you did not drag a field name to an area name, right-click the field and choose the area you want to add it to.
8. Practice adding fields to different areas of the pivot table to master the purpose of each area and how a pivot table is laid out.
9. Go to the “View” menu in the pivot table field list to change how the pivot table field list is viewed.
10. Change the way data are summarized by right-clicking a field heading or section in the pivot table and choosing “Field Options.” This includes changing various calculations such as average, count and sum.

Read more ►

How to Save a Workbook With a Password in Excel

Password Protect Workbook
1. Double-click on the Excel document to open it up.
2. Click on the 'File' tab at the top of the screen. Select 'Info' from the menu that covers the left side of the window.
3. Click on the 'Protect Workbook' button in the 'Permissions' area of the screen. Choose 'Encrypt with password' from the list of options. Enter in your desired password and click 'OK.' Enter the password again to confirm it, and press 'OK.'
Protect Document Elements
4. Double-click on the Excel file to open it up. Select the worksheet that you want to protect from the list of worksheets at the bottom of the Excel window.
5. Select the 'Review' tab at the top of the screen. Locate the 'Changes' area of the ribbon and select either 'Protect Sheet' or 'Protect Workbook.' Protecting the sheet will allow you to lock down the particular worksheet you are on from any changes. Protecting the workbook will prevent anyone from making changes to the overall design of the document, like adding new worksheets or altering the name of a current worksheet.
6. Place check marks next to the items that you want to allow people to alter. Anything without a check mark will be locked down. If you chose 'Protect Workbook,' do the opposite, as a check mark will signify to lock down that aspect of the workbook.
7. Type in a password into the field at the top of the window. Press 'OK' to continue. You will have to enter the same password again, and press 'OK' again to lock down the document.

Read more ►

How to Move a Column in Microsoft Excel

1. Open the Excel workbook to the worksheet containing the column you want to move. If you are moving it to a different workbook, open the second workbook to the target worksheet as well.
2. Click in the column header for the column you want to move. Use the built-in Excel column header with the pre-assigned letter, not a user-entered column header. Clicking the built-in header will select the entire column.
3. Press the 'Ctrl' and 'X' keys simultaneously to cut the column from its original location.
4. Click the column header to the right of where you want the column inserted, whether it is in the same worksheet or elsewhere. This will highlight the column you just clicked.
5. Press 'Ctrl' and 'V' simultaneously. This will paste the column to the left of your highlighted area, moving it to your target location.

Read more ►

How to Create a Curved Line Graph in Excel or Word

Microsoft Excel
1. Open Microsoft Excel. Highlight the data you want to insert in a graph.
2. Click the 'Insert' tab from the menu at the top of the page. You can insert pictures, clips and graphs here.
3. Look to the 'Charts' section on the drop-down menu. Select 'Line' and then choose either a 2-D or a 3-D line chart depending on your preferences. Your data will appear in a line graph.
4. Right-click on the line in the graph and select 'Format Data Series.' This is where you will choose options to make the line in the graph curved.
5. Select 'Marker Line Style' from the options at the right of the box that opens. Check the box that says 'Smoothed line' and click 'Close.' You will now have a curved line graph on your spreadsheet.
Microsoft Word
6. Open Microsoft Word. Click 'Insert' from the toolbar at the top.
7. Select 'Chart' and when the charts options box opens, choose 'Line' and click 'OK.'
8. Enter the data for the chart in the spreadsheet that appears. After you enter the data, close out the spreadsheet. The data will be applied to the graph on your document.
9. Right-click on a line in your graph and select 'Format Data Series.' Select 'Marker Line Style' and check the box that says 'Smoothed line.'
10. Click 'Close.' You will now have a curved line graph on your Word document.

Read more ►

How to Zip an Excel File

1. Open 'My Documents' in Microsoft Windows XP or earlier. If you are running Windows Vista, open 'Documents.' Both of these are usually found on the 'Start' menu.
2. Navigate through the folders until you locate the Excel file you want to zip.
3. Right-click on the file icon or name you want to zip and select 'Send To' from the pop up menu that appears.
4. Select 'Compressed (zipped) folder' from the sub-menu. A new zipped file will be created in the same folder. It will keep the same file name, but with a '.zip' extension, as your Excel file. This is the default, but you can change it just like any other file name.

Read more ►

How to Use Excel for Correlation of Data From Three Sensors

1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.
2. In column A, in cells A1 to A10, enter the following numbers:
0.33
0.05
0.77
0.90
0.25
0.66
0.30
0.12
0.05
0.85
3. In column B, in cells B1 to B10, enter the following numbers:
0.54
0.18
0.29
0.01
0.60
0.05
0.01
0.89
0.02
0.36
4. In column C, in cells C1 to C10, enter these numbers:
0.32
0.95
0.50
0.52
0.91
0.75
0.83
0.01
0.11
0.15
5. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then check the box next to 'Analysis ToolPak.'
6. Click on the 'Tools' menu selection again, then scroll down to 'Data Analysis.'
7. Click on 'Data Analysis,' scroll down to 'Regression,' then click 'OK.'
8. Put the cursor in 'Input Y Range' then shade cells A1 to A10.
9. Put the cursor in 'Input X Range' then shade cells B1 to C10.
10. Click on 'OK' and the spreadsheet will produce a regression output data table automatically.
11. Look under regression statistics. The third number down is called 'Adjusted R Square.' This number represents the correlation of these three columns of data, which in this case are slightly negatively correlated with a value of -.16.

Read more ►

How to Copy and Paste Multiple Cell Contents in Microsoft Excel 2003

1. Highlight the cells that you wish to copy. Simply left-click on a cell and hold the mouse button as you drag the cursor over the multiple cells you wish to copy. Then let go of the mouse button and the cells will stay highlighted.
2. Copy the cells. To do this, scroll to the “Edit” tab on the command bar and select “Copy.”
3. Highlight the cells you wish to paste into. Find the group of cells that you wish to paste into; they must number the same as the cells you copied. Highlight the cells that you wish to paste into by following the directions in Step 1.
4. Paste into the cells. To paste your cells into the new cells, scroll to the “Edit” tab on the command bar and select “Paste.”

Read more ►

How to Create a Mirrored Image in Excel

1. Select or create the image you wish to manipulate in just about any software program you want. The image can be simple or intricate, homemade or an object that you found in an obscure photo album.
2. Click the image so that the entire object is highlighted. This will ensure that whole image will be copied to Excel.
3. Right click and select 'Copy.' Open or go to the Excel window and click on the cell where you want the image to be.
4. Pick 'View' from the top of the Excel window. If there is not a check mark next to the Drawing toolbar option, click 'View,' 'Toolbars' and 'Drawing.' A separate toolbar along the side, bottom or top of your window will appear.
5. Choose the 'Draw' option on the Drawing toolbar. In most versions of Excel it has an arrow pointing up or down next to it. A pop up window will appear next to it. Click 'Rotate or Flip' and then 'Flip Horizontal' or 'Flip Vertical' depending on how you want or image to look.
6. Drag the outer corner of your image inward or out to change the size.

Read more ►

How to Construct a Categorical Frequency Table in Excel 2007

1. Enter your data into one column. For example, if you have a list of 10 house prices that you want to categorize, enter them into cells A1 to A10.
2. Enter your bin values into the next column. In our example, you would enter your bin values (for example, 100,000, 200,000, 300,000) into column 2. Bin values are optional: If you do not enter values for bins, Excel will choose bins for you.
3. Click on 'Data Analysis' from the 'Data' tab.
4. Click on 'histogram' from the list box.
5. Enter the range for your chart data into the 'Input Range' box. For example, enter 'A1:A10.'
6. Enter the range for the bin values into the 'Bin Range' box. For example, if you have four bins in cells B1 to B4, enter 'B1:B4.' If you also want to display a histogram, check the 'Chart Output' box.
7. Press 'OK.' Excel will enter the frequency table into the Excel worksheet.

Read more ►