Excel

Browse » Home » All posts

Thursday, August 16, 2012

How to Make Text Flash in Excel

1. Open the Microsoft Excel 2010 file in which you want to make the text flash. Press 'Alt' and 'F11' to open up the VBA console.
2. Right-click the 'ThisWorkbook' entry on the left side of the VBA console. Move your mouse over 'Insert' and choose 'Module.'
3. Double-click the 'ThisWorkbook' option. Copy the following code and paste it into the white space on the right side of the screen:Private Sub Workbook_Open()BlinkEnd SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)NoBlinkEnd SubThis code will start and the blinking text when you open this workbook, and stop the blinking text when you close the workbook, once you insert the code to tell Excel what Blink and NoBlink actually mean.
4. Double-click 'Module 1' from the list on the left side of the screen. Place your cursor on the right side of the screen and enter the following line:Public Timecount As DoubleThis creates a variable called 'Timecount' that you can use in every subroutine.
5. Press enter to move the cursor to the next line and enter the following code:Sub blink()With ThisWorkbook.Worksheets('Sheet1').Range('A1:A10').FontIf .ColorIndex = 3 Then.ColorIndex = 2Else.ColorIndex = 3End IfEnd WithTimecount = Now TimeSerial(0, 0, 1)Application.OnTime Timecount, 'Blink', , TrueEnd SubThis creates a subroutine called 'Blink' that will cause all the text within the defined range to flash. You can change the range from 'A1:A10' to whatever range you desire, including an individual cell.
6. Press 'Enter' to access the next line and enter the following code:Sub noblink()ThisWorkbook.Worksheets('Sheet1').Range('A1:A10').Font.ColorIndex = _xlColorIndexAutomaticApplication.OnTime Timecount, 'Blink', , FalseEnd SubThis will create a subroutine that will run when you close the workbook. It turns all the text back to black, so that someone who opens the workbook without macros enabled will not accidentally see nothing in the text boxes.
7. Close the VBA console by clicking the 'X' in the top-right corner. Click the 'File' tab at the top of the screen and choose 'Save as.' Select 'Excel Macro-enabled Workbook' from the drop-down field and type in a name for the workbook. Click 'Save.'
8. Close the Excel file and then reopen it. Your text, within the range defined in the macro, will start to flash. Depending on your security settings, you may need to click the 'Enable Macros' button at the top of the screen to see the flashing text.

Wednesday, August 15, 2012

How to Sort in Excel 2003

1. Open Excel 2003 and select a workbook containing data that needs to be sorted. Click 'File' on the menu bar and select 'Open.' Browse your computer for the workbook. Click the workbook and select 'Open.' The workbook opens.
2. Highlight the range of data that needs to be sorted. Click the first cell in the range you want to sort by. Click the 'Ascending' button on the Standard toolbar. The data is sorted in ascending order by the values in the column.
3. Highlight the range of data you want to sort. Click 'Data' on the menu bar. Select 'Sort.' The 'Sort' dialog box opens. Select a column header in the 'Sort By' field to sort your data by. Select another field in the 'Then By' field to perform a secondary sort. Click 'OK.' Your data is now sorted by multiple criteria.

How to Teach Yourself Microsoft Excel Virtually

1. Browse through Excel tutorials on the official Microsoft Excel website. Choose 'Getting Started With Excel 2010' if you are a total beginning, or 'Excel Skills Builder' to augment the skills you already have.
2. Compare private databases of Excel tutorials. Examples include 'Excel-2010.com,' 'Excel 2010 Tutorials' and 'Lynda.com.' Although the accuracy of non-Microsoft materials isn't guaranteed, private websites often provide other users' personal experiences, which may help you work through difficulties better than straight instruction.
3. Enroll in a formal online Excel 2010 training course via a local community college or university. Enrolling in an online course allows you the freedom to teach yourself Excel but the resource of having a tutor or professor on hand to clear up any ambiguities for you.

How to Control Multiple Users on One Excel Spreadsheet

1. Open the Excel spreadsheet you wish to share among multiple users.
2. Click 'Tools' and select the 'Share Workbook' option. A pop-up window will appear.
3. Place a check mark in the check box at the top of the 'Editing' tab on the Share Workbook pop-up window.
4. Click the 'Advanced' tab at the top of the 'Share Workbook' pop-up window. The window will change to present a variety of customizations for controlling multiple users on the Excel spreadsheet.
5. Choose desired options on the 'Advanced' tab. You may change the history log time frame and control how updates are handled.
6. Click the 'OK' button. Excel will immediately save the workbook and provide a pop-up message to indicate this. The Excel spreadsheet is now set up to handle multiple users.
7. Place the spreadsheet on a shared network drive where multiple computers can access it.
8. Save the spreadsheet on any computer to immediately see updated changes by other users currently working on the same file.

How to Create Mailing Labels in Microsoft Word

1. Open Microsoft Word 2010 and create a new document if one hasn't already been created. You can create a new document by clicking 'File' then 'New.'
2. Click the 'Mailings' tab, then 'Labels.' The 'Envelopes and Labels' window will open.
3. Type the address you wish to use on your labels in the address box. Choose if you want a whole sheet of the same label or just a single label printed in the 'Print' section.
4. Click the 'Options...' button near the middle bottom section of the window to choose the labels to which you want to print. Under 'Label Information' select the brand of labels you are using in the drop-down box. Under 'Product Number' select the product number of the labels you are using and then click 'OK.'
5. Click 'New Document' to create a new page of labels. If you are only printing a single label, the new document option will not be available. To print just a single label, click the 'Print' button in the lower left-hand side of the window.
6. Insert the label sheet in your printer. Print the newly created label document by clicking 'File,' then 'Print.' Choose your printer and click 'Print.'

