98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel 2010
Excel 2010
Resolved · Urgent Priority · Version 2010
Wed 29 Jan 2014, 10:51Delegate Hindu said...
Hindu has attended:
Excel VBA Intro Intermediate course
Excel Advanced course
Excel 2010
Hi,
I have a report that I export to excel from our payroll system. For example Pension Report. Unfortunately this report comes with subtotals already in the report. Is there a way I can get rid of the subtotals. Its hard to explain unless I send you the file for you to see.
Basically the report comes like this. All I want from the report is the subtotal.
For example EE 22 total ER is £630.54
EE56 total ER is £1128.21. How can I best achieve this?
Number Name Year Prd Type Input val Calc amount
22 xxxxxxx 2013/2014 9 Ers 70.06 70.06
2013/2014 8 Ers 70.06 70.06
2013/2014 7 Ers 70.06 70.06
2013/2014 6 Ers 70.06 70.06
2013/2014 5 Ers 70.06 70.06
2013/2014 4 Ers 70.06 70.06
2013/2014 3 Ers 70.06 70.06
2013/2014 2 Ers 70.06 70.06
2013/2014 1 Ers 70.06 70.06
Totals 630.54 630.54
56 xxxxxxx 2013/2014 9 Ers 124.29 124.29
2013/2014 8 Ers 124.29 124.29
2013/2014 7 Ers 124.29 124.29
2013/2014 6 Ers 124.29 124.29
2013/2014 5 Ers 124.29 124.29
2013/2014 4 Ers 124.29 124.29
2013/2014 3 Ers 124.29 124.29
2013/2014 2 Ers 137.09 137.09
2013/2014 1 Ers 121.09 121.09
Totals 1128.21 1128.21
66 xxxxxxx 2013/2014 9 Ers 118.26 118.26
2013/2014 8 Ers 118.26 118.26
2013/2014 7 Ers 118.26 118.26
2013/2014 6 Ers 118.26 118.26
2013/2014 5 Ers 118.26 118.26
2013/2014 4 Ers 118.26 118.26
2013/2014 3 Ers 118.26 118.26
2013/2014 2 Ers 130.36 130.36
2013/2014 1 Ers 115.24 115.24
Totals 1073.42 1073.42
Regards
Hindu
Wed 29 Jan 2014, 14:18Trainer David said...
RE: Excel 2010
Hi Hindu
If I understand you correctly you are trying to hide certain rows of your report.
I believe a relatively straightforward way to achieve this would be to manually group the data as outlined below.
1. Select the row(s) which are not required to display. (You can select non-adjacent ranges by holding Ctrl as you select.)
2. In the ribbon area, from the Data tab > Outline choose Group > Group.
3. The - and + signs which appear next to the selected rows can then be used to collapse or expand sections of data as required.
4. The 1 2 3 buttons can be used to switch between different levels of detail within the data.
I hope this answers your question. Please let me know if you need any further clarification.
Kind regards
David
Wed 29 Jan 2014, 14:18Trainer David said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Thu 30 Jan 2014, 11:53Delegate Hindu said...
RE: Excel 2010
Dear David,
No this does not answer my question.
1) There are over 1,000 entries, so cant go and select them manually as will take took long
2) I get Error message “ The command you chose cannot perform multiple selections - Select single range and lick the command again.
Even it did work it wont help me as I would like my finish report to be like this
Number Name Total
22 Smith John 630.54
56 Ecclestone, A 1128.21
66 McDonald S 1073.42
but my report comes out like this
22 Smith John 2013/2014 9 Ers 70.06 70.06
2013/2014 8 Ers 70.06 70.06
2013/2014 7 Ers 70.06 70.06
2013/2014 6 Ers 70.06 70.06
2013/2014 5 Ers 70.06 70.06
2013/2014 4 Ers 70.06 70.06
2013/2014 3 Ers 70.06 70.06
2013/2014 2 Ers 70.06 70.06
2013/2014 1 Ers 70.06 70.06
Totals 630.54 630.54
for all the emplyees, for the payroll number and name is at the top but the totals of 630.54 is at the bottom. I cant do anything about how the system report comes out but I want to change it to the example I've given you above.
Regards
Hindu
Thu 30 Jan 2014, 14:59Trainer David said...
RE: Excel 2010
Hi Hindu
I apologise if my suggestion was not appropriate - and you are right, it is not possible to outline multiple rows (I hadn't counted on that).
I need to discuss with one of my colleagues how we can best support you with this as without seeing the file it is quite difficult to know what to suggest.
I will aim to get back to you by 5pm on Friday.
Regards
David
Thu 30 Jan 2014, 16:04Delegate Hindu said...
RE: Excel 2010
Hi David,
Would you like me to send you a sample file. For you to have an idea at an email address.
Regards
Hindu
Fri 31 Jan 2014, 11:24Trainer Gary said...
RE: Excel 2010
Hi Hindu
Thanks for getting in touch. If you send the file through to info@stl-training.co.uk I'll make sure it gets to David.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 3 Feb 2014, 09:15Trainer David said...
RE: Excel 2010
Hi Hindu
Just a quick note to keep you updated. I received the file which you sent late on Friday and am looking into it now.
I will update you once I have a proposed solution.
Kind regards
David
Mon 3 Feb 2014, 09:53Trainer David said...
RE: Excel 2010
Hi Hindu
I believe I have found a potential solution using a macro. I will outline the steps I followed and have attached a file with macro attached.
Initially I deleted columns C-G as it appears these are not required.
I typed the word "Total" in cell C1
I then made Cell A2 the active cell.
On View > Macros I switched on "Use Relative References"
I clicked on "Record Macro" and gave it a name e.g. delete_rows and a keyboard shortcut e.g Ctrl +d
I then selected rows 3-10, right-clicked and chose Delete
I cut the contents of Cell B3 and pasted into Cell C2
I then selected rows 3 and 4 and right-clicked and chose Delete.
I then selected cell A3 to make it active cell.
I then stopped the macro recording.
At that point I saved the file as a macro-enabled workbook.
Once done selecting the number of the next employee down and running the macro (repeatedly) enables a user to quickly reformat all the other records in the same way.
I hope that this helps
Kind regards
David
Attached files...
Mon 3 Feb 2014, 10:00Delegate Hindu said...
RE: Excel 2010
Hi David,
Sorry how do I see the file that you've attached?
Regards
Hindu
Mon 3 Feb 2014, 10:05Trainer David said...
RE: Excel 2010
Hi Hindu
I believe it should be available to download or open directly at the bottom of my last reply.
Kind regards
David
Mon 10 Feb 2014: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Highlighting only Text cellsTo select onlt text value cells in a spreadsheet, click on Edit-Go to(F5) |
