Excel

Browse » Home » All posts

Sunday, January 13, 2013

How to Separate the First Name Last Name in Excel

1. Open Excel 2010 and click the 'File' tab. Select 'Open.' Browse the files and locate a workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Highlight the column containing the combined first and last name. Right-click the column and select 'Insert' to insert a blank column. This will provide the column for the last name once the split has occurred.
3. Click the 'Data' tab and click 'Text To Columns.' The Text To Columns dialog box appears. Click 'Next' and check 'Space.' Click 'Finish' to split the first and last name in the workbook.

How to Use Indirect in Excel 2007

1. Start Excel 2007. Open the workbook containing a cell or range to which you refer in another cell.
2. Click inside the cell in which you want to refer to the first cell or range and enter the formula you want to use. For example, if you want cell A2 to contain the sum of cells E4 through E21, enter the formula '=SUM(E4:E2)' into cell A2.
3. Click inside the cell in which you want to indirectly refer to the same cell or range.
4. Click inside the Formula Bar. Type '=Indirect( )' and enter the cell reference containing the reference to the range or cell to which you want to indirectly refer. For example, if you want to refer to the sum of cells E4 through E21, which is entered in cell A2, the formula would be '=INDIRECT($A$2).'
5. Press the 'Enter' key. Save the changes to the worksheet. If you change the reference or contents of cell A2 (or whatever cell to which you are indirectly referring), the cell containing the indirect function will change to reflect the contents of that cell.

How to Make Lined Columns in Microsoft

Insert a Separator Line Between Columns in Word
1. Open the Word document.
2. Click the 'Page Layout' tab on the command ribbon.
3. Click the down-arrow for the 'Column's button in the 'Page Setup' group. A list of column formats appears.
4. Click the 'More columns' button. A 'Columns' dialog window displays a list of column formats.
5. Click the preferred format or type a value in the text box. For example, type '3' to set three columns.
6. Select the check box for 'Line Between.'
7. Click 'OK.' A vertical separator line appears between the columns.
Insert Line Borders for Excel Columns
8. Open the Excel worksheet.
9. Click the 'Home' tab on the command ribbon.
10. Click and drag the mouse to select a range of cells or a column. The column and row headers change color.
11. Click the down-arrow on the 'Borders' button in the 'Font' group. This button is located between the 'U' and paint can symbols. A list of border styles appears.
12. Click the preferred border style. For example, 'All Borders' outlines every cell. You can opt to draw borders for certain cells or columns.

Saturday, January 12, 2013

How to Use Excel to Balance a Checkbook

1. Open Microsoft Excel. Click cell “A1” and type “Date.”
2. Type “Check Number” in cell 'B1' and type “Description” in cell “C1.”
3. Click cell “D1” and type “Amount.” Type “Balance” in cell 'E1.'
4. Click the column “A” header at the top of your spreadsheet. Right-click the header and select “Format Cells” from the drop-down menu. Click the “Number” tab and click “Date” in the Category section. Select the format “3/14/01” in the Type section, and click “OK.”
5. Click the column “D” header and drag your mouse to column “E.” This action will select both columns. Click the dollar sign ($) located in the Number section on the “Home” tab.
6. Click cell “A1” and drag your mouse over to cell “E1.” Click the “Center” button in the Alignment section and the “Bold” button in the Font section. Click the arrow next to the “Border” button and select “Bottom Border.” If you have trouble finding a button, hover your mouse over a button to view a description.
7. Hover your mouse between column header “B” and “C.” Your mouse cursor will change to a vertical line with two arrows. Click your mouse and drag your mouse right to resize the column. Resize the column to 13.57 (100 pixels). Hover your mouse between column header “C” and “D.” Resize the column to 20.71 (150 pixels). Resize column D and E to 10.71 (80 pixels.)
8. Enter your beginning checking account balance in cell “E2.”
9. Enter the following formula in cell “E3” and press enter: =IF(A3,IF(ISNUMBER(FIND('Deposit',C3)),E2 D3,E2-D3),' ')
10. Click cell “E3” and hover your mouse over the lower-right corner of the cell. Your cursor will change to a plus sign ( ). Click the corner of the cell and drag your mouse down approximately 10 cells. This procedure copies the formula from cell “E3” and pastes it in the following cells. The formula adjusts to coincide with the row.
11. Enter the date of your first transaction in cell “A3” to activate the formula. If you omit this step, the register will not calculate your transaction.
12. Enter the check number in cell “B3,” if applicable. If the transaction does not involve a check, leave this cell blank.
13. Enter the transaction’s description in cell “C3.” If this transaction is a deposit, you must include the word “Deposit” in your description for the formula to recognize that the deposit is an addition to your balance.
14. Enter the amount of your transaction in cell “D3.” Cell E3 displays your current balance.