How to Make the Top Row Scroll in Excel

1. Open up to the worksheet in the Microsoft Excel workbook.
2. Click the 'View' tab.
3. Click the 'Freeze Panes' icon. Select 'Freeze Top Row' from the menu.
4. Click the 'Windows Office' orb logo in the top left corner. Use the save options on the menu, if you want to make the top-row scroll permanent.

How to Figure Percentage Using Excel

1. Click the 'Start' button on the taskbar, point to 'All Programs' and then click 'Microsoft Excel.'
2. Click in an empty cell on the spreadsheet document in which you want the percentage value to be displayed (for example, B12).
3. Type '=(total_value/number_of_values)*100' in cell B12 to get the percentage. Here, replace total_value with the total sum of the numbers whose percentage you want to calculate and number_of_values with the number of individual values. For example, to calculate the percentage of 12, 22, and 32, type '=(66/3)*100'. Here, 12 22 32=66 and there are 3 individual values (12,22,32).
4. Type '=((second_number-first_number)/ABS(first_number))*100' in an empty cell on the spreadsheet were you want to calculate the difference between two numbers as a percentage. Here, replace first_number with the first of the two numbers whose difference you want as percentage and second_number with the second number. For example, to calculate the difference between 32 and 42 as percentage, type '=((42-32)/ABS(32))*100' in the cell.

Tuesday, August 14, 2012

How to Adjust Page Breaks in Excel 2007

1. Open the worksheet you want to print.
2. Click 'View' then 'Page Break Preview.'
3. Click on the page break and drag the break to the place you want the break to be.

Monday, August 13, 2012

How to Set Two Print Areas Using an Excel 2007 Worksheet

Printer Method
1. Select your first desired print range by highlighting the cells within your worksheet; for example A1:H15.
2. Hold down the 'Ctrl' key while highlighting the second set of cells to print--for example N5:V15--resulting in two separate highlighted areas.
3. Select the 'Page Layout' tab, click on 'Print Area' and select 'Set Print Area.'
4. Press 'Ctrl P,' a shortcut that brings up the Print Dialog Box.
5. Verify the output printer displayed is the correct printer and click the 'Properties' button.
6. Look through your different tabs and options for a setting asking you to specify how many pages per sheet you want to print and change it from '1 page per sheet' to '2 pages per sheet.' These options vary by printer based on the make and model and may not be available on your printer.
7. Click the 'OK' button to close the properties box and return to the Print Dialog Box. If your printer does not have this capability, use the camera method.
Camera Method
8. Right-click on the tab of your current sheet ('Sheet1' by default) located at the bottom of the page and select 'Insert' from the pop-up menu. Click on 'Worksheet' and then click 'OK' to insert a new blank sheet into your document. Click the Office button in the upper left-hand corner of Excel and select 'Excel Options.'
9. Choose 'Customize' from the menu on the left and click the drop-down menu under 'Choose commands from' to change the selection from 'Popular Commands' to 'All Commands.' Scroll through the options until you find 'Camera;' click on it and choose 'Add' to add it to the quick-access ribbon at the top of the page. Click 'OK' to save your changes.
10. Return to the sheet where the data you need to print is located. Select the first range of cells to be printed and then click the Camera tool in the quick access ribbon at the top of the page. Switch to your blank worksheet and click to select where you want the upper left-hand corner of your image placed. The new sheet will contain the selected range as a graphic.

How to Insert the Same Drop

1. Enter the list items in a column on the spreadsheet where you will not need to enter other data, such as on the far right or bottom of the page. Enter the items in the order you want them to appear in the list, one item per cell. Make sure there are no blank cells between the items.
2. Select all of the cells into which you want to insert the drop-down list. If the cells are contiguous in one row or column, you can select them by clicking the first cell and dragging your mouse down or across the row or column. If they are in noncontiguous cells, press and hold the 'Ctrl' key while you click on each cell into which you want to insert the drop-down list.
3. Go to the 'Data' menu in Excel 2003 and select 'Validation.' In Excel 2007 or 2010, go to the 'Data' tab, click the 'Data Validation' button. The 'Data Validation' dialog box will open.
4. Make sure you are on the 'Settings' tab. Select 'List' in the 'Allow' box. Click inside the 'Source' box and then select the range of cells containing the list you created in the first step.
5. Make sure 'In-cell Dropdown' is checked. Clear the 'Ignore Blank' check box if you want to require users to enter data into the cell. Click 'OK' to insert the drop-down list into all of the selected cells.

How to Center in a Row on Excel for Microsoft 2003

1. Select the row of text that you want to center by left clicking on the first cell and then dragging the cursor to the right.
2. Right-click and then select 'Format Cells' from the pop-up window.
3. Select the 'Alignment' tab.
4. Click on the arrow next to the 'Horizontal' or 'Vertical' text alignment tab. If you want the text centered both horizontally and vertically, then choose both options.
5. Click on 'Center Across Selection.'
6. Click on 'OK.'

How to Create Free Receipts

1. Open a Microsoft Excel spreadsheet. Select 'New'. Select a free sales receipt template from the list of available spreadsheet templates.
2. Fill in or delete the specified fields for company information, such as company name, address, company logo, and receipt number.
3. Enter in the sales information for each item, which includes quantity, item number, item description, and the unit price.
4. Enter a value in the discount cell if a discount applies and the discount is a flat number. If the discount is in the form or a percentage off, then enter '=xy*z' in the cell where x = the column letter of the item, y = the row number of the item, and z = the percentage off in the form of a decimal (e.g. 20 percent is 0.20).
5. Enter the sales tax in decimal form (e.g. 8 percent is 0.08) into the sales tax cell on the sales receipt.

