Excel

Browse » Home » All posts

Tuesday, October 16, 2012

How to Change the Orientation of Text in Excel Cells

1. Start Microsoft Excel and open the file you want to change.
2. Select the cells in which you want to format the text.
3. Open the Format menu and select Cells.
4. In the Format Cells dialog box, select the Alignment tab.
5. In the Orientation box, increase or decrease the number of degrees, or drag the indicator to the angle you want.
6. Select OK to accept the changes.

How to Create a Drop Down Menu in Excel 2007

1. Switch to a different sheet in the workbook that is being used by clicking a tab on the bottom of the window. This helps prevent the list from being confused with a data-entry area.
2. Type a list of items on the sheet using cells that are on top of one another (e.g., A1, A2, A3 and A4). Leave no spaces between the items and type them in the order that they should appear on the drop-down menu.
3. Use the mouse to draw a box around the cells you used for the list.
4. Click inside the text-entry box at the top of the screen, to the left of the formula area. To confirm that you have the correct box, hover the mouse cursor over it. A message saying 'Name Box' will appear. In the box, type a name for the list. Remember the name.
5. Return to the original sheet and click the cell where the drop-down menu should be created.
6. Click the 'Data' tab at the top of the window. In the 'Data Tools' portion of the tab, click 'Data Validation.'
7. Click the drop-down menu next to 'Allow' and select 'List.'
8. Click inside the 'Source' box. Type the equal sign followed by the name of the list. For example, if you named the list 'MyList,' type '=MyList' in the box. Click 'OK.' The drop-down menu will appear in the cell.

Monday, October 15, 2012

How to Use the Excel Chart Wizard

1. Enter your data into an Excel spreadsheet.
2. Start the chart wizard. Click on the 'Insert' button on the toolbar and then select 'Chart.'
3. Select the type of chart you want from the 'Chart type' list in the chart wizard dialog box. Different versions of the chart you have selected will appear in the 'Chart sub-type' window on the right. Select one and click the 'Next' button.
4. Examine the preview of your chart. The 'Data range' tab shows you the range of data from your spreadsheet that is being displayed in your chart. If you want to change the range of data or the labels, click in the 'Data range' box to make the changes without closing the wizard.
5. Plot your chart. Click on the 'Rows' or 'Columns' radio buttons to plot your chart by rows or by columns.
6. Add or remove data from the selected range. Click on the 'Series' tab to make adjustments if there is information you would like to add or remove. Use the 'Series,' 'Name,' 'Values' and 'Category (X) axis labels' to make the changes.
7. Enter chart options. Use this step of the wizard to enter labels for your chart, select the type of gridlines to show and choose whether to hide or display a legend and the data table. Click 'Next.'
8. Choose the location of your chart. Select the 'As new sheet' radio button if you want to place your chart on its own sheet. The 'As object in' radio button will embed it in an existing worksheet which you can select from the drop down menu.
9. Click 'Finish.' If you selected a new sheet for your chart, it will be displayed in a separate window. Otherwise, your chart will be displayed on the worksheet you selected with handles you can use to move it where you want it.

How to Calculate Time Cards in Excel

1.
Go to 'Start,' 'Programs,' 'Microsoft Office' and 'Microsoft Excel.' If you have used Excel recently, go to 'Start' and 'Microsoft Excel.' Click 'New' to open a new document.
2.
Go to 'Timesheets,' click on 'Weekly timesheets with breaks,' then click on 'Download' at the lower right-hand corner of the screen. The time card will have a place for the Company name, time in and time out.
3. Enter '9:00 AM' in the 'Time In' cell (E11) for Sunday. Enter '12:00 PM' in the 'Time Out' cell (E12) for Sunday. The total hours calculated before the break is 3.00.
4.
Enter '1:00 PM' in the second 'Time In' cell (E14) for Sunday. Enter '6:00 PM' in the second 'Time Out' cell (E15) for Sunday. The total hours calculated after the break is 5.00.
5.
Enter '9:00 PM,' '2:00 AM,' '2:30 AM' and '5:00 AM' in the respective 'Time In' and 'Time Out' cells for Tuesday. The time before and after the 'Meal Break' is 5.00 and 2.50 hours, respectively.
6. Change the formula for the day's total hours. Enter '=J12 J15' to add the total hours for the day. Press 'Enter.'
7. Copy and paste the formula in each cell, except the 'Total Hours Scheduled' cell, to remove the error message icon.