How to Increase the Height of a Row to Pixels in Excel 2007

1. Open an Excel spreadsheet. You will see on the left side of the spreadsheet that the rows are numbered (1, 2, 3, etc.).
2. Place the cursor on the line under the row you want to increase the height of. For example, if you want to increase the height of row 2, place the cursor on the line between row 2 and row 3. Note that the cursor changes from the usual arrow to what looks like a plus sign ( ).
3. Click your mouse and while continuing to hold it down, drag the row to increase (or decrease) to the height you want. When you click your mouse on the line between rows, you will see a small dialogue box appear that will say 'Height: 20.25 (27 pixels)'--or whatever height the row is.
4. To increase the height of several rows at once and to ensure they are all exactly the same size, place the cursor over the number of the first row in your selection. You will see that the cursor is now a black arrow. Do not place the cursor on the line as you did in Step 2.
5. While holding down the mouse, drag it to select as many rows as you want. Now perform Step 2.

How to Change the Default Owner Name for Microsoft Excel

Change owner name in Excel 2007
1. Launch Excel and click on the Windows icon at the top left-hand corner of the screen.
2. Select the 'Excel Options' button at the bottom of this window.
3. Click on the 'Popular' tab in the left column.
4. Change the 'User name' under the 'Personalize your copy of Microsoft Office' section at the bottom of the screen.
5. Click the 'OK' button at the bottom of the screen.
Change user name in earlier versions of Microsoft Excel
6. Launch the Excel and click the 'Tools' option at the top of the screen, then select the 'Options' choice.
7. Click on the 'General' tab.
8. Enter your desired user name into the 'User name' box.
9. Click 'OK' at the bottom of the window.

How to Automatically Use Absolute Cell Reference in Excel

1. Open Excel.
2. Click the empty box in the upper-left corner of the spreadsheet. This is the box that is to the left of column A and on top of row 1. This highlights every cell in the spreadsheet.
3. Click in the formula tab.
4. Press F4 to toggle to the absolute value reference. Since all cells are highlighted, this toggling will affect every cell in the spreadsheet.

Friday, December 28, 2012

How to Make a Monthly Budget on Excel

1. Click 'Start' > 'All Programs' > 'Microsoft Office' > 'Microsoft Office Excel.'
2. Click cell 'A1,' type 'Expenses,' click cell 'B1' type 'Amount,' click cell 'D1,' type 'Income' and then click cell 'E1' and type 'Amount Earned.'
3. Click cell 'A2,' type the name of a monthly expense (rent, car payment, food etc.) press the 'Enter' key, type the name of another expense and then continue in this manner until all expenses you plan to incur are listed in column A. Repeat this step, clicking on cell D2 and entering sources of income on each line instead of expenses (income from each job, tips, interest, gifts, etc.)
4. Click cell 'B2,' type the amount of money you plan to spend on the expense in cell A2, press the 'Enter' key, and then continue typing in the anticipated amount you will spend on each expense. Repeat this step, clicking on cell E2 and entering amounts corresponding with each source of income. You will now have lists of all of your planned expenses and sources of income.
5. Click cell B2, hold down the mouse button and then drag the mouse down to select the values of all planned expenses.
6. Click the 'Auto sum' button under the 'Editing' group on the ribbon (the main toolbar at the top of the Excel interface.). A total of all planned expenses will appear underneath column B.
7. Repeat steps 5 and 6, selecting cell E2 instead of B2. The total of all planned income sources will appear under column E2.
8. Click on an empty cell underneath the lists press '=' click on the cell containing the total of all income, press '-' and then click on the cell containing the total of all expenses and press 'Enter.' The cell will display the difference between planned income and expenses (the amount of money you will have left over after you pay all your planned expenses.).
9. Hold the 'Control' key, press 'S,' enter a name for the budget in the space provided and then click 'Save.'

