How to Calculate Business Quarters in Excel Not Based on Calendar Year

1. Open a new workbook in Excel and enter a list of random dates that can be used for testing.You can copy these example dates to your sheet beginning in cell A1:1/4/2010
2/11/2010
3/21/2010
4/28/2010
6/5/2010
7/13/2010
8/20/2010
9/27/2010
11/4/2010
12/12/2010
2. Determine the number value of the month in which your 'year' starts (e.g. January is 1 and November is 11). For this example, the year will start in October (month 8).
3. Subtract one from your month value. In this example, the result would equal seven (7).
4. Substitute your new value into the following equation:=MOD(CEILING(22 MONTH(
) -
- 1,3)/3,4) 1For this example that would be:
=MOD(CEILING(22 MONTH(A1)-7-1,3)/3,4) 1
5. Copy and paste the equation into cell B1 in your Excel worksheet.
6. Click and hold the mouse button down on cell B1 and drag your mouse cursor down to highlight all the cells from there to B10.
7. Hit the keys 'Control' ('Ctrl') and D at the same time to copy the formula down to all the cells.Your worksheet should now look like the following data:01/04/20102
02/11/20103
03/21/20103
04/28/20103
06/05/20104
07/13/20104
08/20/20101
09/27/20101
11/04/20102
12/12/20102

Read more ►

How to Copy Formulas Without Changing Them in Excel

1. Open your Excel document.
2. Click on the cell containing the formula you wish to work on.
3. Locate your cell references in the formula shown in the Formula Bar. These will be a letter followed by a number, such as A3 or D14.
4. Place a dollar sign ($) in front of the letter and number of the cell reference you don't want to change. Examples would be $A$3 or $D$14.
5. Copy your formula to the other parts of your spreadsheet--you'll see that Excel does not change your cell references.

Read more ►

How to Center Horizontally and Vertically in Excel

Center Horizontally
1. Click the cell or cells in which you want to center data horizontally.
2. Click the 'Home' tab.
3. Click the 'Center' button located in the 'Alignment' group. The Center button centers the text horizontally in the selected cell.
Center Vertically
4. Click the cell or cells in which you want to center data vertically.
5. Click the 'Home' tab.
6. Click the 'Middle Align' button located in the 'Alignment' group. The Middle Align button centers the text vertically, between the top and bottom of the cell.

Read more ►

How to Lock a Formula in Excel 2007

1. Select the cell containing the formula you wish to lock by highlighting it.
2. Click on the 'Home' tab, which is located on the top of the Excel window.
3. Navigate to the 'Cells' module, which is on the right half of the Excel window. Select 'Format.' A menu displaying cell formatting options will appear.
4. Navigate to the 'Protection' section of the format menu. Click on 'Lock Cell,' highlighting the lock icon.
5. Protect the sheet; until you do so, locking the formula cell will not take effect. Repeat steps 2 and 3. Select 'Protect Sheet' from the cell formatting menu. A dialog box titled 'Protect Sheet' will appear.
6. Select the 'Protect worksheet and contents of locked cells' option. Type in a password that users must enter to unprotect the sheet into the password field; this is not a required step. In the 'Allow all users of this worksheet to' section, choose privileges that you wish users to have when working with the sheet. Finish by clicking 'OK.'

Read more ►

How to Do Descriptive Statistics in MS Excel 2007

