Wednesday, March 16, 2011

How to Turn off Gridlines in Excel


1. Launch Excel by clicking on the 'Excel' shortcut in your Windows Start Menu.
2. Open the worksheet for which you want to disable gridlines by clicking on the 'Excel' icon and selecting the 'Open' command.
3. Click on the 'View' tab and de-select the 'Gridlines' check box under the 'Show/Hide' category.
Read more ►

Tuesday, March 15, 2011

How to Go to Precedent Worksheets in Excel


Enable the Display Options
1. Open the saved Excel worksheet.
2. Click the 'File' tab on the command ribbon. A list of commands will appear.
3. Click 'Options.' The 'Excel Options' dialog box will open.
4. Click the 'Advanced' button on the left pane. The 'Advanced' pane will appear.
5. Scroll down the right pane to the 'Display' section.
6. Select the named workbook in the 'Display Options for This Workbook' text box.
7. Select the radio button for 'All' in the 'For Objects, Show' option.
8. Open another workbook if it contains the formula's reference cells. An open workbook makes the cells accessible for the 'Trace Precedents' option.
9. Click 'OK.' The 'Excel Options' dialog box will close.
Apply the Trace Precedents Command
10. Click and drag on the cell that contains the formula. The selected cell will appear highlighted. The formula will appear in the Formula Bar.
11. Click the 'Formulas' tab.
12. Click the 'Trace Precedents' button in the 'Formula Auditing' group. Tracer arrows will appear superimposed on the cells. Blue arrows indicate no errors. Red arrows indicate cells that cause errors. A black arrow points to a worksheet icon if the selected data cell is referenced by another worksheet's cell.
Read more ►

Monday, March 14, 2011

How to Use VBA to Import Data From Excel Into Access


1. Launch Microsoft Office Excel and type 'data1' in A2, and 'data2' in B2. Press 'Ctrl' and 'S' to open the 'Save As' dialog Window and save the workbook in 'C:\Temp\' as 'dataToImport.xlsx.' Click 'Save' and close Excel.
2. Launch Microsoft Office Access, click 'Blank Database' and click the 'Create' button. Click 'Database Tools,' and click 'Visual Basic' to open the VB Editor Window. Click the 'Insert' menu and then click 'Module' to insert a new code module. Click the 'Tools' menu, click 'References,' and check the box next to 'Microsoft Excel
Object Library.'
3. Start by typing the following VBA code to create new sub procedure:Private Sub importExcelData()
4. Type the following to create variables you will use to read Excel:Dim xlApp As Excel.ApplicationDim xlBk As Excel.WorkbookDim xlSht As Excel.Worksheet
5. Type the following to create variables you will use in Access:Dim dbRst As RecordsetDim dbs As DatabaseDim SQLStr As String
6. Type the following to define database objects and also define the Excel workbook to use:Set dbs = CurrentDbSet xlApp = Excel.ApplicationSet xlBk = xlApp.Workbooks.Open('C:\Temp\dataToImport.xlsx')Set xlSht = xlBk.Sheets(1)
7. Create a new table with two columns in Access to import data from Excel. Type the following VBA code to create the table using the 'DoCmd' object:SQLStr = 'CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)'DoCmd.SetWarnings FalseDoCmd.RunSQL (SQLStr)
8. Open the table you just created by using a the Recordset object. Type the following to open the table and add a new row:Set dbRst = dbs.OpenRecordset('excelData')dbRst.AddNew
9. Type the following to get values from the Excel workbook, save them to your table and update the record:xlSht.Range('A2').SelectdbRst.Fields(0).Value = xlSht.Range('A2').ValuexlSht.Range('B2').SelectdbRst.Fields(1).Value = xlSht.Range('B2').ValuedbRst.Update
10. End your procedure by typing the following VBA code:dbRst.Closedbs.ClosexlBk.CloseEnd Sub
11. Press 'F5' to run the procedure. The data in your Excel workbook has just been imported into your Access table.
Read more ►

How to Open Excel 2007 Without a Blank Document


1. Click on the 'Microsoft Office' button.
2. Click on 'Excel Options.' The Excel Options dialog box will open.
3. Click on the 'Advance' tab, then scroll down to 'General.'
4. Type the name of your file location in the 'At startup, open all files in:' text box. For example, type 'C://MyDocuments/myfile.xls.'
5. Click on the 'OK' button. The next time you open Excel, the selected files will open automatically.
Read more ►

How to Use Calendar Control in VBA Excel


1. Start Microsoft Office Excel, select the 'Developer' tab then click 'Visual Basic.' Click the 'Insert' menu and select 'UserForm.'
2. Right-click the 'Toolbox' menu and select 'Additional Controls...'. Check the box next to 'Calendar Control 12.0' then click 'OK.'
3. Click the calendar control and add it to 'UserForm1.' Click the 'CommandButton' control and add one to your 'UserForm1.' Double-click the 'CommandButton1' to open Microsoft Visual Basic.
4. Copy and paste the code below inside the 'CommandButton1_Click()' subroutine.Dim dataString As StringdataString = Calendar1.ValueActiveCell = dataStringActiveCell.NumberFormat = 'mm/dd/yy'
5. Press 'F5' to run the program then click 'CommandButton1.' The program will get the calendar date chosen by user and will add it to the active cell in the Excel spreadsheet.
Read more ►