How to Hide the Excel Pivot Table Data Area

1. Move the data you want to hide to the right. The easiest way to hide a large data field is to hide everything that lies on the right side of a selected column. Therefore, start by moving all the data you want to hide to the right. Click on 'Pivot Table Wizard' on the Pivot Table Toolbar. Now click on 'Layout.' Drag and drop the columns to arrange the data in such a way that all the data you wish to hide is to the right of the column(s) that will be visible and click 'OK.'
2. Click on the first column from the left. Now go to the Pivot Table Toolbar and click on the icon that looks like a list with a green plus sign on the side (fifth icon from the left). A new window, titled 'Show Detail,' will appear. Here, select only the fields for which you wish the data to show. If you select no fields at all, only the data in the very first column will show. You can also hide the data in the fist column if you so desire.
3. Click on the first column. Click the icon from the Pivot Table Toolbar that looks like a list with a red bar on the side (fourth icon from the left). The data in the column will now disappear. If you press the same button again, the data will reappear. This way, you can hide all the data in the data area of the Pivot Table. If you wish to keep all the column headings and hide the data beneath the headings, simply click on the fourth icon (which hides data in one column) once for each column.

Thursday, December 27, 2012

How to Add a Custom Menu to an Excel Toolbar

Adding a Custom Menu to a Toolbar
1. Open Microsoft Excel.
2. Display the toolbar that contains the menu where you want to add your command.
3. Open the Tools menu and select Customize.
4. Select the Commands tab.
5. In the Categories box, click New Menu.
6. Drag New Menu from the Commands box to the desired location on toolbar.
7. Right-click the new menu and select Name.
8. Type a name for the menu in the Name box. Press Enter.
9. Now add commands to the new menu, following the steps in the next section.
Adding a Command to a Menu
10. Open the Tools menu and select Customize.
11. Select the Commands tab.
12. Click your new menu on the toolbar. A box will appear below it.
13. In the Categories box, select the category for the command.
14. Drag the command you want from the Commands box to the new menu's box on the toolbar. When the menu displays a list of menu commands, point to the location where you want the command to appear on the menu on the toolbar, and then release the mouse button.

How to Convert Lotus 123 Files to Excel

From Lotus 123
1. Open your file in Lotus 123.
2. Go to the 'File' menu, select 'Save As,' and save it as a Microsoft 97 file (*.xls).
3. Open the resulting .xls file in Excel.
From Excel
4. Open Excel.
5. Locate the Lotus 123 file (with an extension of *.wk4 in most cases).
6. Click 'Open.' Excel will prompt you for approval before converting the file.
7. Go to the 'File' menu, and select 'Save As' to save a copy of the file as an Excel native format.

How to Create Diagonal Lines in a Cell in an Excel Spreadsheet

1. Open Microsoft Excel by double-clicking the Excel icon on your screen or selecting 'Programs' from the 'Start' menu and then selecting 'Microsoft Excel.'
2. Select the cell you would like to place the diagonal line in by clicking on it once. The cell will be highlighted with a bold border around it to indicate that it has been selected.
3. For Excel versions before 2007, move your cursor to the 'Format' pull-down menu and select the first item: 'Cells.' A pop-up box will appear named 'Format Cells.' Select the tab that says 'Border' within the box.
4. For Excel 2007 or more recent versions, within the 'Home' tab at the top of the page, click on the down-facing arrow next to the image of a box within the 'Font' section. This is called 'Format Cells.' This arrow will select a pull-down menu. Select the last entry: 'More Borders.' The 'Format Cells' pop-up box will appear within the 'Borders' tab.
5. Select either the left or right diagonal line amongst the line options surrounding the sample box. Your selection will show up as an example in this box.
6. Confirm your selection by pushing 'OK.' The box will close and you will to your spreadsheet with the diagonal line appearing in the cell.