Analysis ToolPak
1. Install the Microsoft Office Analysis ToolPak. Click the 'Microsoft Office Button' and then 'Excel Options.'
2. Click 'Add-ins' and then 'Excel Add-ins' located in the Manage box. Click 'Go.'
3. Select 'Analysis ToolPak' in the Add-ins Available box and click 'OK.' If you do not see the Analysis ToolPak option, click 'Browse' to locate it. Click 'Yes' to install it if a prompt signifies that it is not installed on your computer.
Descriptive Statistics
4. Collect the data you would like to analyze. The descriptive statistics tool will be used later to organize and interpret the data for you.
5. Open Excel to generate a new spreadsheet.
6. Type the label of your first column in cell 1A. Enter the data values that you are evaluating in the cells directly below your label. For example, a real estate professional wishing to analyze prices of homes might label column 1A Sale Price. Thereafter, the various sale prices will be keyed into cells 2A, 3A, 4A and 5A.
7. Save the file so that you do not lose your data. Click on 'File,' 'Save.' Type in the file name and click 'Save.'
8. Select 'Tools,' 'Data Analysis.' This can be found under the Data tab. Click 'OK' to open the dialog box.
9. Choose 'Descriptive Statistics' in the dialog box. This is the tool you want to use. Click 'OK.'
10. Click on the small chart box located to the right of the input range. Highlight the spreadsheet column containing the data that you want to summarize. Do this by holding down the left mouse button and highlighting all of the data in the column you want to select. This data will be placed in your input range. Click on the small chart box again to return to the Descriptive Statistics box. If you also highlight the label, click on 'Labels in First Row' on this screen.
11. Click 'Output Range' and indicate to which cell you want the results to go. For example, choose cell H1 by typing 'H1' in the output range.
12. Select 'Summary Statistics' and click 'OK.' The descriptive statistics are now generated. Double-check the count in the results to make sure it included the correct number of items from your list of data.

Read more ►

How To Calculate Mortgage Payments in Excel

1. Start a new, blank workbook in Excel. In Excel 2007, click the 'Office' button and click 'New'; then click 'Microsoft Online.' In Excel 2003, go to the 'File' menu and click 'New'; then go to the 'Templates' section of the task pane.
2. Type 'mortgage calculator' in the 'Search Microsoft Online' box and click 'Go.' A list of possible templates will appear.
3. Select the 'Mortgage Payment Calculator' template and click the 'Download' button. The Mortgage Payment Calculator template will open as a new Excel spreadsheet.
4. Enter the mortgage loan amount in cell C7. Enter the interest rate in C8. Type the number of years of the mortgage loan in cell C9. The worksheet will calculate the monthly payment amount, as well as other data, such as the number of payments and equity.
5. Enter up to five different mortgage-loan amounts into columns C through G. This will make it easier to compare options, such as a 15-year mortgage vs. a 30-year mortgage, or different interest rates or loan amounts.

Read more ►

How to Prepare an Excel PivotTable

Using Excel Data
1. Open the Excel file that has the information that you want to make into a PivotTable.
2. Edit the range of data that you will use to remove any blank rows. Also place a column name in the top row, for any column that doesn't have one.
3. Select any cell in the range of data that you want to use.
4. Click on the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button that appears on the toolbar. A small window will appear, and Excel will automatically select the entire range of data. Press 'OK' to continue, and the PivotTable will be created on a new worksheet.
Using External Data
5. Open the Excel 2010 program by double-clicking the Excel icon.
6. Click the 'Insert' tab at the top of the screen and then click the 'PivotTable' button on the left side of the toolbar.
7. Select the 'Use an External Data Source' radio button and then press the 'Choose Connection' button just below the radio button. A list of available connections will show up.
8. Click the connection you want to use and then click 'Open.' If you do not see the connection that you need, click on 'Browse for more' and then navigate to the connection file and click 'Open.'
9. Select 'New Worksheet' to place the PivotTable on a new worksheet, or select 'Existing Worksheet' and then input the cell where you want the PivotTable to go. Click 'OK' when you are done.
Placing Information in the PivotTable
10. Select a cell anywhere in the PivotTable placeholder graphic to bring up the field list on the right side of the screen.
11. Place checkmarks on the field list to the right side of the window, next to the fields you want to add to the PivotTable. Each field that you see is equal to one column of data from your original data set. When you place a checkmark, you will see the field appear both on the PivotTable in the main window, and in one of four boxes to the right of the field list. Each box corresponds to a different area on the PivotTable.
12. Drag and drop fields between the boxes on the right of the screen to place them where you want them on the PivotTable. Fields in the 'Column Labels' box will appear at the top of the PivotTable, while fields in the 'Row Labels' box will appear along the left side. Any field in the 'Values' box will have its data make up the body of the PivotTable, and any field in the 'Report Filter' will appear in a small drop-down box above the PivotTable, where you can filter the entire table.

Read more ►

How to Turn Off Sharing in Excel 2007

