How to Make a Basic Bookkeeping Spreadsheet in Excel

1. Examine your personal or business finances to determine the best way to use Excel. Different options are keeping track of accounts receivable and payable, including payroll, rent or utilities and any raw materials used in the production process.
2. Open Excel on your computer and create a new spreadsheet by clicking on the blank page icon at the top left of your screen.
3. Orient yourself with Excel's format. The page is made up of a grid which creates boxes. Information can be typed into each box.
4. Title the headings for your spreadsheet's rows and columns. For example, you may choose to use the first column to list all the dates in a given month, the second column to list any accounts receivable transactions and the third column to list any accounts payable transactions.
5. Fill in the information into the appropriate boxes. For example, if you or your company paid out $15,000 in payroll checks on the first of the month, you would write '15,000' in the corresponding box.
6. Add the total dollar amounts from each column -- in our example, you'd have a total for accounts receivable and a total for accounts payable. You can also calculate average expenditures and profits by selecting the 'Subtotals' option from the drop-down box under the 'Data' tab.
7. Sort information from high to low or low to high by clicking on the 'Sort' option from the drop-down box under the 'Data' tab; this gives you the option of seeing which entries had the highest or lowest values.

Read more ►

How to Paste Into PowerPoint and Remove Gridlines

1. Select the text, image or object that you want to include in your PowerPoint presentation. For example, highlight a range of cells in an Excel spreadsheet. Press 'Ctrl C' on your computer's keyboard.
2. Press 'Ctrl M' in PowerPoint to create a new slide and then press 'Ctrl V' to paste the object. If you copied a spreadsheet, for instance, it will paste in as a spreadsheet object. Note, that you can link or embed things such as spreadsheets, charts and Word documents.
3. Click on the 'View' tab in PowerPoint and click in the check box next to 'Gridlines' to remove the gridlines from the design view. If you want to remove the gridlines from a spreadsheet that you've pasted in, click inside the spreadsheet in PowerPoint. Press 'Ctrl A' to select all of the content in the spreadsheet. Click on the 'Table Tools: Design' tab and in the 'Table Styles' section click on the 'Borders' button and choose 'No Borders.'

Read more ►

How to Assign a Macro to a Cell in Excel

1. Right-click the sheet tab you want the change to happen in. The sheet tabs are at the very bottom of the worksheet.
2. Click 'View Code.'
3. Cut and post the following code into the worksheet:Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Address = '$A$1' ThenRange('A10') = 'Your text here'End IfEnd Sub
4. Change the code to suit your needs. The above code writes 'Your text here' into cell 'A10' when cell 'A1' is clicked. Change the 'A1' in 'Target.Address = '$A$1'' to the cell you want to click, then change Range('A10') = 'Your text here' to indicate the cell you want the event to happen in and the text you want to appear.

Read more ►

How to Create Frequency Relative Frequency on Excel Using a Pivot Table

1. Gather all of your data and compile it into a list on Microsoft Excel. Select and highlight the entire list of data points. Right click it and choose the option 'Sort from smallest to largest.' Now that the data points are in numerical order, the task of creating a frequency distribution table will be much easier.
2. Determine the interval size and the number of classes that will be used for your distribution table. For example you may have something like 5 classes of intervals: 1-5, 5-10, 10-15, 15-20, and 20-25. These classes will be used to organize the data points.
3. Start setting up the table on Microsoft Excel. In column A, set up the different classes and label the column as 'Class.' Column B will be for the 'Frequency.' Count up the number of data points that fall in each class interval, and state the frequency in column B. Select the empty cell below the list of frequencies in Column B and use the sum function to add up the values. This result will give you the total number of data points. In column C, set up the 'Relative Frequency.' Relative Frequency is calculated simply by dividing the individual frequencies by the total number of data values. Select an empty cell at the end of the 'Relative Frequency' column and perform the 'sum' function. The sum for the 'Relative Frequency' column should be 1.00.
4. Select the 'Insert' tab on Microsoft Excel, and select the PivotTable button. Use the selection icon and select the entire table. Place a check next to 'New Worksheet,' so that the table appears on a separate sheet. Click 'OK.' A column in the right side of the screen will appear. Place a check next to the following fields that you'd like to add to your report: 'Class,' 'Frequency,' and 'Relative Frequency.'
5. Use the 'Options' and 'Design' tabs to edit and format the PivotTable.

Read more ►

How to Open Excel Templates

1. Open Excel. Click 'File' if you are using Excel 2010 or click the 'Office' button if you are using Excel 2007. The Office menu will open.
2. Click 'New' on the Office menu. A window called 'New Workbook' will open. You can search for and open Excel templates here.
3. Look to the 'Templates' section on the 'New Workbook' menu. You can view recently used or installed templates here.
4. Click one of the Excel template categories to view an Excel template. Categories you can choose from are 'Blank and Recent' and 'Installed Templates.'
5. Select the template you want to open and click 'Create' or 'OK' depending on whether you are opening a recent template or an installed template. The template will open in a new workbook.