How to Make a Sales Order Form in Excel 2007

1. Open a new workbook in Microsoft Excel 2007. Click on the 'Office' button on the top left hand corner and select 'New.'
2. Select the Microsoft Office Online under 'Templates' and type sales order in the search box. Hit 'Enter.'
3. Select a template such as 'Sales order (Simple Blue design).' Click on the 'Download' button.
4. Fill in information on the company name, slogan, date, invoice, customer ID, return address, and shipping address. In order to add the company logo, go to cell A1 and click on the 'Insert' tab in the toolbar. Click on 'Picture' and find the picture on your computer and click on the 'Insert' button.
5. Enter the following information pertaining to the order: salesperson's name, job, shipping method, shipping terms, delivery date, payment terms, due date, quantity, item number, description, unit price, discount, and sales tax. The line total cell is automatically calculated by multiplying the quantity by the unit price, and subtracting any discounts. The total discount cell, subtotal, and total cells are also automatically calculated using formulas.

How to Insert Bullet Points Into Excel 2007

1. Select the cell or cells in which you want to insert bullet points. The cells can be empty, or they can have text already entered.
2. Click the 'Format' button, which is found in the 'Cells' group under the 'Home' tab. Scroll to the bottom of the list that appears, and under 'Protection,' choose 'Format Cells.'
3. Click 'Custom' under the 'Number' tab. If the selected cells are empty, the '@' symbol will appear in the 'Type' box. If there is already text in the cells, there will be text in the 'Type' box. Scroll down in the 'Type' menu until you see the '@' symbol, and select it; it will replace whatever text is already in the box.
4. Place the cursor before the '@' symbol in the 'Type' box. Hold down the 'Alt' key, and type 0149 on the number pad. Release the 'Alt' key; a bullet will appear before the @ symbol. Insert a space between the bullet and the @ symbol if you want a space between the bullet and the text in the spreadsheet. Click 'OK' to exit the 'Format Cells' menu.
5. Enter text into the selected cells. A bullet will appear in front of any text entered. If there was already text in the cells, the bullet point will appear before it.

How to Use Excel Spreadsheet for Bills

1. Select cell 'A1,' located in the top-left corner of the Excel 2010 worksheet.
2. Type 'Bill Type' into the the cell. This column will contain the name of the bill, like 'Rent' or 'Electricity.' Select the cell to the right of the first one and enter in 'Bill Amount.' Continue moving one cell to the right and enter in 'Bill Month,' 'Due Date,' 'Amount Paid' and 'Date Paid.' You can also add more fields if there is some other aspect of each bill that you want to track.
3. Select cell A2. Enter in the bill type for the first bill you want to track. Then move to the cell directly to the right, and enter in the bill amount. Continue until you have entered all the available information for that bill. Leave the 'Amount Paid' and 'Date Paid' fields blank until you have paid the bill.
4. Enter another bill's information into row 3. Continue until you have all your bill information for the month entered in.
5. Select cell A1 and hold the mouse button down. Move the mouse to the rightmost cell in the last row of the information that you entered and release the button. Select the 'Insert' tab at the top of the window and click the 'Table' button. Select 'OK' to build the table. This will format your information to make it readable and provide drop-down arrows that will let you narrow down your information.
6. Right-click anywhere in the table, move your mouse over 'Table' in the pop-up menu and choose 'Totals Row' to add a summation row at the bottom of the table. Select the cell in the totals row under 'Bill Amount' and choose 'Sum' from the menu that appears. This will now give you the total amount for all the bills you entered into Excel. You can do the same thing for the 'Amount Paid' column, or any other column that contains numerical data.
7. Right-click the row number for the totals row and select 'Insert' if you need to add another bill to the field. This will create a blank row at the bottom of the table. Generally you will have to do this several times each month to add that months bills.
8. Select a drop-down arrow next to each header to narrow down the table to just include rows that contain certain entries. You can look at just bills of a certain type, or just bills from a certain month. The totals row will automatically update when you narrow down the table.

Subscribe to: Posts (Atom)