Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

microsoft access course - excel subtotals

Forum home » Delegate support and help forum » Microsoft Access Training and help » microsoft access course - Excel Subtotals

microsoft access course - Excel Subtotals

ResolvedVersion Standard

Graham has attended:
Access Introduction course

Excel Subtotals

When I have summarised a report using subtotals is it possibe to copy and paste a collapsed view of the table without including the lowest level of detail

Edited on Thu 21 Jun 2007, 14:17

RE: Excel Subtotals

Hi Graham

Thanks for your question. I assume your question relates to subtotals in Excel because of the title, even though it's posted in the Access forum.

If you are using subtotals to create totals, then this would be possible, but you would need to apply an advanced filter first.

Because the totals are larger than other values in the column, you can do the following:

1. Apply the subtotals (Data - Subtotals)

2. Enter the heading of the column you have put the subtotals into in a cell up the top of the spreadsheet.

3. In the cell directly underneath this, enter >X where X is a number just smaller than the smallest total you have.

4. Go to Data - Filter - Advanced filter.

5. Choose Copy to another location, select the whole list of data for List range, select the two cells that you typed information into in steps 2 and 3 above. Next to Copy to, select a blank cell on the same sheet.

6. Click OK.

This should give you just the subtotals from the list, separate from the list itself. From there you can cut and paste this information onto another sheet if you wish.

I've attached an example at the bottom of this post.

Amanda

Access tip:

Duplicating an Entry

To duplicate the entry press CTRL+' (apostrophe)this will copy the contents of the previous entry in the same field.
(Table view, line above) note this also works in Excel.

View all Access hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.