1. Launch Microsoft Excel and locate the 'Review' tab on the ribbon located on the top of the screen.
2. Click the 'Share Workbook' tool located in the 'Changes' group to launch the 'Share Workbook' dialog box. You should see a check mark next to the 'Allow Changes' check box.
3. Click the 'Allow Changes' check box to clear the check mark. Click 'OK' to save your changes and disable sharing.

Read more ►

How to Interpret the Linear Regression Summary in Microsoft Excel 2003

1. Right-click on the regression line in your chart, and choose Properties. Check 'Display equation on chart' and 'Display R-squared value on chart'. Click OK.
2. Look at the R-squared value displayed next to the regression line. The R-squared value represents the amount of variability in the data that is explained by the linear regression analysis. If all the data lies exactly on the regression line, the R-squared value will be 1. If the R-squared value is 0, that means there is no correlation between the two datasets.
3. Turn your attention to the equation listed above the R-squared value. It will be of the form 'y = m x b', where m and b have been replaced by numbers. This equation describes the linear regression line. The 'm' value is the slope of the line, and the 'b' value is the location where the line crosses the vertical axis. You can use this equation to predict values in the dataset based on their value on the horizontal axis; just multiply their horizontal location by the 'm' value and then add the 'b' value to the result; this will give you the best estimate of the location of that point based on the linear regression analysis.
4. Look at the slope of the line. If it slopes downwards to the right, the data is 'negatively correlated,' if it slopes upward, the data is 'positively correlated.' Positive correlation means that the datasets tend to agree with or reinforce each other; negative correlation means that they tend to be at odds or mutually exclusive.

Read more ►

How to Create an Excel Spreadsheet to Figure Out Sick Leave

1. Type 'Vacation and Sick Leave Record' in cell E1. In cell E2, write the period that the sick leave worksheet is applicable to. For example, write '1/1/2010 to 12/30/2010.'
2. Type the employee's name in cell A4. Optionally, type their maximum sick leave accumulation on the next line in days or hours.
3. Type the word 'Month' in cell A6. Type the months January through December directly underneath, in the same column, with one month per row.
4. Type the word 'Amount' in cell B6, the word 'Used' in cell C6 and the word 'Balance' in the cell D6.
5. Enter the total number of the employee's available sick leave hours in cell B7. For example, if the employee has 120 hours, write '120.'
6. Type the following formula into cell D7:=B7-C7.Copy the formula to cells D8 to D17 by dragging the fill handle (the little black square in the bottom right corner of the cell) to cell D17.
7. Click on cell B8, type '=', then click on cell D7. This transfers the balance from the previous month to the 'amount' column for the beginning of the second month. Drag the fill handle of cell B8 to cell B17.

Read more ►

How to Password Protect Excel 2003

1.
In order to protect your Microsoft excel worksheet go to TOOLs menu select PROTECTION. From there you should see a menu like the picture on your left.
2.
From there you will be given a series of options that will allow you to protect a Microsoft excel worksheet, workbook or a range on your specific file. As you can see from the image on your left you can get very granular with this.
3. Make your appropriate adjustments and then click OK. You should be prompted to retype the password. Once that is done make sure you save your changes and close the document and reopen it to see if your password took.

Read more ►

How to Create a Box Plot in Microsoft Excel 2007

