locked
Template for income/expenditure with monthly totals RRS feed

  • Question

  • Hi there, I use an Excel spreadsheet to record finances - at the moment everything is in one long list and I have a total for the year but if I want to extract monthly income I have to cut and paste and then autocalculate the total manually.  Is there a template for income and expenditure that I can use that's got everything I need built in?  I'm not very good at Excel I'm afraid, so it will need to be idiot-proof!

    TIA, 

    FP

    Tuesday, July 21, 2020 1:00 PM

Answers

  • One option would be to add a column that returns the month, for example with a formula

    =TEXT(A2,"mmm")

    Click anywhere in the data.

    On the Data tab of the ribbon, in the Outline group, click Subtotal.

    In the 'At each change in' dropdown, select Month.

    Under 'Add subtotal to', make sure that the check box for Gross is ticked, and all others are clear.

    Click OK.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Frecklepaw Thursday, July 23, 2020 10:19 AM
    Wednesday, July 22, 2020 1:52 PM
  • Another way is a pivot table based on the data:

    Click anywhere in the data.

    On the Insert tab of the ribbon, click PivotTable.

    Specify a destination for the pivot table. It can be on the same sheet, or on another sheet.

    Add the Date field to the Rows area and the Gross field to the Values area.

    Excel will automatically group the data by month. You can expand each of the months to see individual data.

    You can easily view your data categorized differently, for example by Client or by Payment type, simply by placing another field in the Rows area.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Frecklepaw Thursday, July 23, 2020 10:19 AM
    Wednesday, July 22, 2020 1:57 PM

All replies

  • Hi,

    Could you provide a sample about this problem?

    If you want to find a template to help you, try to Search for online templates:

    This “Excel IT Pro Discussions” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

    Regards,

    Emi Zhang


    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !

    Wednesday, July 22, 2020 7:56 AM
  • If I have each individual item recorded by date like this, how can I easily extract the info (in this case the monthly total from all entries) easily without it becoming a huge long list?! Hopefully I'm explaining that well enough. 

    e.g. 

    22/7/20 

    23/7/20

    26/7/20

    2/8/20 

    I need something to split up the months and provide a running total - how can this be done?  So that it's not one big long list and I need to manually extract the info but cut and paste and then doing autosum?  Does this make sense?  Is there an automated feature for this type of format, or a better format I can use?  

    Wednesday, July 22, 2020 10:55 AM
  • One option would be to add a column that returns the month, for example with a formula

    =TEXT(A2,"mmm")

    Click anywhere in the data.

    On the Data tab of the ribbon, in the Outline group, click Subtotal.

    In the 'At each change in' dropdown, select Month.

    Under 'Add subtotal to', make sure that the check box for Gross is ticked, and all others are clear.

    Click OK.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Frecklepaw Thursday, July 23, 2020 10:19 AM
    Wednesday, July 22, 2020 1:52 PM
  • Another way is a pivot table based on the data:

    Click anywhere in the data.

    On the Insert tab of the ribbon, click PivotTable.

    Specify a destination for the pivot table. It can be on the same sheet, or on another sheet.

    Add the Date field to the Rows area and the Gross field to the Values area.

    Excel will automatically group the data by month. You can expand each of the months to see individual data.

    You can easily view your data categorized differently, for example by Client or by Payment type, simply by placing another field in the Rows area.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by Frecklepaw Thursday, July 23, 2020 10:19 AM
    Wednesday, July 22, 2020 1:57 PM
  • Both genuis! Thank you! :) Will make things so much easier now as I had it all in one huge long list! 
    Thursday, July 23, 2020 10:19 AM
  • Hi Hans, what is happening with the new Q&A I've asked a question on there in the MS forums but no one has even looked at it!  I'm sad that this forum is closing down soon!!

    Do you have any recommendations on where can I get advice on MS Office related stuff in future as I get the feeling the new 'Q&A' forum will not be as good for advice. 

    I'll definitely miss your help and advice if you're not now on the new Q&A 

    Why did they have to change it? :( 

    Tuesday, August 4, 2020 1:04 PM
  • I don't like Q&A either; I decided to give it a miss.

    I'm mostly active on Eileen's Lounge (disclosure: I am one of the founders and admins of that board) and on Microsoft Community


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Tuesday, August 4, 2020 2:22 PM
  • Hi Hans, amazing - I will check it out :) 

    Thanks so much for all your help on here over the last few years, it's been a real lifeline to me. You always know what to do!  

    FP  

    Wednesday, August 5, 2020 7:53 AM
  • You're welcome, it was a pleasure helping you. All the best!

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Wednesday, August 5, 2020 9:48 AM