Saturday, August 11, 2012

How to Convert Numbers Into Letters in MS Excel

1. Open your spreadsheet in Microsoft Excel.
2. Click 'Tools' in the top menu bar, select 'Macro,' and choose the 'Visual Basic Editor' option. If you use Excel 2007 or later, click the 'Developer' tab and click the 'Visual Basic' button instead.
3. Click 'Insert' in the top menu bar of the Visual Basic Editor and select 'Module.'
4. Copy and paste all of the code shown below:Option ExplicitFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = ' Thousand 'Place(3) = ' Million 'Place(4) = ' Billion 'Place(5) = ' Trillion 'MyNumber = Trim(Str(MyNumber))DecimalPlace = InStr(MyNumber, '.')If DecimalPlace > 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace 1) _'00', 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber
''Temp = GetHundreds(Right(MyNumber, 3))If Temp
'' Then Dollars = Temp Place(Count) DollarsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ''End IfCount = Count 1LoopSelect Case DollarsCase ''Dollars = 'No Dollars'Case 'One'Dollars = 'One Dollar'Case ElseDollars = Dollars ' Dollars'End SelectSelect Case CentsCase ''Cents = ' and No Cents'Case 'One'Cents = ' and One Cent'Case ElseCents = ' and ' Cents ' Cents'End SelectSpellNumber = Dollars CentsEnd FunctionFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right('000' MyNumber, 3)If Mid(MyNumber, 1, 1)
'0' ThenResult = GetDigit(Mid(MyNumber, 1, 1)) ' Hundred 'End IfIf Mid(MyNumber, 2, 1)
'0' ThenResult = Result GetTens(Mid(MyNumber, 2))ElseResult = Result GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd FunctionFunction GetTens(TensText)Dim Result As StringResult = ''If Val(Left(TensText, 1)) = 1 ThenSelect Case Val(TensText)Case 10: Result = 'Ten'Case 11: Result = 'Eleven'Case 12: Result = 'Twelve'Case 13: Result = 'Thirteen'Case 14: Result = 'Fourteen'Case 15: Result = 'Fifteen'Case 16: Result = 'Sixteen'Case 17: Result = 'Seventeen'Case 18: Result = 'Eighteen'Case 19: Result = 'Nineteen'Case ElseEnd SelectElseSelect Case Val(Left(TensText, 1))Case 2: Result = 'Twenty 'Case 3: Result = 'Thirty 'Case 4: Result = 'Forty 'Case 5: Result = 'Fifty 'Case 6: Result = 'Sixty 'Case 7: Result = 'Seventy 'Case 8: Result = 'Eighty 'Case 9: Result = 'Ninety 'Case ElseEnd SelectResult = Result GetDigit _(Right(TensText, 1))End IfGetTens = ResultEnd FunctionFunction GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = 'One'Case 2: GetDigit = 'Two'Case 3: GetDigit = 'Three'Case 4: GetDigit = 'Four'Case 5: GetDigit = 'Five'Case 6: GetDigit = 'Six'Case 7: GetDigit = 'Seven'Case 8: GetDigit = 'Eight'Case 9: GetDigit = 'Nine'Case Else: GetDigit = ''End SelectEnd Function
5. Return to your spreadsheet and single-click the cell containing a number you want converted into text.
6. Type '=SpellNumber(X)' (without quotation marks) into the cell, replacing 'X' with the number originally contained in the cell. You can also refer to the value of another cell, instead of a specific number (e.g., '=SpellNumber(B5)' to use the value in cell B5).
7. Press the 'Enter' key to convert the number value into letters. If you entered '7' in the previous step, for example, the value in the cell now displays as 'Seven.'

How to Make a Frequency Chart

1.
Create a dataset of student grades; include student names and the grades 50, 60, 70, 80, 90 and 100. Enter or sort the grades in ascending order.
2.
Create a bin range of 50, 60, 70, 80, 90 and 100 and enter or sort it in ascending order.
3.
Go to 'Data Analysis.' In older versions of Excel, go to 'Tools,' 'Data Analysis.'
4.
Click on 'Histogram,' then click 'OK.' A frequency chart is also considered a Histogram.
5.
Select the 'Input Range' of the histogram. Click on the box beside the field.
6.
Highlight the 'Input Range' which is the 'Grade' column. Click on the box again to return to the histogram dialog box.
7.
Select the 'Bin Range' of the histogram. Click on the box beside the field and highlight the 'Bin Range' column.
8.
Click on the box beside the field to return to the 'Histogram' dialog box.
9.
Click on the 'Labels' check-box and the 'Output Range' button. The Labels check-box tells Excel that there are labels in the dataset. The Output Range tells Excel where to place that information that will feed the frequency chart or histogram.
10. Select the 'Output Range' using the same steps as the selection of the Input and Bin Ranges. However, select only one cell. In this case, cell E14 was selected. Return to the 'Histogram' dialog box.
11. Click the 'Chart Output' box which will generate the frequency (histogram) chart.
12. Click 'OK' to generate the frequency chart.

How to Turn Off Calculations in Excel 2010

1. Open Microsoft Excel and click 'Formulas' in the Ribbon at the top of the Excel window.
2. Click 'Calculation Options' on the far right side.
3. Select 'Manual' to disable auto-calculation. If you want Excel to recalculate your spreadsheet while auto-calculation is disabled, you will need to click 'Calculate Now,' also located under 'Calculation Options.' When you are ready, you can re-enable auto-calculation.