Sunday, March 13, 2011

How to Find Hidden Columns in Excel 2007


1. Inspect the column headers at the top of the Excel 2007 spreadsheet. The headers are gray and are labeled with letters in consecutive alphabetical order. Note which letters are missing from the headers. These missing letters indicate which columns are hidden.
2. Reveal single or consecutively hidden columns by clicking on the two columns that would be on either side of the hidden columns. For example, if column 'B' is missing (hidden), you will click on columns 'A' and 'C' to select them. To continue that example, if columns 'B' through 'F' are hidden, you will click on columns 'A' and 'G' to select them. Be sure to click on the actual gray column header to select the complete column rather than a specific cell within the column.
3. Click the 'Home' tab if you are not already on that tab. Then click the 'Cells' group to show the drop-down menu. Click the 'Format' button, which will reveal another drop-down menu. On this new menu, scroll down to the 'Hide Unhide' option (under Visibility). This will reveal one more menu that contains the options to 'Unhide Rows' and 'Unhide Columns.' Select 'Unhide Columns.' Your missing columns will be revealed.
4. Alternatively, you can follow Step 2 to select the two columns adjacent to your hidden columns and then right-click to reveal a shortcut menu. Click 'Unhide' on that menu to reveal the hidden columns.
Read more ►

How to Highlight Changes in Microsoft Excel 2003


1. Access the highlight menu. To access this menu, scroll to the “Tools” tab and select “Track Changes.”
2. Open the Highlight Changes Options menu. Under the submenu that opens scroll to “Highlight Changes” and left-click to access the 'Highlight Changes' menu. Make sure to check the box labeled “Track Changes While Editing.”
3. Set up the “When” attributes. By checking this box, you can access a dropdown menu where you can choose from the following: All (which will track changes by everybody), Since I Last Saved (which will track changes from your last save), Not Yet Reviewed (which will track all changes that have yet to be reviewed by you) and Since Date (which will track changes since a certain date you enter.)
4. Set up the “Who” attributes. Under this box you can choose to track changes by Everyone or by Everyone But Me (which will track all other user changes but yours) by selecting the desired attribute from the dropdown menu.
5. Set up the “Where” attributes. Under this selection, you can enter a span of cells, rows, columns, or all three, by entering their corresponding data into the field provide, which will track all changes made to the specified cells, rows or columns.
6. Set the screen options. If you wish to see the changes tracked on-screen and highlighted, check the box labeled “Highlight Changes on Screen.”
7. Implement the changes. To implement your changes click on the “Okay” button.
Read more ►

Saturday, March 12, 2011

How to Find the Author of an Excel Document in 2007


1. Open the Excel document in Microsoft Excel 2007.
2. Click the top left 'Office' button, select 'Prepare' and click 'Properties.'
3. Look at the top left 'Author' field of the 'Document Properties' panel that appears just above your spreadsheet. This field lists the author of the Excel document.
Read more ►

How to Insert a Cell Drop


Microsoft Excel 2003
1. Open the Microsoft Excel 2003 application on your computer. Enter the data you want for your drop-down box into a single column of your spreadsheet.
2. Click on the cell where you want the drop-down list to begin so that it’s selected. Click on the “Data” option from the top toolbar menu.
3. Click on the “Validation” option and then click on the “Settings” tab. Click on the “List” option from the “Allow” box.
4. Enter the reference for all of the cells to be used in the drop-down list into the “Source” box. Click on the box next to the “In-cell drop-down” field so that it’s selected.
5. Change any other drop-down settings to meet your preferences and then click on the “OK” button. Your drop-down cell box will then be created.
Microsoft Excel 2007
6. Open the Microsoft Excel 2007 application on your computer. Enter a list of data that you want for your drop-down list into a single column of your spreadsheet.
7. Click on the cell where you want your drop-down list to begin so that it’s selected. Click on the “Data” tab and then click on the “Data Validation” option from the “Data Tools” group.
8. Click on the “List” option from the “Allow” box. Click on the “Source” box and then select the column of cells you want for your drop-down list.
9. Click on the box next to the “In-cell dropdown” field so that it’s selected. Make any other changes to the drop-down list settings and your drop-down box will be created in your spreadsheet.
Read more ►

Friday, March 11, 2011

How to Create Dynamic Charts in Excel Using Data Filters