How to Make a Fitness Chart

Excel 2010
1. Click the 'File' tab and select 'New.' Type 'Fitness chart' in the search box. View the available fitness charts. Download the chart by clicking the chart image and the 'Download' icon.
2. Replace the default measurements in the spreadsheet with your measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Chart' tab. Depending on the template, the tab may be called 'Measurements' or something similar.
3. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Fitness Chart' in the search box. View the available fitness charts. Download the chart by clicking the 'Use This Template' icon.
5. Replace the default measurements in the spreadsheet with your actual measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, this tab will be called 'Measurements' or something similar.
6. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
OpenOffice
7. Access the OpenOffice website. Type 'Fitness' in the search box. View the available fitness spreadsheets. Download the spreadsheet by clicking the 'Use This Template' icon.
8. Replace the default measurements in the spreadsheet with your own measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, it may be called 'Measurements' or something similar.
9. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.

How to Disconnect a User From a Shared Workbook

1. Click on the 'Review' tab in Microsoft Excel. Select 'Share Workbook' in the 'Changes' section.
2. Click the 'Editing' tab. Review the listing of user names under 'Who Has This Workbook Open Now.'
3. Click to highlight a user's name. Click on the 'Remove User' button.
4. Click the 'View' tab. Select 'Custom Views' in the 'Workbook Views' group.
5. Click to highlight a user's name in the 'View' list. Click the 'Delete' button.

How to Create a Pie Chart in Excel

1. Launch Excel and open the file which has the data that you want to base your pie chart on. The data can be created by simply entering the numbers, formulas, or by calling it in from other programs.
2. Highlight the data that you wish to base your chart on. The last cell that you wish to include will not be highlighted but it will have a bold outline around it.
3. Click 'Insert', 'Chart' and a Chart Wizard window will appear. Scroll down the right side column of Chart Type and click on 'Pie.' This will bring up several sub-types of pie charts on the left hand side of the window. Pick the one you think will best describe your data. Click 'Next.'
4. Review the data that you are seeing on this new window. If you do not see a pie chart with your data or the numbers look incorrect, click 'Cancel' and highlight your data and try the process again. If all is correct, select 'Next.'
5. Enter labels and a title for your pie chart on the next window. The next tab, Legend, allows the placement of the titles. The Data Labels tab allows manipulation and changes of the labeling of the pie chart. Click 'Finish' when the pie chart appears the way you want it to.
6. Manipulate the size of the chart on the spreadsheet by clicking the box it is in and dragging the mouse. Right clicking the chart and box that the chart is in brings up many options to edit the chart also.

How to Use the Chart Wizard in Access to Create a Chart

1. Open the Access database. Right-click the report or form to which you want to add a chart, and select 'Design View' to open it in Design View, which is a window showing the underlying design of your form or report that displays the details and properties of the boxes, buttons and other items in your object. Using Design View makes it easier for you to make changes to the way an Access object looks and acts.
2. Go to the 'Insert' menu and click 'Chart' in Access 2003. Click the 'Chart' button in the 'Controls' group of the 'Design' tab in Access 2007 or 2010. Click on the form or report where you want to place the chart. The Chart Wizard will open. The Chart Wizard is a small window that walks you through the steps to create precisely the chart that you require.
3. Select the table or query from which you want to obtain data to create a chart. To choose a table, click the 'Tables' button and click on the table you want to use from the list presented. To choose a query, click the 'Query' button and click on the name of the query you want to use from the list presented. Click the 'Next' button at the bottom of the window.
4. Choose the fields containing data that you want included in the chart. To select a field, click on the field name in the 'Available Fields' list. Click the single right-arrow button in the middle of the window to move that field to the 'Fields for Chart' list. Repeat, adding up to six fields to the 'Fields for Chart' list. Click the 'Next' button at the bottom of the window.
5. Select the type of chart you want to create by clicking on the chart type under 'What Type of Chart Would You Like.' To find out more about a specific chart type, click on the type to view a description in the right side of the Chart Wizard window. Click the 'Next' button at the bottom of the window after selecting a chart. Select layout options. Drag and drop field buttons from the right side of the Chart Wizard onto the sample chart in the main Chart Wizard window. Click the 'Next' button at the bottom of the window.
6. Type in a title for the chart in the 'What Title Would You Like for Your Chart' Box. Select 'Yes' or 'No' under 'Do You Want the Chart to Display a Legend?' A legend is a box next to or beneath the chart that provides further detail by identifying the colors or patterns used in the chart. Click the 'Finish' button at the bottom of the Chart Wizard window.
7. Click the 'Save' button on the 'Standard Toolbar' in Access 2003 or the 'Quick Access Toolbar' in Access 2007 or 2010 to save the chart added to your Access report or form.