How to Do a Scattergraph in Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'
2. Click 'File,' then 'Open.' Locate the Excel document that contains the data in it which you wish to make a scatter chart from.
3. Highlight the cells that contain the data.
4. Click 'Insert,' then 'Chart' to launch the Insert Chart wizard.
5. Select 'XY (Scatter)' foam the list of options then click 'Next.' You can 'Press and Hold to View Sample' to see what the graph will look like. Click 'Next' then name the axes and the chart. Click 'Finish' when you have finished creating the graph.

Saturday, July 28, 2012

How to Do a VLOOKUP With Two Spreadsheets in Excel

1. Select the cell in which you want the returned value.
2. Click on the formula bar, the long empty box at the top of the screen.
3. Type =VLOOKUP(“text you want to match”,Be sure to include the double quotes.
4. Click the tab of the other spreadsheet, the one holding the searchable table.
5. Highlight the table.
6. Click back to the original spreadsheet. Now the location will be in the VLOOKUP function.
7. Click the formula bar again, add a comma after the table location and type in which column you want the value returned from. For example, if you want to return two columns to the right of the search column, type 3.
8. Type a closing parenthesis and press “Enter.”

How to Add Radio Buttons to Microsoft Excel for Data Collection

1. Define the mutually exclusive data to be collected. For example, if the spreadsheet is used to track clothing by size, the option buttons might be for small, medium and large sizes. This prevents mistyping information.Select the 'Developer' tab; from the 'Controls group,' press 'Insert > Option Button.' Place the cursor into the position where the upper left corner of the button and label are desired, and then drag a square, creating the frame. Do the same for the remaining option buttons to be created. Excel will name these 'Option Button 1,' '2,' and '3.'
2. Select 'Option Button 1,' and in the 'Developer' tab, 'Controls' group, select 'Properties.' A new window opens allowing the option button to be customized. From this pane, the font, color, status, control, caption and important functions can be controlled. Define the caption, select fonts, colors and size, and then create the functions for the button. Do the same for the remaining option buttons to be grouped.
3. Select the option buttons. Using the 'Drawing Tools>Format' tab in the 'Align' group, use the 'Align' button to position the option buttons as desired, then use the 'Group' button to create the Option Button Group.
4. Add the appropriate commands and cell links as needed for the option buttons, and then protect the cells over which the buttons are located. When the spreadsheet is completed, protect the Worksheet in the 'Review' tab, 'Changes' group, and the option buttons will be functional.

How to Insert Auto Numbering in a Header for Excel 2003

1. Run the Microsoft Excel 2003 application, and open the workbook that you want to edit.
2. Click 'View' in the menu bar, and then click 'Header and Footer' in the drop-down list.
3. Click the 'Custom Header' button.
4. Click the section of the header that you would like to add automatic page numbering to.
5. Click the '#' icon to add automatic numbering to the specified section.
6. Type any additional text that you want to display in the header, and then click 'OK' twice to save your header changes.

Friday, July 27, 2012

How to Use Multiple Regression in Excel

Excel for Multiple Regression
1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.
2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you are using Excel 2007, you can access the Data Analysis add-in by clicking the Microsoft Office button in the top left corner of an open Excel workbook. Click the button and then click “Excel Options.” A new window opens, displaying a set of options on the left side. Choose “Add-ins,” select “Analysis ToolPak” and click “OK.”
3. Click the Tools menu in Excel and select Data Analysis (in Excel 2007, click the “Data” tab and click the Data Analysis button). A window opens that displays a menu of analysis tools. Scroll to “Regression” and click “OK.”
4. Enter the values for dependent variable (Y) and independent variables (X) by clicking on the applicable cells and columns in your Excel data sheet. After selecting the ranges of data to be entered for analysis, click 'OK.' Excel runs the procedure and display your results on a new worksheet.
5. Examine your summary output, starting with the regression statistics at the top of your output. Note the value of R-square, which tells you what percentage of the variability in the dependent variable (for example, average salaries) is explained by your regression model. Then note the values of the coefficients and corresponding t-statistics and significance levels. A t-statistic of 2 or greater indicates statistical significance, meaning that the relationship between that independent variable and the dependent variable is likely not due to random chance.

How to Compare Two Columns in Excel 2007

1. Activate Excel and open a new worksheet. Set up three different columns entitled 'A,' 'B' and 'C'. Allow for five data entries within each column so that you are working with a 3x5 grid.
2. Enter the data for the two known columns in 'A' and 'C'. Leave column 'B' blank for the time being, and make sure not to enter zeros in the 'B' column. Column 'B' needs to remain blank because this is where the formula will be entered and function.
3. Select the first cell in column 'B' and enter the following formula therein; =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),'',A1)
4. Navigate to 'Fill' from the 'Edit' menu and click 'Down'. The duplicate data will appear in column 'B.' Rinse and repeat as needed with new data sets.

How to Merge Columns From Two Tables

1. Open the Excel file that contains the two tables with the data you want to merge.
2. Select the cell where you want the merged column to start and type in a header for the column. Select the cell directly beneath the header. If you want the merged call to be part of one of the tables, right-click on the top of the column where it lists the column letter, directly to the right of where you want the new column to go. Choose 'Insert.'
3. Enter in the following: =CONCATENATE(XX,' ',YY) Make 'XX' equal to the first cell that you want to merge, and make 'YY' equal to the cell in the second column you want to merge with the first. Both 'XX' and 'YY' need to follow Excel's standard naming rules, which list the column letter followed by the row number, like 'A1' or 'C23.' Press 'Enter' when you are done typing in the formula. The two cells will merge in your new column.
4. Click on the cell that you just entered the formula into and move your mouse over the lower-left corner of the cell. When the mouse cursor turns into a plus sign, click and hold the mouse button. Drag the mouse down until you have reached the last row of the column and release the mouse button. The entire column will fill with data merged from the two original columns.