1. Organize the source data in your spreadsheet.
2. Create the type of chart specific to your data needs using the Chart Wizard tool.
3. Insert data filters to the source data by selecting 'Menu' then 'Data.' Next choose 'Filter' and then 'Auto Filter.' In Microsoft Excel 2007, use the home ribbon and then select 'Filter and Sort Button' followed by 'Filter.'
4. Apply the data filter to select the type of data you wish to use in the chart. The chart will automatically change depending upon how the data is filtered.
Read more ►

How to Add Add


1. Open Excel and click the Office button. You can open, save or print a document and view the default settings for Excel using this menu.
2. Click the 'Excel Options' button located at the bottom of the menu that opens.
3. Select 'Add-Ins' located in the options list. You can view and manage your add-ins here.
4. Click the arrow next to 'Manage' and select 'COM Add-Ins' from the drop-down list that opens and click 'Go.' A box will open that shows the add-ins that are available for you to add or remove from Excel.
5. Select an add-in you want to use in Excel and click 'Add.' The add-in will be added to Excel.
Read more ►

How to Do Add


1. Open Microsoft Excel, and click on the 'Office' button.
2. Click on 'Excel Options' located at the bottom right corner of the Office menu window.
3. Select 'Add-Ins' from the vertical navigation bar on the left of the 'Excel Options' window.
4. Check the boxes next to the add-in programs you would like to download, and then click 'OK.'
5. Follow the installation prompts to install the add-ins, and then click on 'Add-Ins' located in the toolbar at the top of the Office Excel page to load and use your add-in programs; you can also access them through the data, formula and add-in tabs.
Read more ►

How to Create High Resolution TIFF Files From Excel Files


1. Create your file in Microsoft Excel. This can be anything from an invoice to a chart to a simple listing of data.
2. Select the area of your Excel spreadsheet that you want to convert to a TIFF file. Go to the 'Page Layout' menu then click 'Print Area' and 'Set Print Area' (this option may appear under 'File' menu in some older versions of Excel).
3. Print the file to a standard postscript printer (download a free file at Adobe.com). Name the postscript file and type in '.ps' without the quotation marks as the file extension.
4. Open the .ps file in Adobe Photoshop. A dialog box will open, asking you to confirm information about the file size and resolution. Type in the resolution you would like---300 pixels per inch and up is considered 'high-resolution' in most design applications. Set 'Mode' to CMYK. Click 'OK' to continue.
5. Look at the file in Photoshop to ensure that the output is the same as you saw in Excel. The text inside of an Excel chart may be different after the conversion---if you don't like it you can delete and re-add the text elements in Photoshop.
6. Adjust the size of the image in Photoshop if you'd like. You can crop it down, make it larger and even add photos and additional text as you see fit.
7. Go to 'File' then 'Save As' on the Photoshop menu. Select 'TIFF' from the 'Format' drop down list. Click 'Save' to create the TIFF file.
Read more ►

How to Make an X Axis in Excel 2007


1. Open the spreadsheet with the data for which you wish to create a graph. Place all the X values in one column and the Y values in a second column. Each row should contain the matching values for both axes.
2. Click 'Insert' on the Menu bar and go to 'Chart.' Pick your desired picture and input the description of the graph in the box provided.
3. Move to Step 2 of the Chart Wizard to arrange the Chart Source Data. Highlight the columns and their respective headings and click 'Enter.' Choose the 'Series: in columns' button.
4. Go to Series tab and fill out the X and Y value fields. Check this area carefully to make sure that Excel 2007 correctly identifies the columns for the X and Y axes. Since you are creating an X axis, be sure that it contains the correct data for that column before hitting the 'Next' button.
5. Fill out all the other tabs in the next step for the titles of the axes as well as the legend and labels of the data.
6. Click on the next step of the chart wizard to select the position of the graph. You can choose to create the graph on a separate workbook sheet or on the same page.
Read more ►

How to Use a Letter to Represent a Value in Excel


1. Launch Microsoft Excel.
2. In the Excel spreadsheet, type a letter in the column 'A' (for example, in the cell 'A1'). Type the value, which should be represented by the letter, in the same row in the column 'B' namely in the cell 'B1.'
3.
Repeat Step 2 to add to the spreadsheet other letters you wish to associate with values. Make sure to add one such 'letter-value' entry per row. In the example shown in the figure, the array 'letter-value' spans six rows from the cells 'A1' and 'B1' to 'A6' and 'B6.'
4. In the Excel spreadsheet, select a cell in which you want to insert the value using a letter. Type the following function: '=LOOKUP('T',A1:B6)', then press 'Enter.' Note that 'A1' and 'B6' are the first and last cells in the array defined in Step 2. 'T' is the letter. In our example, the value '9.06' appears in the cell.
5. Use the form 'LOOKUP('letter',A1:B6)' as an argument for any function. For instance, select a cell and type '=SUM(LOOKUP('u',A1:B6),LOOKUP('A',A1:B6))'. Press 'Enter.' The sum of two numbers represented by letters 'u' and 'A' appears in the cell. In our example, it is 18.17 (12.5 5.67).
Read more ►

Blogger news