How Do You Limit the Number of Rows in an Excel Spreadsheet?

1. Open Microsoft Excel from the 'Start' menu.
2. Press 'Alt' and 'F11' at the same time to open the visual basic editor.
3. Click 'View' on the top toolbar then select 'Project Explorer' from the drop down menu.
4. Click 'View' again then select 'Properties Window' from the drop down menu.
5. Select the name of the spreadsheet you are working with from the list in the explorer window on the left side of the screen.
6. Change the value in the 'ScrollArea' category to limit the number of rows allowed on the spreadsheet. For example, in order to limit the spreadsheet to 365 rows, you would enter the text '$1:$365' (without the quotation marks) into the ScrollArea box. To limit the spreadsheet to another number of rows just put that number in the place of 365 when entering '$1:$365' in the ScrollArea box.
7. Close the visual basic editor when you are done and the number of rows in the spreadsheet will be limited based on your specifications.

Sunday, October 14, 2012

How to Train in Microsoft Excel 2007

1. Talk to your employer. If you need to use Microsoft Excel 2007 for work, your employer may have a training program in place or may be willing to start one to help train employees.
2. Work with your instructor. If you need to use Microsoft Excel 2007 for a class, your instructor may be able to help you understand what you need to know to complete assignments. Tutoring services might also be available through your school.
3. Go through the Microsoft Excel 2007 intermediate level tutorial. This is a free service offered by Microsoft that takes you through all the basic functions of Excel and using a hands-on approach to help you retain what you learn. The tutorial takes 30 to 40 minutes.
4. Use the Excel 2007 'Support/Training' page when you have general questions about Excel or want to find out how to access or accomplish a specific task. From this page, you can access multiple informative articles.
5. Utilize other Microsoft Excel training courses. Microsoft offers separate programs for different uses of Excel, such as creating workbooks, entering formulas and adding charts. These courses give you more in-depth information about each major use.
6. Practice what you learn. Each time you learn something new in your Excel training, practice it. For example, if you learn how to use a formula, create a practice spreadsheet with data to test your knowledge. This helps you retain what you learn.

How to Use VBA to Save in Excel As a PDF

1. Double-click the Excel file name from My Documents or Windows Explorer, or launch Excel and use the 'Open' command from the start button. Browse to your file and click 'Open.'
2. Press the 'Alt' and 'F11' keys simultaneously to open the Visual Basic editor. Use the Project Explorer to browse to the module where you need to automate saving the spreadsheet as a PDF file. Place your cursor in your Visual Basic code where you want the save to occur.
3. Use the 'ExportAsFixedFormat' function to perform a 'Save to PDF.' This function can be invoked from any worksheet (e.g., ActiveSheet.ExportAsFixedFormat) or for the workbook as a whole (e.g., ActiveWorkbook.ExportAsFixedFormat).You can set a number of options with this command; the option values shown below are Excel's default values.[Mandatory] Type:= xlTypePDF[Mandatory] Filename:=
[Optional] Quality:= xlQualityStandard (xlQualityStandard or xlQualityMinimum)[Optional] IncludeDocProperties:= True ('True' means that the document properties, such as author name and document title, will be included in the PDF file; 'False' means that these properties will not be set in the PDF file.)[Optional] IgnorePrintAreas:= False ('True' means that all content in the spreadsheet should be included; 'False' indicates that only the information within your set printing areas will be included.)[Optional] From:= 1 (This is the page number where Excel should start the save. If this is omitted, then Excel begins at the first page.)[Optional] To:= 5 (This is the page number where Excel should finish the save. If this is omitted, then Excel ends on the last page.)[Optional] OpenAfterPublish:= False ('True' indicates that Excel should launch your default reader for PDF files with the PDF file open; 'False' indicates that the new PDF file should not be automatically opened.)
4. Execute the code function to ensure that the 'Save to PDF' works properly. Save the spreadsheet file.