How to Make a Bar Line Graph in Excel

1. Launch Excel. Click the 'File' tab and select the 'New' option from the File menu to create a new Excel spreadsheet.
2. Enter the data for the graph into the cells. Enter the data you want to appear in the bar graph in one row and the data you want to appear in the line graph in an entirely separate row. Each piece of data must be entered into its own cell. For the bar graph, each cell will become its own bar. For the line graph, each cell will represent a specific point on the line.
3. Click on the first data-containing cell (upper-left corner) and drag the mouse cursor to the last data-containing cell (bottom-right corner) to highlight all of the cells that contain data.
4. Click the 'Insert' tab, followed by the 'Bar' option beneath the 'Charts' heading. Select the 'Stacked Bar' option from the Bar menu to transform the data into a bar chart. By default, the data entered in the first row appears as a blue bar, while the data entered in the second row appears as a red bar.
5. Click on the data series that you want to change to the line portion of the graph (the red bar, for example). Click the 'Design' tab, followed by the 'Change Chart Type' option beneath the 'Type' heading. Click the 'Line' option and select the type of line chart you want to use from the Line menu. The data series you selected will now be transformed into a line chart, giving you a bar and line graph.

How to Copy Column Values to Rows in Excel 2007

1. Copy all of the blocks in the column that you want to convert to a row. You can copy multiple blocks at once by holding down the 'CTRL' button as you click on each block in the column.
2. Right click anywhere within the highlighted blocks and select 'Copy.' Another method for copying the information in the columns is to press the 'CTRL' and 'C' buttons at the same time.
3. Choose the row where you want to paste the information, right click on the first block and select 'Paste Special.' A menu with several options will pop up.
4. Place a mark next to 'Transpose' in the bottom right section of the menu and click 'OK.' The information in the column will paste into the row you chose.

How to Use the LINEST Function in Vista and Excel

1. Highlight a set empty cells in your spreadsheet which composes 5 rows and 3 columns. Click on the 'Formulas' tab, and then select the 'More Functions' menu. Select 'Statistical' sub-menu, and then select the 'LINEST' function. The LINEST 'Function Arguments' dialog will open.
2. Click on the red arrow next to the 'Known_y's' argument, and highlight the column or row of data on your spreadsheet that represent the y-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
3. Click on the red arrow next to the 'Known_x's' argument, and highlight the column or row of data on your spreadsheet that represent the x-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
4. Type 'TRUE' without the quote marks in both the 'Const' and 'Stats' boxes. Click 'OK' to close the 'Function Arguments' dialog.
5. Highlight the formula in the formula bar, and hold down Control, Shift, and Press Enter. Starting with the top left cell and going from left to right, the following information will be calculated: slope, intercept, the standard error of all values, the standard error of all values except the last, the R-squared value, the standard error for the y estimate, the F statistic, degrees of freedom, the regression sum of squares, and the residual sum of squares.

Thursday, July 26, 2012

How to Print the Gridlines of an Excel Worksheet

1. Start Microsoft Excel, and open a spreadsheet that you would like to print, having the gridlines appear on the printed copy of the spreadsheet.
2. Choose the 'File' menu and click on 'Page Setup...' to open the 'Page Setup' dialog box. Within the 'Page Setup' dialog box you can choose settings that apply to your whole Excel spreadsheet.
3. Click on the 'Sheet' tab at the top of the 'Page Setup' dialog box to display the sheet settings for the Excel spreadsheet you have open.
4. Find the 'Print' section of the 'Sheet' tab in the 'Page Setup' dialog box, located in the middle of the dialog box.
5. Use your mouse to click the check box in front of the option 'Gridlines' to add a check mark to the box. This will make sure that the gridlines will print when you print your current spreadsheet.
6. Press the 'OK' button with your mouse to close the 'Page Setup' dialog box and set your current spreadsheet to print with gridlines.
7. Print your spreadsheet by choosing the 'File' menu, clicking on 'Print' and then selecting the settings you wish to apply to your printed spreadsheet. When your spreadsheet prints, you will notice that the gridlines have printed, as well.

How to Chart Cells From Two Different Worksheets in Microsoft Excel

Create the Chart
1. Enter data in rows by using row headers with data under them. You can also choose to enter data in columns by using column headers with data under them.
2. Select the cells containing the data you will use for the chart.
3. Select the 'Insert' tab on the top menu, and click the arrow in the bottom-right corner of the Charts group to open the Insert Chart window.
4. Choose the type of chart in the Templates column of the Insert Chart window.
5. Select the chart using the images in the right box in the Insert Chart window, and click the 'OK' button.
6. Move your embedded chart by clicking anywhere in the chart to activate the Chart Tools option on the top menu.
7. Click 'Chart Tools' on the top menu, select the 'Design' tab, and click the 'Move Chart' button in the Location section of the menu.
8. Embed the chart in any sheet in the workbook using the pull-down menu in the Object In option in the Move Chart window, if you choose to. Click the 'OK' button when you are done.
9. Move the chart to a new sheet that you can name using the box next to the New Sheet option in the Move Chart window, if you want to. Click the 'OK' button when you are done.
Add Data from a Second Worksheet to the Chart
10. Click anywhere in the chart to activate it.
11. Click 'Chart Tools' on the top menu, and select the 'Design' tab.
12. Click the 'Select Data' button in the Data section of the Design tab to open the Select Data Source window.
13. Click the 'Add' button in the Legend Entries (Series) box of the Select Data Source window to open the Edit Series window.
14. Name the series in the Series Name box. You can choose to type in a name or link to a cell that contains the name.
15. Select the square box next to the Series Name box in the Edit Series window to link to the name of the series. If it is on another worksheet, click the tab of that worksheet, select the cell containing the name, and click the square box in the Edit Series window.
16. Select the square box next to the Series Values box in the Edit Series window to select the data range you want to include in your chart.
17. Click the tab for the sheet containing the data, and select the range of data you want to include on your chart, and then click the square in the Edit Series window. Click the 'OK' button to include the new data from a second worksheet in your chart.

