Tuesday, July 19, 2011

Copy Excel subtotals without making a mess

Takeaway: After using Excel’s Subtotal feature, you can quickly collapse the outline to view only the subtotal rows. You might get a surprise if you try to copy them though - here’s how to avoid a Subtotal gotcha!

In the last entry, I showed you how to use Excel’s Subtotal feature to count elements in a group. Today, I want to share a minor glitch when using this feature. I call it a glitch, but Excel’s not doing anything wrong - it just doesn’t do what you expect! Once you add subtotals, you can collapse the outline to view just the subtotal rows. If you try to copy only the subtotal rows, you’ll get a surprise - Excel copies the subtotal and detail rows, not just the subtotal rows. Fortunately, there’s an easy fix. Use Go To Special to select only the visible cells before copying.
Let’s look at a quick example using the sheet show below. Specifically, we’ll use Subtotal to find the average unit price of each category. Then, we’ll copy the subtotal rows and see what happens.

The first thing you have to do when using Subtotal, is to sort the data - you’ll want to do this with any Subtotal task. With the data sorted, you’re ready to subtotal the unit price values as follows:
  1. Select any cell in the data range.
  2. Click the Data tab and then click Subtotal in the Outline group. In Excel 2003, Subtotal is on the Data menu.
  3. In the resulting dialog box, choose Category from the At Each Change In dropdown.
  4. Select Average from the Use Function dropdown.
  5. Check the Unit Price field in Add Subtotal To.
  6. Click OK and Excel will display the average unit price for each category.

To view only the subtotal rows, click 2 in the outline symbols to the left. Now, copy these rows and see what happens. As I warned, Excel copies all the rows, not just the subtotaling rows.

Now, here’s the easy fix:
  1. Select the range you want to copy. In this case, that’s C4:G66 - that’s 62 rows! It might look like just a few subtotaling rows, but if you check the row headers, you’ll see that Excel’s just hidden the detail rows. (If you don’t select the range first, you’ll end up with a mess.)
  2. Press [Alt]+;, which is the equivalent of pressing [F5], clicking Special at the bottom of the Go To dialog box, clicking Visible Cells Only to select only the visible cells in the selected range, and then clicking OK. (That shortcut’s worth of an entry of its own!)
  3. Press [Ctrl]+C to copy the selected visible cells to the Clipboard.
  4. Select a target cell and paste the range by pressing [Ctrl]+V. Excel copies only the subtotaling rows. You can copy to another area of the same sheet, to a different sheet, or even to a new workbook.

Well, maybe you knew all that already. I see this question a lot, but I think many people who use Excel a lot know how to get around the subtotals copy problem. So, let’s explore a bit further. If this is something you do often, add the Select Visible Cells option to the Quick Access Toolbar (or the menu). If you’re using Excel 2010, do the following to add this option to the QAT:
  1. From the QAT dropdown, choose More Commands.
  2. From the Choose Commands From dropdown, choose All Commands.
  3. Thumb down and select Select Visible Cells.
  4. Click Add and then click OK to return to the sheet.
In Excel 2003,
  1. Select Customize from the Tools menu.
  2. Click the Commands tab.
  3. Select Edit in the Categories list.
  4. From the Commands list, select Select Visible Cells and drag it to the menu or toolbar.
  5. Click Close.
Now you won’t even have to press [Alt]+;–just click the option on the QAT (or the menu).

A word of warning when using Subtotal to average: Excel evaluates the derived values to calculate the grand total - not the actual detail data. Most of the time that won’t matter, but if you’re dealing with serious precision, you’ll want to watch for discrepancies.

Placement is everything when using aggregate functions in Access reports

June 27, 2011, 11:27 AM PDT
Takeaway: The same aggregate function will return different results in the same report. It’s important that you understand how placement determines how an aggregate evaluates values in an Access report. http://www.techrepublic.com/blog/msoffice/placement-is-everything-when-using-aggregate-functions-in-access-reports/5321?tag=nl.e056

Friday, February 18, 2011

What is cloud computing?

Cloud computing is Internet-based computing, whereby shared resources, software and information are provided to computers and other devices on-demand, like electricity.

Cloud computing is the management and provision of applications, information and data as a service. These services are provided over the internet, on a consumption-based model.  I.e. pay per usage.

Cloud provides a convenient way of accessing computing services; it does not depend upon your hardware used or your location.  The cloud eliminates the need to store information on your PC, mobile device or gadget with the assumption that the information can be quickly and easily accessed via the net.  There is no need to download or install dedicated software on your own computer which frees up onboard memory thus reducing energy costs.

We already have a lot of people using cloud computing today without realizing it. An excellent example of cloud technology is Google which offers software as a free online service to billions of users across the world. Google hosts a set of online productivity tools and applications in the cloud such as email, word processing, calendars, photo sharing, and website creation tools.

Thursday, February 17, 2011

MS`Excel - Inserting a watermark


Inserting a Watermark
- Click the Insert tab
- Click WordArt from the Text group
- Select one of the transparent styles
- Type the text for the 'watermark' and click away
- Rotate using the handle in the middle and

position as required
- If the fill is not transparent enough, from the

 Drawing tab, select and change to No Fill. 
Similarly select Text Outline and
change as required

Netlogistix introduces Microsoft Excel classes in Mississauga

Netlogistix is introducing Microsoft Excel classes in Mississauga.  The first 2 classes will be Excel Level 1 (March 10/2011) and Excel Level 2 (March 31/2011).  Visit http://www.netlogistix.com/