Read more ►

How to Compare Workbooks

Comparing Numeric Values
1. Open a new workbook, and name it CompareWorkbooks.xls.
2. Look at the values that you want to compare in both workbooks. For example, you may wish to compare cells B2 to F7.
3. In cell B2 (or the upper left cell) of the CompareWorkbooks workbook, insert the formula '=[Workbook1.xls]Sheet1!A1-[Workbook2.xls]Sheet1!A1.' If the Workbooks or sheets are named differently, change the formula as appropriate.
4. Copy the formula from that cell into all of the applicable cells in the CompareWorkbooks workbook. A '0' in a cell means that the cells in the two compared workbooks are identical. A value in the cell represents the difference between the values in the corresponding cells in the compared workbooks.
Comparing Almost Identical Workbooks
5. To compare workbooks that are virtually identical, open a new workbook, and name it CompareWorkbooks.xls.
6. Look at the values that you want to compare in both workbooks. For example, you may wish to compare cells B2 to F7.
7. In cell B2 (or the upper left cell) of the CompareWorkbooks workbook, insert the formula '=IF([Workbook1.xls]Sheet1!A1
[Workbook2.xls]Sheet1!A1,'DIFF VALUE',''). If the Workbooks or sheets are named differently, change the formula as appropriate.
8. Copy the formula from that cell into all of the applicable cells in the CompareWorkbooks workbook. If the words 'DIFF VALUE' appear in a cell, it means that the corresponding cells in the two compared workbooks are different.

Read more ►

How to Graph a Trend Analysis in Microsoft Excel

1. Enter the data in Microsoft Excel on which the trend analysis will be performed.
2. Create a chart from the data entered. The chart must be an unstacked, two-dimensional chart that is formatted as bars, columns or lines.
3. Click anywhere in the chart. This will display the Chart Tools and add the Design, Layout and Format tabs at the top of the worksheet.
4. Click Trendline under the Layout tab in the Analysis grouping. This will create a drop-down showing the types of trend analysis that can be performed on your data chart.
5. Select one of the predefined trend-line options. The options are linear, exponential, linear forecast and two-period moving average. This will automatically graph the trend analysis in the chart.

Read more ►

How to Calculate Total Number of Rows in MS Excel

1. Log on to your computer and open Microsoft Excel by clicking on the 'Start' button, choosing 'All Programs' from the menu and selecting 'Microsoft Office.' Choose 'Excel' from the list of programs, then click the 'File' menu and choose 'Open' from the menu.
2. Hold the 'Ctrl' key down and tap the 'End' key on your keyboard. This will automatically move the cursor to the last row in the spreadsheet that contains an entry. If you simply want to know how many rows are in your spreadsheet you can use that row number. If you need to know how many rows actually contain data, move on to the next step.
3. Position your cursor underneath the last row that currently contains data and type '=COUNT(' hit the up arrow on your keyboard, then the period key. Hit the up arrow again and continue pressing the up arrow until you reach the top of the spreadsheet. When you have reached the top of the spreadsheet type ')' to close the calculation. You will see the number of rows that contain data.
4. Type the formula directly if you are working with a large spreadsheet. If you are working with a spreadsheet that contains a large number of rows, it will be easier to enter the formula directly. To enter the formula directly type '=COUNT(firstrow:lastrow)' For instance, if the first row in your spreadsheet is A1 and the last is Z1, the formula would be '=(COUNT(A1:Z1).

Read more ►

How to Remove Passwords in Excel 2007

1. Open the password-protected Excel 2007 file by double-clicking it from Windows or clicking 'Open' from the Office menu at the upper left of the screen.
2. Attempt to modify the document by double-clicking a cell and attempting to change a value. An error message will appear and warn you about your attempted action.
3. Click the 'Review' tab at the top of the screen.
4. Click 'Unprotect Sheet' and supply the document's password when prompted. Click 'OK' to submit the password.
5. Click the Office icon and then click 'Save.' The document will be saved in its unprotected state, and a password will no longer be needed to modify it.

Read more ►

How to Turn Excel 2007 Documents to Excel 2003

1. Open the Excel 2007 file that you want to convert into Excel 2003.
2. Click the 'File' button in the upper left hand corner.
3. Highlight 'Save As,' and then select 'Excel 97-2003 Workbook' as your save option. This will save your spreadsheet as '.xls', which is the Excel 2003 format.

Read more ►

How to Write Macros in Excel 2003

