jump to navigation

Tip Microsoft Excel June 5, 2008

Posted by gisbp in ILMU.

Easy Excel Searches 


Excel’s AutoFilter converts the uppermost cell of each column in a list into a searchable drop-down list box. The list box contains the set choices All, Top 10, and Custom, then a sorted list of the column’s unique values. With one click you can instantly filter your entire list based on the current column’s contents. To turn on AutoFilter, select any single cell inside your list, then select ‘Data->Filter->AutoFilter’. To turn AutoFilter off, deselect this option. The Custom choice will display a dialog box for more complex queries. For queries the basic AutoFilter dialog box can’t handle, use Excel’s Advanced Filter feature.


Filenames In Footers 


With Excel 2002, Microsoft added the ability to include the path to an Excel worksheet in its header or footer. The path is automatically updated if the file’s location changes. Do this by selecting ‘View->Header and Footer’ (or ‘File->Page Setup->Header/Footer’) and clicking on the ‘Custom Header’ or ‘Custom Footer’ button. The button with the folder icon adds the necessary code ‘&[Path]&[File]’ to the selected area of the header or footer. While you could select the path and filename from the list of preset headers/footers, a custom one lets you format the text and add an image, too.


Import Over 256 Columns in Excel 


You probably know that you can not insert a list with more than 256 lines in an Excel spreadsheet. You can fairly easily import them into multiple worksheets though. First, open a blank Workbook and choose Data->Import External Data->Import Data’ from the menu. Select your CSV file as the import source. In the Import wizard, choose ‘Delimited’ and click ‘Next’. Check the box next to ‘Comma’ and click ‘Finish’. Excel will now ask where to put the data. click OK to accept the default location. You’ll get a warning that some of the data won’t fit. Click OK to continue regardless.Scroll all the way to the right-most column (column IV) in the imported data and take careful note of the data in the first few rows. Quite likely the data in row 1 will be the field name corresponding to the last imported column. Now select ‘Sheet2’ in your Workbook and repeat the process of importing the CSV file. This time, after checking the box next to ‘Comma’, click ‘Next’ rather than ‘Finish’. Scroll to the right until you see the last column that was imported the first time around. Click on its column header, then scroll all the way to the left and Shift-click on the first column’s header. Find the option titled ‘Do not import column (skip)’ in the panel at top right and click it. Now click ‘Finish’ to import the remaining columns. Of course if the total number of columns is greater than 512, you can repeat the import as many times as necessary, each time using a new worksheet and skipping the already-imported columns.




Link A Text Box To A Cell 


To link a text box to a worksheet cell, first, create a text box. Next, click inside the text box. Then, in the Formula bar, type an equal (=) sign. Finally, select the worksheet cell that contains the data or text you want shown in your text box and press ‘Enter’. You may instead choose to type the reference to the worksheet cell. But remember to include the sheet name followed by an exclamation point, for example, Sheet1!F2, before pressing Enter. You can add a text box to your worksheet by using the text box tool on the Drawing toolbar.


Use Two Formats In One Cell 


In older versions of Excel, you could apply different formats to the text in the same cell, but it was awkward. You had to use keystroke combinations to turn on a particular format, type the text, and then turn the format off. Office 97/2000 lets you use format buttons. For example, first, select any blank cell and enter the text ‘abc’. Next, press ‘Alt+Enter’ and type ‘def’. (Both strings should be in the same cell, but on two different lines.) In the ‘Formula’ bar, highlight ‘abc’ and then click the ‘Bold’ button on the Formatting toolbar. Highlight ‘def’ and click the ‘Underline’ button. You won’t notice any change in the Formula bar, but the cell will display the two strings and their formatting. ‘abc’ should be in bold and ‘def’ should be underlined. You’ve applied two different formats to the same cell.


View Which Cells Affect Which 


To find out which cells affect which, select ‘Auditing’ from the Tools menu, then choose ‘Show Auditing Toolbar’. Click a cell with a formula, then select ‘Trace Precedents’, and arrows to the cells that are used to calculate the value of this particular cell will appear. Click the ‘Trace Dependents’ button again, and arrows to the cells that use this particular cell in their formulas will appear. To clear the arrows, click the ‘Remove All Arrows’ button.




No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: