Excel

Browse » Home » All posts

Thursday, September 15, 2011

How to Rotate the Page in Excel 2007

1. Open a new or existing document in Excel.
2. Click on the 'Page Layout' tab.
3. Click on the 'Orientation' button in the Page Setup button group, then select 'Landscape' if 'Portrait' is already highlighted, or 'Portrait' if 'Landscape' is already highlighted.

Tuesday, September 13, 2011

How Do I Turn a Microsft Excel Spreadsheet Into a Chart?

Instructions
1. Open Microsoft Excel from the 'Start' menu and wait for the application to load.
2. Enter the data you want to convert into a chart into the cells. Include labels for each column in row 1.
3. Click and drag from the top right cell to the bottom left cell to highlight all the data you want to include in the chart. Highlight the column label headings along with the data.
4. Click the 'Insert' tab on the top of the screen.
5. Choose one of the chart templates from the drop down menus. As soon as you click a chart type it will automatically be inserted into the spreadsheet. You can double-click on the title of the chart to change it.

How to Set Up a Web Query in Excel 2003

1. Launch Excel and open a new spreadsheet.
2. Select any cell and open the 'Data' menu, open the 'Get External Data' sub-menu and click the 'New Web Query' option.
3. Type the URL address of the Web page you would like to analyze in the text box underneath '1'.
4. Choose an option underneath '2'. You can extract data from the entire page, from only tables or from a specific data table.
5. Select a formatting option: 'none', 'rich text formatting' or 'full HTML formatting'. If you just want to acquire data, then your best option is to select 'none'.
6. Click the 'OK' button and click it again when the dialog box pops up. Excel will populate your spreadsheet with the website data and start from the cell you previously selected.

How to Insert a Static Time in Excel

1. Start Microsoft Excel 2007, and open an existing workbook from your files in which you want to insert a static time into one of the cells. Alternatively, you can start a new, blank workbook to insert the static time.
2. Select the cell in the workbook you want to insert the static time into by clicking on it. The selected cell will now have a thick black box around it, indicating that it is selected and ready for the next step.
3. Hold down the 'Ctrl,' 'Shift' and ':' keys at the same time on the keyboard. This keyboard shortcut will instruct Excel to enter a static time into the selected cell.
4. Look at the cell, and see the current time to be inserted into the selected cell. This time will not change at any point unless you manually change it yourself. It will not update on its own at any time.
5. Insert the static time into any other cells in the open Excel workbook using the same procedure that is outlined in the previous steps.

How to Locate the NOW Function in Microsoft Excel

1. Open Excel 2010 and click the 'Formulas' tab. Select the 'Date and Time.' A drop-down list appears.
2. Select 'NOW.' The Function Arguments dialog box appears. Click 'OK.'
3. Review the date and time that has appears in your spreadsheet.

How to Make a Chart Using Excel 2007

1. Open Excel 2007 and open the file that contains the data you want to use to create a chart.
2. Highlight the data you want to include in the chart by left-clicking on one data cell and dragging your mouse across all of the cells you wish to include. The cells are highlighted in blue when selected.
3. Click on the 'Insert' tab of the toolbar and then select what chart you want to create. The chart is automatically created and placed inside the worksheet.

How to Convert Wk1 to Xls

1. Confirm that both the data and the format file for the WK1 file are in the same folder.
2. Open the Excel “File” drop-down menu. Click “Open.” Find and open the WK1 file in the folder tree. Don’t open the formatting file yourself, just the file with the WK1 extension.
3. Open the “File” drop-down menu again. Click “Save.” Select .XLS as the file type in the Save panel. Select “OK.” Excel then converts the workbook into the Excel format.
4. Open the converted .XLS file to check for errors. Search for the phrase “formula failed to convert.” If Excel was not able to convert a formula, it defaults to displaying the value that the formula produced and writes “formula failed” in a cell comment. To search the comments for this phrase, open the Edit drop-down menu and click following sequence: 'Find > Look in: Comments.' Recreate the lost formulas where possible.
5. Print out the new spreadsheets and compare the numbers with printouts of the original Lotus workbook spreadsheets, if they are available. This is an additional check to make sure the conversion was performed correctly.

Monday, September 12, 2011

How to Set Up a Linear Program in Excel 2007

