 
|
In a workbook, you might have some sheets that everyone uses, and other sheets that only one or two people need to use, for Admin functions. For example, the workbook shown below has a data entry sheet for orders, and two Admin sheets -- one for lists and one for workbook options. To make [...] Occasionally, I get calls from clients who don't understand why their Excel file isn't working. They're clicking buttons, or selecting from drop down lists, but none of the usual magic is happening. Is the file broken? When you open a workbook in Excel 2007, a security warning bar might appear above the worksheet, telling you [...] I saw this on Twitter yesterday: Wanted "Break Apart all merged cells in entire spreadsheet" button If you've done much work in Excel, you've probably encountered problems that are the result of merged cells on a worksheet. Merging cells can seem like a good idea at the time, but can interfere with sorting and filtering, [...] To highlight specific cells on an Excel worksheet, you can use conditional formatting. In the example shown below, orders with a quantity greater than 50 are highlighted with green fill colour. This was the result of simple conditional formatting, based on the cell value. Sometimes though, the conditional formatting can be distracting, and [...] You can use the VLOOKUP function to find data in a lookup table, based on a specific value. If you enter a product number in an order form, you can use a VLOOKUP formula to find the matching product name or price. In some Excel workbooks, you might need to pull data from a specific [...] If you're entering dates on an Excel worksheet, and there's data in the adjacent column, you can use the Fill handle to make the job quick and easy. Enter the first date in the date column, then point to the Fill handle, and double-click.
That fills the dates down to the first blank cell [...] Last month, I showed you how to drag information from a web browser into Excel. You can also drag text files, to open them quickly in Excel. I find this a really quick way to open a text file, especially if Windows Explorer is already open. Instead of using the Open command, or the Text [...] In the last blog post, you saw how to turn off buttons and drop downs in an Excel 2007 pivot table. In the comments, Jon Peltier suggested a simple Copy and Paste Values instead, but that doesn't paste the pivot table style formatting. Then, John Walkenbach pointed us to one of his articles, which [...] If you're sharing an Excel pivot table with colleagues who aren't too skilled in Excel, you might want to hide some of the buttons and drop downs in the pivot table before you send it. For example, in the pivot table shown below, the sales are summarized by city and product, and has filter buttons [...] Every now and then I get a workbook from a client with numbers in Accounting format. If all the numbers are the same length, the currency symbols line up nicely. However, if the numbers are different lengths, some of the symbols are a bit off. For example, in the screen shot below, the $ sign [...]  |
|
|
Copyright © 2012 Contextures Blog - All Rights Reserved |
||