Set Up the Plot Data
1. Create a table with a column for each data set. In the table rows, add formulas for the calculations of (in order) the minimum, first quartile, median, third quartile and maximum for each data set using the Excel functions MIN, MAX, MEDIAN and QUARTILE (or PERCENTILE). This is the 'summary table.'
2. Create a second table with the same rows and columns as above. This table will contain the values used for the plot. This is the 'plot data table.'
3. Add a formula for each data set's maximum value in the plot data table that is the maximum minus the third quartile values from the summary table.
4. Add a formula for each data set's third quartile value in the plot data table that is the third quartile minus the median values from the summary table.
5. Add a formula for each data set's median value in the plot data table that is the median minus the first quartile values from the summary table.
6. Add a formula for each data set's first quartile value in the plot data table copying the first quartile value from the summary table.
7. Add a formula for each data set's minimum value in the data table table that is the first quartile minus the minimum values from the summary table.
Create the Plot
8. Select the range containing the third quartile, median and first quartile of all the data sets in the plot data table.
9. Open the Insert ribbon. Click 'Column' on the charts. Select 'Stacked Column' from the '2-D Column' type charts.
10. Click 'Select Data' under 'Design' on the 'Chart Tools' section of the ribbon. Modify the 'Series' order so 'Series 3' is the bottom segment on the chart and 'Series 1' is the top segment on the chart.
11. Click on the bottom segment of one of the columns in the chart. Select 'Layout' under 'Chart Tools' on the ribbon. Click 'Error Bars' and select 'More Error Bars Options.' The 'Format Error Bars' window appears.
12. Select 'Minus' for the 'Direction.' Select 'Custom' for the 'Error Amount.' Click 'Specify Value' and select the range for all the minimum values in the plot data table for the 'Negative Error Value' in the 'Custom Error Bars' window. Click 'OK' to exit this window and 'Close' on the next window to return to the chart.
13. Click on the top segment of one of the columns in the chart. Go to the 'Format Error Bars' window as above. Choose 'Plus' for the 'Direction' and use the maximum values in the plot data table for the 'Positive Error Value' range for the 'Custom Error Amount.'
14. Right-click on the bottom segment of one of the columns on the chart and select 'Format Data Series.' Set the 'Fill' to 'No fill.' Set the 'Border Color' to 'No line.' Close the window.
15. Delete the chart legend. Add polish by formatting the chart colors, adding a title and other finishing touches.
16. Select the rows containing the plot data table. Under 'Home' on the ribbon, click 'Format' and select 'Hide Rows' under the 'Hide Unhide' sub-menu.

Read more ►

How to Do Percentages With Excel 2003

1. Open Excel 2003, and open a workbook that contains a column with amounts and another column with totals. Click 'File' on the menu bar, and click 'Open.' Browse your files, and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click in the next available column in your workbook. Type '=.' Click in the first cell that contains the first amount. Type '/.' Click in the first cell that contains the first total, and press the 'Enter' key. A value is generated.
3. Highlight the column containing this new formula. Click the '%' symbol on the standard toolbar. Your value is transformed into a percentage.

Read more ►

How to Maximize a Sheet in Excel 2007

1. Open an Excel worksheet by clicking on the round 'Office' button in the top left-hand corner of Excel 2007. Select 'Open' from the drop-down menu on the left. Choose the file name of the Excel document that you wish to open.
2. Click on the 'maximize' button in your Excel work window. You'll find it just to the left of the 'close' button in the top right corner. An 'X' identifies the close button, and a square, the maximize button. The maximize function changes your worksheet to maximum size within your open Excel document.
3. Click on the 'restore down' button to make your worksheet smaller. The restore down function changes your worksheet to smaller size within your open Excel document. You'll find the restore down button in the same location as the maximize button. The two buttons function as a toggle: When you have maximized the sheet, the button represents restore down.

Read more ►

How to Sort and Filter in Microsoft Excel

1. Open Microsoft Excel and go to 'File' and 'Open' to open the document containing data you want to sort and filter. Highlight the document and click 'Open.' The document will open in a new window.
2. Highlight the data you want to sort. You may select an entire worksheet, a column, multiple columns or specific data within a column.
3. Complete a simple sort by selecting the 'Home' tab from the ribbon and from the 'Editing' group selecting 'Sort and Filter.'
4. Click the appropriate option that matches your preferred sort. For example, if your data is a group of dates, click 'Sort Oldest to Newest' or 'Sort Newest to Oldest.' If your data is text, select either 'Sort A to Z' or 'Sort Z to A.' If your data consists of numbers, select 'Sort Smallest to Largest' or 'Sort Largest to Smallest.' The sort will occur upon clicking this command.
5. Complete an advanced custom sort by selecting 'Editing', 'Sort and Filter' and 'Custom Sort.' Select the appropriate options for your sort from options that include columns, value or order---with or without the column headers. You may also sort by multiple levels. For example, your data may be sorted alphabetically at the first level and by date at the second level.
6. Filter your data by selecting 'Home,' the 'Editing' group and 'Filter.' A drop-down arrow appears next to the first cell of data. Click the drop-down and uncheck any data that you want to filter out. You may perform a sort based on the data that remains.

Read more ►