Linear Program Setup
1. Enter a title for your linear program in cell A1. Enter labels for your variables in cells C3 and D3.
2. Type your variable coefficients in cells C5 and D5. List each coefficient in the column that corresponds to its respective variable.
3. Create a border around cells C6, D6 and B7. Cells C6 and D6 will be used to display the optimal solutions for your variables, and cell B7 will be used to show the value of the objective function given the optimal variable values.
4. List your constraints beginning with cell C9 and continuing down one row for each constraint. Use a different cell for each element of your constraints, including the inequality symbols, but excluding the operator symbols, such as the plus or minus symbol.For example, if one of your constraints is 3x1 6x2
5. Type the heading 'LHS' under your constraints in column C, and type the heading 'RHS' beside it in column D. List the right-hand-side values of your constraints vertically under the RHS heading, and create borders for each corresponding cell under the LHS heading. The LHS cells will be used to display the actual left-hand-side values of your constraints for the optimal solution.
6. Enter the formula '=SUMPRODUCT(C5:D5,C6:D6)' in cell B7, the optimal solution output cell. This formula will calculate the value of the objective function given the optimal values of the variables.
7. Enter the formula '=SUMPRODUCT(C9:D9,$C$6:$D$6)' in the first cell under your LHS heading, and copy the formula into each additional left-hand-side value output cell under the LHS heading. This formula will calculate the actual value of your constraints given the optimal values of the variables.
Linear Program Solution
8. Click 'Data -> Solver' to bring up the 'solver parameters' dialog box. Set the target cell to '$B$7,' the objective function value output cell. Set the solver to maximize or minimize the function, based on the purpose of your linear program.
9. Enter the constraints into the solver parameters dialog box. Begin by clicking 'Add' for each individual constraint. For each constraint, enter the cell reference for the corresponding cell under your LHS heading in the cell reference box, choose the proper inequality symbol from the drop-down box in the center, and enter the cell reference for the corresponding cell under your RHS heading.
10. Back in the solver parameters dialog box, click 'Options' to open the solver options dialog box. Click 'Assume Linear Model' and 'Assume Non-Negative,' then click 'OK.'
11. Click 'Solve' in the solver parameters dialog box, and Excel 2007 will fill in the optimal solution, the value of the objective function, and the actual left-hand-side values of your constraints.

How to Change Your Header Margins on Word

1. Double-click the Word document's header.
2. Change the top margin by editing the number--which is measured in inches--in the 'Header from top' setting in the 'Position' section on the Ribbon.
3. Change the bottom margin by editing the number--also measured in inches--in the 'Header from bottom' setting in the 'Position' section on the Ribbon.
4. Click 'Page Layout' on the top of the program. The options in the 'Page Layout' Ribbon lets you change the left and right indentations.
5. Change the left indent by editing the number in the 'Left Indent' listing in the 'Paragraph' section of the Ribbon.
6. Change the right indent by editing the number in the 'Right Indent' listing in the 'Paragraph' section of the Ribbon.

How to Make a T

1. Start Microsoft Excel. Press 'CTRL N' to create a new workbook.
2. Type 'Data1' and 'Data2' in cells 'A1' and 'B1,' respectively. You may replace these with your preferred variable names.
3. Enter your first set of data in column 'A,' starting from cell 'A2.'
4. Enter your second set of data in column 'B,' starting from cell 'B2.' Make sure that both columns have the same number of cells. For instance, if column 'A' contains five entries, column 'B' should also have five entries.
5. Highlight all non-empty cells (including the variables) and then press 'CTRL SHIFT F3.'
6. Select the cell below the last number in column 'A.' Type '=T.TEST' and then press 'CTRL A.' This opens a dialog box where you can enter parameters for the function.
7. Enter 'Data1' and 'Data2' into the 'Array1' and 'Array2' boxes, respectively.
8. Enter 1 into the 'Tail' box if you'd like to use one-tailed distribution. Otherwise, enter 2.
9. Enter the kind of test you'd like to perform in the 'Type' box. Use any of the values below.1 - Paired t-test2 - Two-sample equal variance3 - Two-sample unequal variance
10. Click on 'OK' to view the result of your formula.

How to Reset Shortcut Keys in Microsoft Excel

1. Open Microsoft Excel 2003 and click on the 'Tools' menu. Then click on 'Customize.' Ensure that the toolbar you want to reset is visible by clicking the 'Toolbars' tab in the 'Customize' box and selecting the toolbar of the shortcut keys that you want to reset.
2. Click on 'Customize Keyboard' while inside the 'Customize' box.
3. Choose to 'Reset All' while inside the 'Customize Keyboard' dialog box. Then click on 'Yes' when prompted to 'Reset All.'
4. Click on 'Close' and then 'Close' again to exit out of the 'Customize' box.
5. Change an individual shortcut key by clicking on 'Commands' while inside the 'Customize' box. Then click the button on the toolbar and choose 'Modify Selection.'
6. Type a name for the menu command in the 'Name' box. Use an ampersand before the letter you want to use as the keyboard shortcut. Then press 'Enter.'

Sunday, September 11, 2011

How to Use an Excel Workbook Over a Network

