How to Select Excel Sub-Totals

ExcelJust a quick one for all you Microsoft Excel users.

I had a problem a moment ago whilst comparing 800 records from a SQL query against 8000 rows that I had rolled up into 800 subtotals in an Excel 2010 spreadsheet.

It doesn’t seem possible to paste my SQL rows alongside the the Excel subtotals so I tried the other way round.

No  surprises but just doing a straight select -> copy -> paste doesn’t cut the mustard, copying everything, subtotals and all.

Go To

Well, a quick google brought me to this useful tip on MrExcel.com

Once you’ve selected your data you just need to go to the “Edit” menu, select “Go To” and then click the “Special” button.

This offers up a handy list of useful options , including the option to choose “Visible Cells Only”.

Click OK and the clipboard is now full of all your subtotals; subtotals that can be pasted as a fresh set of data, just as it appeared when you selected it.

Go To SpecialThe only problem with this is that Microsoft ditched their menu bar a couple of versions ago and if, like me, you keep up to date with Microsoft Office, then you might struggle to find the “Go To” option.

And that’s where I can add a little extra to the mix – try pressing the Alt + E keys and then the G key; the Go To window should be open for your pleasure,

They may have taken the menu bar away from us but the short-cut keys still work…

And I’m damned if I can find “Go To” in amongst the ribbon toolbars.

Advertisements

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