How to Rotate a Pie Chart in Excel

1. Open the Excel worksheet containing the pie chart you wish to rotate.
2. Click on the pie chart. Doing so will display the 'Chart Tools' menu at the top of the Excel window. Within this menu you will see tabs labeled 'Design,' 'Layout' and 'Format.'
3. Select the 'Format' tab. Navigate to the 'Current Selection' group. Locate the 'Chart Elements' box and click on the arrow next to it. Click on the desired data point or series.
4. Return to the 'Format' tab. Select 'Format Selection' from the 'Current Selection' group.
5. Locate the 'Angle of First Slice' box. Move the slider to the position corresponding to the desired degree of rotation. Alternatively, indicate the angle at which you wish the first slice to appear by entering a value between zero and 360.

How to Change to R1C1 Cell Reference in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Office' button in Excel.
3. Click on 'Excel Options.'
4. Click on 'Formulas,' in the left-hand pane of the 'Excel Options' box.
5. Check the 'R1C1 reference style' check box under 'Working with formulas' to enable 'R1C1' style referencing. Clear this check box to use 'A1' style referencing.

Wednesday, July 25, 2012

How to Insert a Check Box on an Excel Spreadsheet

1. Click the 'Developer' tab.
2. Click 'Insert.'
3. Select 'Check Box' under the Active X controls.
4. Click where you want the check boxes to appear on the spreadsheet.
5. Go back to the 'Developer' tab and select 'Design Mode.' This allows you to design the check boxes.
6. Go back to the 'Developer' tab and select 'Properties' to change any properties for the check boxes.

How to Enable Excel Data Analysis in Office 2007

1. Open the Microsoft Excel 2007 application on your computer and then click on the 'Microsoft Office' button.
2. Click on the 'Excel Options' button from the bottom of the application and then click on the 'Add-ins' button.
3. Select the 'Excel Add-ins' option from the 'Manage' box and then click on the 'Go' button.
4. Click on the box next to the 'Analysis ToolPak' field so that it's selected and then click on the 'OK' button.
5. Click on the 'Yes' button if you are prompted to install the add-in for your computer. Once the add-in is loaded, click the 'Data Analysis' button from the 'Data' tab.

How to Enter 17 Digits Into Microsoft Excel 2003

1. Enter your 17-digit number in a cell in an open spreadsheet. If the number displays in scientific notation, you have to adjust the cell format to have it display all 17 digits.
2. Click on the 'Format' pull-down menu at the top of the spread sheet, and select 'Cells' to format how numbers will appear within an individual cell.
3. In the 'Format Cells' box that appears, select 'Numbers' from the tabs. You'll be presented with a series of choices for formatting numbers, such as currency, dates or percentage. Select 'Numbers' from the list. This will change the appearance of numbers in cells, removing scientific notation and displaying all digits as originally entered.
4. You may also want to check the box labelled, 'Use 1000 separator (,)', so that your 17-digit number is displayed with commas, as this makes the numbers much easier to read.

Tuesday, July 24, 2012

How to Make a Series the Same Color Across Multiple Charts in Excel 2010

1. Launch Excel and open the spreadsheet that contains the charts for which you want to create a common color for a data series. Click the chart to display the “Chart Tools” menu in the ribbon.
2. Click one of the data series in a chart for which you want to create a common color. Click the “Format” tab and locate the “Current Selection” section. Click the “Chart Elements” menu and select the “Data Series” option. Click the “Format Selection” button, which will open a separate window.
3. Click the “Patterns” tab and click a color in the “Area” section. The series will automatically take on the selected color. Repeat steps 2 and 3 for the same data series in each chart, selecting the same color each time.

How to Move Columns in Excel 2007

1. Open the Excel 2007 spreadsheet you wish to modify.
2. Select the top of the column you wish to move, such as column A. This highlights the entire column.
3. Right-click the column and click 'Cut.'
4. Place the column in the location you prefer. Right-click the location and click 'Paste.' Repeat these steps for other columns you wish to change. You have now moved columns in Excel 2007.

Monday, July 23, 2012

How to Remove Print Preview Lines in Excel 2007

1. Click the 'Page Layout' tab on the Ribbon at the top of the Excel screen.
2. Find the 'Gridlines' section inside the larger 'Sheet Options' section.
3. Uncheck the 'View' box to remove gridlines from the Print Preview screen. If you want to prevent the gridlines from appearing on a printed sheet as well, uncheck the 'Print' box.

How to Move Columns in Excel

