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.'
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

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.
Read more ►

How to Define Names Labels in Microsoft Excel 2003


1. Open the Microsoft Excel 2003 program and then click the 'Tools' option from the top toolbar menu.
2. Click the 'Options' button and then click the 'Calculations' tab. Select the box next to the 'Accept labels in formulas' field, and then close out of the dialog box.
3. Use your mouse to select a range of cells in your worksheet that you want to define names for.
4. Click the 'Name' field from the left side of the formula bar. Enter the name you want for the group of cells.
5. Press the 'Enter' key on your keyboard, and the names will be added.
Read more ►

Wednesday, December 26, 2012

How to Toggle a Grid in Excel 2007


1. Press down the 'Alt' key on your keyboard.
2. Press the letter 'W,' then the letter 'V,' then the letter 'G.' Press them one at a time: don't try to press them all together at once. This keyboard action will make your gridlines disappear if you have them and will make them reappear if they are not showing.
3. Repeat steps 1 and 2 to toggle the gridlines between showing and not showing.
Read more ►

How to Create Excel Spreadsheets in HTML


1. Click on 'File' in Excel 2003 or the 'Office' button in Excel 2007.
2. Click on 'Save As Web Page' in Excel 2003. In Excel 2007, choose 'Other Formats,' then choose 'Web Page' from the 'Save as Type' box.
3. Choose a save location from the 'Save In' list. For example, click on your Desktop.
4. Type a name for your Web page in the in the 'File name' box.
5. Click on 'Publish' twice. In Excel 2003, select the item you want to publish in the 'Choose' box (Excel 2007 does not require this extra step). Excel will save your workbook as an HTML Web page.
Read more ►

How to Sort Information by Date in Excel 2003


1. Open Excel 2003 by double-clicking the icon. Click 'File,' then 'Open' and select the spreadsheet you want to work with.
2. Click on the top left cell of the data you want to sort. Hold down the mouse button, and drag the cursor to the bottom right box, highlighting all of the data.
3. Click the 'Data' button, then select 'Sort' from the drop-down menu that appears.
4. Click the small arrow by the 'Sort By,' and select the column that holds all of the dates. Choose 'Ascending' if you want older dates to appear first or 'Descending' if you want the newest dates to appear first. Click 'OK,' and Excel will sort the information provided by date.
Read more ►

Tuesday, December 25, 2012

How to Separate Cell Data in Excel


1. Start Microsoft Excel and open an existing workbook that contains cells with data you'd like to separate.
2. Select the cells containing data you want to separate by clicking and dragging or using the SHIFT or CTRL keys on your keyboard.
3. Click the 'Data' tab at the top of the screen to display the 'Data' ribbon.
4. Select the 'Text to Columns' button to display the 'Convert Text to Columns Wizard' dialog box.
5. Choose the 'Delimited' selection to separate the data by looking for commas or tabs or choose the 'Fixed width' selection to separate the data by looking for spaces. You will be able to see a preview of the selected data below these two choices. Click the 'Next' button to move to the next screen of the wizard.
6. Set the column breaks for the selected data by clicking at the point in the preview that you want to add a column. Double-click any column break lines to delete them, and move a break line by clicking and dragging it. Click the 'Next' button when you are finished setting the column breaks for your data.
7. Choose a column data format depending on the type of data you are separating, and then choose a destination where you want the new data to appear.
8. Click the 'Finish' button to close the 'Convert Text to Columns Wizard' and separate the selected cell data.
Read more ►

How to Resize Columns in a Microsoft Access Table


Resizing by Dragging
1. Open your database file in Microsoft Access.
2. Use the F11 key to open the Database window.
3. Click on the Table tab, then click Open. A listing of tables appears.
4. Select the table you want to modify, then click Open.
5. Click in the label or title of the column that you want to resize.
6. Move your cursor to the edge of the column. The cursor turns into a cross shape.
7. When the cursor is a cross, grab the edge of the column and drag it to the width you want.
Resizing From the Format Menu
8. With your table open, click in the label or title of the column that you want to resize.
9. Select Column Width from the Format menu in the toolbar.
10. Enter the exact width that you want the column to be. Alternatively, select Best Fit, which adjusts for the column's content.
Read more ►

How to Make a Histogram in Excel 2003


1. Open an Excel workbook. Click the Windows 'Start' button, click 'All programs,' click 'Microsoft Office' and then click 'Microsoft Office Excel 2003.' Alternatively, you can double-click the Excel shortcut icon on your computer's desktop.
2. Click 'Tools' in the menu bar to open a drop-down menu, then click the 'Add-ins' option. You must first add this feature to Excel before a histogram can be created.
3. Click the check box next to 'Analysis Toolpak' and click the 'OK' button in the Add-Ins window. This particular add-in will be loaded in to Excel.
4. Click 'Tools' in the menu bar a second time, then click 'Data Analysis.' This opens a new window. Click 'Histogram' in the list of options in the window and click 'OK.' This opens the Histogram window.
5. Enter cell names in the Input Range field. This is the data you want to analyze. You can enter these by dragging your mouse over the cells in your Excel worksheet. They will automatically load into the Histogram window.
6. Enter cells names in the Bin Range field as you did with the cells for the Input Range. These are the numbers you want to use as intervals in the histogram.
7. Select your output options. You have two main choices for what the output will look like: a table or a chart histogram. 'Pareto' will output your data in a table in descending order of frequency. 'Chart Output' is just that. The 'Cumulative Percentage' option can add this percentage to your table or chart. You can also have your histogram appear in a new workbook, and you can name it if desired. Click the check boxes of the output options you want, then click the 'OK' button. Your histogram will be generated.
Read more ►

Blogger news