Microsoft Excel 2003
1. Create the workbook and make sure you include any final changes, as certain features in Excel cannot be changed after the workbook is shared. The most important features to create before sharing are conditional formats, subtotals, data tables, worksheet and workbook level protection and macros.
2. Designate user settings by clicking 'Tools' on the menu bar, then clicking 'Share Workbook.' Click the 'Editing' tab in the 'Share Workbook' dialogue box. Click the 'Allow changes by more than one user at the same time' check box.
3. Click the 'Advanced' tab and create settings for when to save the file and how to handle change conflicts between users.
4. Click 'OK,' then save the workbook when Excel prompts.
5. Share your workbook. Click 'File' then 'Save as...' on the menu bar. Navigate to the network location accessible to each user and save the workbook to your network.
Microsoft Excel 2007
6. Create the workbook and make sure you include any final changes, as certain features in Excel cannot be changed after the workbook is shared. The most important features to create before sharing are conditional formats, subtotals, data tables, worksheet and workbook level protection and macros.
7. Click the 'Review' tab, then click 'Share Workbook' in the 'Changes' group. This opens the 'Editing' dialogue box.
8. Create your settings. Click the 'Allow changes by more than one user at the same time' check box in the 'Share Workbook' dialogue box.
9. Click the 'Advanced' tab and create settings for when to save the file and how to handle change conflicts between users.
10. Click 'OK,' then save the workbook when Excel prompts.
11. Share your workbook. Click the Microsoft Office Button, then click 'Save as...' Navigate to the network location accessible to each user and save the workbook to your network.

How to Unhide a Very Hidden Sheet in Excel 2003

1. Open a workbook that you wish to unhide. Press 'Alt' 'F11' to open Visual Basic Editor.
2. Click 'View,' then click 'Project Explorer.' Click 'View' and click 'Properties' to open the 'Project Explorer' and 'Properties' windows that will give you a view of the very hidden worksheets.
3. Select the very hidden sheet in the 'Project Explorer' window and find the very hidden sheet in the 'Properties' window within the 'Visible' tab. Under the 'Properties' window, make the very hidden worksheet visible by selecting this code in the box '-1 --- xlSheetVisible.' You have now unhidden a very hidden sheet in Excel 2003.

How to Balance a Checkbook Using Excel

1. Open the Excel program from your start menu or by double clicking a shortcut on your desktop.
2. Label your headings on the top row and leave open columns between your headings. A1 should be labeled 'Method;' B1 should be blank; C1 should be 'Date;' D1 should be blank; E1 should be 'Description;' F1 should be blank; G1 should be 'Debit;' H1 should be blank; I1 should be 'Credit:' J1 should be blank; K1 should be 'Balance;' L1 should be blank; and M1 should be 'Cleared.'
3. Change your blank column widths to separate the data you will insert later. Click on the first blank column (B), hold the 'Ctrl' button down and click on the other blank columns, (D, F, H, J; L). They will be highlighted in black. Right click your mouse on any black column. A drop down bar will open, click on 'Column Width.' Change to '2' and click 'OK.'
4. Change your other Column Widths that will hold data to the size you desire. The most noticeable change will be the 'Description' column. Change this to a Column Width of '27' so it can hold enough text to record your information.
5. Format cells to hold currency. Click on 'G,' hold down the 'Ctrl' button and click on 'I' and 'K.' Right click on one of the black highlighted columns to see the drop down bar. Select 'Format Cells.' On the 'Number' tab, select 'Currency' and choose your decimal places and dollar sign. This will make your form consistent.
6. Insert your starting balance. On the first row, you want to insert only your starting balance in the 'K2' cell. This will be the number all your debits and credits will be added or subtracted from.
7. Insert your data beginning with Row 3. Check #'s, ATM, Deposit and other methods will be entered in Column A. Insert the date of the transaction (you may format this column by right clicking on 'C,' 'Format Cells' and selecting the date format you prefer). Enter the description and amount in the appropriate columns.
8. Create a running balance. Click on cell 'K3.' On the toolbar, click on the Auto Sum button which appears as a Greek letter 'E.' A dotted, moving block will appear on 'K2,' and you will see a bar under the toolbars with =SUM(K2). Insert your command after the K2: =SUM(K2-G3 I3) and click 'Enter.' You have formatted your cell data.
9. Format the 'Balance' column to update as you enter data. Click on the K3 cell, hold down the 'Ctrl' button and click the letter 'C' on the keyboard. This copies the format of that cell. Click on the K4 cell, hold down the 'Ctrl' button and click the letter 'V' on the keyboard. This pastes the format into that cell. Repeat the paste process as far down as you prefer.
10. Reconcile your Excel spreadsheet to your monthly bank statement. Put an 'R' in the Cleared column to indicate that an entry matches your bank statement and has been added or subtracted to your balance.
11. Verify your balance. Your bank statement may be different from your Excel balance. Certain transactions may not have cleared the bank that you have recorded. Take your Excel balance, and add or subtract any amounts that do not have an 'R' beside them to your Excel balance. This total should match your bank statement balance.

How to Add or Delete an Excel Worksheet

1. Open Microsoft Excel and open the file you want to change.
2. To add a worksheet, open the Insert menu and select Worksheet. The new sheet will appear before the selected sheet in the tab menu at the bottom of the Excel window. It will be numbered.
3. To delete a worksheet, first select the sheet by clicking its tab at the bottom of the Excel window, then open the Edit menu and select Delete Sheet. Click OK to delete the worksheet.

Subscribe to: Posts (Atom)