1. Choose the column heading (1, 2, 3 and so forth) that you want to move and highlight it. You can also select several columns by holding down your left mouse button and sliding it across the columns which you would like to select, in case you want to move more than one column at a time.
2. Leave your mouse placed on the highlighted column(s) and right click your mouse button. This will bring up a pop up menu with several options to choose from.
3. Pick 'Cut' from the menu that pops up. This will make the column you want to move disappear, but your computer will store it in its temporary memory. As long as you don't cut anything else or close the program, your information will not be lost.
4. Select the column that is directly to the right of where you want your selected column to be placed, so that it is highlighted. This is how you let your computer know where you want to place the row that you are moving.
5. Go to the 'Insert' menu and click on the 'Cut Cells' option. The column(s) that you chose to cut will be pasted into your selected area.

How to Sort Columns in Excel 2007

1. Open your spreadsheet file in Microsoft Excel.
2. Press 'Ctrl' and 'A' to select all the columns in your spreadsheet.
3. Click 'Sort Filter' near the right side of the ribbon.
4. Choose 'Custom Sort.'
5. Select the first column you want to sort on, such as Column A, in the first drop-down box.
6. Select the criterion on which you want to sort this column, such as the value of the cell or the cell color, in the second drop-down box.
7. Select your desired sort order, such as A-to-Z or smallest-to-largest, in the third drop-down box.
8. Click the 'Add Level' button if you want to sort by additional criteria.
9. Check 'My Data Has Headers' if your spreadsheet has a header row. If you check this box, Excel will leave the header row as the very top row. Otherwise it would sort your column headings as though they were data.
10. Click 'OK.' Your Excel spreadsheet is now sorted by columns according to the criteria you set.

How to Make a Double Bar Graph on Microsoft Excel

1. Open a new Excel 2010 document.
2. Enter the names of the items you want on your double bar chart, starting in cell B1 and continuing to the right of that cell. These headers will become the labels printed at the base of each of the bar sets on the graph.
3. Type the names of the two categories that the double bar graph will track into cells A2 and A3. These labels will be located to the right of the bar graph. Each of these two categories will be assigned a color bar, which will appear next to each of the items on the graph.
4. Input your numerical data into the cells, starting with cell B2 and continuing down and to the right. When you're done entering information, select cell A1 and hold down the mouse button. Move the mouse to the bottom-right cell and release the button.
5. Select the 'Insert' tab at the top of the screen. Click the 'Bar' button located in the 'Charts' area of the ribbon. Choose any of the 'Clustered' bar chart options, as opposed to the 'Stacked' options. Your double bar chart will appear on the spreadsheet.

How to Create a Frequency Table in Excel Using Pivot Tables

1. Highlight the cells containing the data, if you’re starting the pivot table from scratch.
2. Open the Data drop-down menu and select “PivotTable Report…”. A new frame will open. Click the two “Next” buttons that you see. Click “Existing Worksheet” and select a cell to be the upper-left corner of your pivot table.
3. Click “Layout.” A new panel will open. Drag the field name to the square center of the table that you want to know the frequency of. Double-click it and select “Count.” Drag the same field from the far right into the left column where the row labels go. Click “OK” and then click “Finish.” You will be sent back to the Excel spreadsheet, which will now have a pivot table starting in the cell where you requested it be created.
4. Group continuous data by right-clicking your mouse on the column with the field labels. (An example of continuous data is 'revenue,' while an example of discrete or nominal data is “highest degree achieved.”) Select “Group and Outline” in the menu that pops up and then select “Group.” The range of data will be displayed for you. Select the interval width you want the data grouped into, and enter it in the “By:” field. Select “OK.” The table will now collapse into a more-compressed form and the frequencies listed will go up as data points are added together. If an interval width has no data in it, it won’t be given a row in the table.

Sunday, July 22, 2012

How to Change the Text Box Size in Excel 2003

Inserting a Text Box
1. Click on 'View' on the main toolbar, then 'Toolbars' and ensure there is a check mark next to the 'Drawing' toolbar.
2. Locate the 'Drawing' toolbar on your screen.
3. Click on either the 'Text Box' or 'Vertical Text Box' commands on the 'Drawing' toolbar. Your cursor changes to the 'Text Box' tool.
4. Click and drag your mouse to create a text box.
Resizing a Text Box
5. Locate the text box that you wish to resize, then click anywhere in the text box to select it.
6. Change the width of the text box by clicking and dragging from the central anchor point on either side of the text box.
7. Click and drag from the central anchor point on the top or bottom of the text box to change the height of the text box.
8. Change the width and height of the text box simultaneously by clicking and dragging from any of the text box's corner anchor points.

How to Format an 'If Statement' in Excel for a Blank or Filled Reference

1. Open the Microsoft Excel 2010 worksheet. Press 'Alt F11' to open the VBA console.
2. Select a module from the list on the left side. If no module exists, right-click on a worksheet in the list, move the mouse pointer over 'Insert' and choose 'Module.' Select the module that appears.
3. Type 'sub testing()' into the first line of the module and press 'Enter.' Change 'testing' to whatever word you want as it is simply the name of the subroutine. Excel VBA automatically adds the 'End Sub' command to the last row of the module.
4. Type 'Dim x as range' into the next line. This will establish 'x' as a variable, which you will need in the next statement.
5. Type 'For each x in range ('XX:YY')' into the next line. Change 'XX' to the top-left cell in the range you want to work with and 'YY' to the bottom-right cell. This will create a loop that will go through each cell in the range one by one.
6. Type 'If IsEmpty(x) Then XXX Else YYY' into the next line. Change 'XXX' and 'YYY' to whatever code you want to run. The IsEmpty command will check each cell and perform the action after 'Then' if the cell is empty. 'Else,' and the code after it, are optional.
7. Type 'Next' on the next line. This tells Excel to loop back to the 'For' statement and move on to the next cell.

Friday, July 20, 2012

How to Determine an MS Excel File Version