1. Click 'Tools > Macros > Record New Macro...' in the top menu.
2. Choose a name for your macro and type it into the 'Macro Name' textbox. For example, if you want to create a button that sets a certain cell to a special format, you might name the macro 'Special Format Macro.'
3. Type the shortcut key you want to use to access the macro into the 'Shortcut Key' textbox. If the example, you might choose 'S' as your shortcut key.
4. Press 'OK.'
5. Make the changes you wish to record into the macro. For example, you could make the text of the cell bold and the borders green.
6. Press the stop button on the Stop Recording Dialog. The button has a blue square on it.

Read more ►

How to Label Category Axis (X) in Excel 2007

1. Open Excel 2007 and open a workbook. Click the 'Office' button and select 'Open.' Browse your files and locate the workbook you wish to use, and double click it.
2. Locate a range of data that contains row headers. Highlight the data and the row header information. The row header information will appear in the label category x axis. Click 'Insert' on the ribbon. Select a 'Line' chart to represent your data. The chart appears in your worksheet.
3. Click the outside border of the chart. The Chart Tools tab appears above the ribbon. Select the 'Layout' tab. Click the 'Axis Title' icon. A drop down list appears. Click the 'Primary Horizontal Axis Title.' Select 'Title below Axis.' Your horizontal or x axis is now labeled.

Read more ►

How to Create an Exploding Pie Chart in Excel

1. Start Microsoft Excel 2007 and open an existing spreadsheet that creates a pie chart or create a pie chart from existing data in the spreadsheet.
2. Click on the pie chart to select the entire pie chart. Be careful to click in the white area of the chart so the entire pie chart is selected. You can tell the whole pie chart is selected because it will be surrounded by a light blue line.
3. Hover your mouse over the piece of pie that you want to explode. Your mouse cursor will turn into a 4-headed arrow.
4. Click and drag the slice of pie outwards, away from the rest of the pie in the chart. Notice the blue outline that will grow outside of the pie chart. This line represents how far away the pieces will explode from each other.
5. Release the mouse button. The pie chart will now be exploded with all of the slices of pie broken away from each other.
6. Click and drag the individual pieces of pie to their new location if you would like to manually adjust the exploding pie chart pieces.

Read more ►

How to Get Rid of Circular References in Excel

Excel 2003
1. Open the 'Tools' drop-down menu at the top of the screen if the 'Circular Reference' toolbar is not displayed, then click 'Customize.'
2. Click on the 'Toolbars' tab at the top of the window that appears, then check the 'Circular Reference' check box if it is not already checked. Click the 'OK' button and close the window.
3. Click on and highlight the first cell in the 'Navigate Circular Reference' box within the 'Circular Reference' toolbar.
4. Examine the formula in the highlighted cell carefully for errors; i.e., referencing the cell itself. If you find no such mistake that could be the cause of the circular reference message, click to the next cell in the 'Navigate Circular Reference' box. (If the word 'Circular' is shown in the status bar without referencing which cell, switch to another worksheet.)
5. Continue through each cell, reviewing and correcting any circular references present in each until the status bar no longer shows the word 'Circular.'
Excel 2007
6. Click on the 'Formulas' tab at the top of the window.
7. Click on the arrow on the 'Error Checking' button in the 'Formula Auditing' box.
8. Select the first cell listed in the submenu under 'Circular References.'
9. Examine the formula in the cell closely and look for self-references. If you find no cause of the circular reference in the cell, click to the next one in the 'Circular References' submenu.
10. Repeat the previous step and review each cell in turn until the words 'Circular References' disappear from the status bar.

Read more ►

How to Use Excel Query

Query a Database
1. Open the Excel worksheet into which you want to import data.
2. In Excel 2003, go to the 'Data' tab, point to 'Import External Data' and select 'New Database Query' to query a database. In Excel 2007 or 2010, go to the 'Data' tab and select 'From Access' in the 'External Data' group.
3. Select the database you want to query and click 'Open.' Select the table in this database from which you want to import data and click 'OK.'
4. Choose 'Table,' 'PivotTable Report' or 'PivotChart and PivotTable Report' under 'Select How You Want to View This Data in Your Workbook.'
5. Enter a range of cells in the current worksheet and click 'OK.' The query will import the database table you selected into the current worksheet.
Query a Web Page
6. Open the worksheet into which you want to import data from a web page.
7. In Excel 2003, go to the 'Data' menu, point to 'Import External Data' and select 'New Web Query.' In Excel 2007 or 2010, go to the 'Data' tab and click on 'From Web' in the 'Get External Data' group. The 'New Web Query' dialog box will open.
8. Enter the address of the web page you want to query in the 'Address' field and click 'Go.'
9. Click the arrow next to the table you want to import. If you do not see arrows, click the 'Show Icons' button on the 'New Web Query' toolbar. To import several tables, simply click the arrow next to each one.
10. Click 'Import.' Enter the range in the current worksheet where you want to place the data. Click 'OK.' The query will import the table or tables you selected into the current worksheet.

Read more ►