How to Put a Check Box in Excel 2003

1. Open the specific Excel 2003 worksheet that needs to have a check box added. Locate the 'Forms' toolbar at the top of the screen, which shows a series of boxes corresponding to different forms available to be created.
2. Click the 'Check Box' icon in the 'Forms' toolbar, which shows a square blue box with a black check mark. Click the starting point anywhere inside the spreadsheet where you want the check box to appear. Drag your pointer down and toward the right to set the borders of the check box. Release your mouse button to create the check box.
3. Click the 'Check Box 1' text appearing next to the box and type whatever label you'd like to use for the box. Click the 'Control Properties' icon located in the 'Forms' toolbar to open a new window containing the check box's properties.
4. Navigate to the 'Control' tab. Type the name of the cell that should correspond to the check box in the 'Cell Link' box. For instance, if you want the box to be checked in correlation to data stored in cell 'A4,' type 'A4' in the box. Click the 'Checked' or 'Unchecked' radio button to change whether the box should check or uncheck when data is entered into the corresponding cell.
5. Click 'OK' to save your changes. If you need to make changes to the check box in the future, click the box and then click the 'Control Properties' button.

A Line Won't Delete in Microsoft Excel

1. Check to see if you are highlighting the correct line to delete in your document. Point your cursor on the line number on the right side and left-click to highlight. Once highlighted, you should be able to delete the line.
2. Remove any document protections. If the Excel document is protected, this may prevent editing including removal of lines. On the 'Review' tab, click to see 'Changes' group to see if there are protections on the worksheet or workbook. These may have to be removed or a password inserted to continue.
3. Investigate the explanation of any error messages you receive when attempting to delete a line in Excel. The error message may provide insight into why the command cannot be executed. If you do not understand the message, visit Microsoft Online to search for Excel help.
4. Highlight all rows and press F5 and 'Go To' and click 'Special.' Select 'Visible Cells' and click 'OK.' This may now allow you to delete lines as necessary, especially if there are multiple or hidden lines.
5. Close the Excel document and exit the application. Delete any temporary or extra files off your computer as you may have a memory or resource issue. Shut down your machine and reboot. Open your Excel document and retry to delete your line.

How to Make a Graph on Excel 2003

1. Open the spreadsheet you want to graph in Excel 2003. Select 'Insert' and 'Chart' from the toolbar. The chart wizard appears.
2. Select the type of chart you want to create in the 'Standard Types' tab. To see a sample of how the chart will look, click the 'Press and Hold to View Sample' button and click 'Next.'
3. Click the button on the right of the 'Data range' text box to select the area of your spreadsheet you want to graph. The 'Source Data - Data range' toolbar appears.
4. Drag your mouse over the area of your spreadsheet that will be graphed.
5. Click the button on the right of the 'Source Data - Data range' toolbar to return to the chart wizard. A sample of your chart appears in the dialog box.
6. Select 'Rows' or 'Columns' under 'Series in' and click 'Next.'
7. Select the tab containing the options you want to customize: 'Titles,' 'Axes,' 'Gridlines,' 'Legend,' 'Data Labels' or 'Data Table.' Click 'Next.'
8. Select 'As new sheet' to place the graph in a separate worksheet or 'As object in' to place it into the current worksheet. Click 'Finish.'
9. Select the graph on your worksheet if you chose 'As object' in the previous step and drag it where you want it to appear.

Saturday, October 13, 2012

How to Edit a Calculated Field PivotTable

1. Open Excel 2010 and open a workbook containing a PivotTable.
2. Click the worksheet containing the PivotTable. Click the PivotTable. The PivotTable Tools Tab appears. Click the 'Options' tab and 'Fields, Items, and Sets.' Select 'Calculated Field.' Your calculated fields are displayed.
3. Select the calculated field formula that you want to edit. Click the 'Formula' box and select 'Modify.' Edit the formula and click 'OK.' Your calculated field is edited in your PivotTable.

Subscribe to: Posts (Atom)