1. Click on the Windows “Start” button then on “Computer” to open Windows Explorer.
2. Browse your computer to find the Excel file.
3. Check the file extension. If it is '.xlsx' then the file was created with Excel 2007. The extension '.xls' indicates older program versions.
4. Right-click on the Excel file and choose “Properties” from the pop-up menu.
5. Select the tab “Details” (in Windows Vista or 7) or “Summary” then “Advanced” (in Windows XP).
6. Read the Excel version in the line “Type,” for example, 'Microsoft Office Excel 2003.'

How Can I See Excel 2003 Commands in Excel 2007?

1. Click the 'Office' button, and select 'Customize Quick Access Toolbar.' This action opens a pop-up window.
2. Select 'All Commands' from the 'Choose' menu. This action displays a list of available Excel commands that can be added to the Quick Access Toolbar.
3. Select a command from Excel 2003 that you want to view independent of the ribbon interface. Click the 'Add' button. Repeat this process for each Excel 2003 command that you want to view in the Quick Access toolbar. Click the 'OK' button when you have finished adding Excel 2003 commands.

How to Remove Hyperlinks in Microsoft Excel 2007

1. Open Excel using the 'Start' menu or another shortcut. You may also type 'Excel' in the Start menu search box and click on the resulting link.
2. Click the 'Open' option and select the spreadsheet you want to work with. Locate the cell containing the link.
3. Right-click the link you want to delete and click the 'Remove Hyperlink' option. Repeat for each link you want to remove.

How to Calculate Percentage Increases in Excel 2007

1. Type 'Beginning Number' in cell A1, and then type your beginning number in cell B1. For example, type '50' in cell B1.
2. Type 'Ending Number' in cell A2, and then type your ending number in cell B2. In the example, type '100' in cell B2.
3. Type 'Percent Increase' in cell A3.
4. Type '=((B2-B1)/B1)' in cell B3. In the example, your result will be 1, or 100%.

How Can I Disable All Double Clicking in Excel 2003?

1. Open the document you wish to disable double-clicking on in Microsoft Excel 2003.
2. Press the 'Alt' key on your keyboard and 'F11' key simultaneously. The VBA editor will appear.
3. Double-click 'Workbook' in the upper left window pane in the VBA editor. A new window will appear.
4. Copy and paste the following code into the new window:Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ByVal Target As Range, ByVal Cancel As Boolean)Cancel = TrueEnd Sub
5. Press 'Alt' and 'F11' simultaneously again to save the code and return to Excel. The string of code disables all double-click actions in the Excel workbook.

How to Create a Stacked Bar Graph in Excel

1. Click 'Start,' then 'All programs,' then 'Microsoft Office,' then 'Microsoft Excel.'
2. Press 'Ctrl' 'O,' then locate and open the Excel document which contains the data to be turned into a stacked bar chart.
3. Highlight the data that you want to create the chart from.
4. Click 'Charts,' then 'Bar,' then 'Stacked Bar.' The stacked bar chart will automatically appear in the worksheet. You have now inserted a stacked bar chart into your Excel document.

Thursday, July 19, 2012

How to Allow a Shared Workbook

Microsoft Excel 2003
1. Launch Excel 2003, then open the Excel workbook you want to share with other users on the network.
2. Click the 'Tools' option on the menu bar, then click 'Share Workbook.' Wait for the 'Share Workbook' options dialog window to appear.
3. Click and enable the checkbox next to the label that reads 'Allow Changes by More Than One User at the Same Time. This Allows Workbook Merging.' Click the 'OK' button.
4. Click the 'File' > 'Save' option on the menu bar to save the permission changes to the workbook.
Microsoft Excel 2007 or 2010
5. Open Microsoft Excel, then open the workbook you want to share.
6. Click the 'Review' tab on the 'Office Ribbon,' then click 'Share Workbook.'
7. Click the 'Editing' tab, then enable the checkbox next to 'Allow changes by more than one user at a time. This also allows workbook merging.'
8. Click the 'Advanced' tab, then select options for tracking and updating changes to the shared workbook. Click 'OK.'
9. Save the workbook to save the sharing and permission changes for the file.

How to Keep Track of Changes in Excel 2003

1. Open Excel from the Start menu on your toolbar.
2. Open the file for which you'd like to track changes.
3. Go to Tools > Track Changes.
4. If you want to see the changes while you are editing, select 'Highlight Changes.' Then select the boxes labeled 'Track Changes While Editing' and 'Highlight Changes on Screen.' When making a lot of changes, you might not want this option on because it can be distracting.
5. Decide who gets to make changes on your worksheet. Select the 'Who' tab and choose the users who can make changes. If you choose 'Everyone,' your file will be shared on the network.
6. Select the 'When' option to decide when changes are tracked. The most popular is option is 'All.'
7. Click 'OK' to save your options.

Wednesday, July 18, 2012

How to Convert XML Files Into an Excel Spreadsheet

1. Open Excel.
2. Click the Microsoft Office symbol in the upper left-hand corner. Select 'Open' from the menu.
3. Find the XML document you want to convert and click 'Open.' If the document does not seem to be present where it should be, you may need to select 'XML Files(*.xml)' from the 'Files of type:' drop-down box.
4. A small dialog box will open titled 'Open XML.' In this box select the radio button titled 'As an XML table,' then click 'OK.'
5. If another dialog pops up that has a message relating to creating a 'XML schema,' simply click 'OK.' The XML file should be opened as a spreadsheet.
6. To complete the conversion, save the spreadsheet as an Excel file by clicking the 'Office' button, then 'Save.'

Subscribe to: Posts (Atom)