+ Reply to Thread
Results 1 to 20 of 20

Excel file optimisation

  1. #1
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Excel file optimisation

    Hello!

    I have been working for 2 weeks on a file (see attached). I understand that for you, professionals in Excel, it will sound risible that a person can spend so much time on such file, but for me, as a beginner, it did take a lot of ,a truly lot of effort to come up with this result. Initially I have started it for a simple reason to learn Excel as it is very useful, and it did turn out to be very interesting and challenging.

    On Saturday night I almost lost the whole work, but have managed to recreate the file. And now I even think that I might use it at might work, although no one, except me, needs anything of the sort - everyone and everything is on the cloud, and on a customized software.

    Still, I would like to optimize it. Please, take a look at my questions and issues I have with the file. I will be grateful for your help and your opinion. I did put the copyright sign there, but it's merely for fun, and, of course, if any of you find this type of file useful, I will be happy if you use it for your purposes. And also, I used lots of templates I found on the web to come up with this result.

    1) The file is very very heavy - 16MB. For your convenience, I am attaching a much smaller version of only 3.2 MB because I have deleted 8 additional tabs, which are completely the same as tab1 and tab10. This size seems to be absurd. All these 16MB come from formatting and formulas. How can I optimize it? How can I decrease the size while still keeping nicely looking formatting with those interesting features I added?

    I couldn't upload it here, so I put it on the google drive - here the max size is 1000KB.
    File

    2) Formatting is changing constantly by itself; I can't predict when and how. It can change when I change some cells, or when I add rows, or just when I open the file on another day. Drop-down lists also can change. And that happens seemingly on a whim (of course, not on whim, but as I don't know the reason, it seems to be like that).

    Thank you very much!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    I did put the copyright sign there, but it's merely for fun, and, of course, if any of you find this type of file useful, I will be happy if you use it for your purposes. And also, I used lots of templates I found on the web to come up with this result.
    I hope you see the irony in what you are saying, even if the copyright symbol is 'for fun'. I'm really glad that you'll be happy for us to use the things that we have helped you to create - that's very generous of you.

    In terms of file optimisation, the excessive use of conditional formatting will make the file large and slow it down, so you need to cut back on that. Most of the 'interesting features' are what I would call 'unnecessary bells and whistles' and add nothing at all to the workings of the spreadsheet, as far as I can see. I do not really understand the purpose of the spreadsheet - much of it is in a language I don't speak (Russian?), and there are no instructions about how it is meant to be used or what it is supposed to do.

    I do not understand your second point at all about things changing on a whim, unless you are constantly changing the format of the file between a Google Sheet and an Excel file - perhaps you could elaborate on this?

    All in all, this is an apprentice piece, and it has been useful to you as a learning tool. However, it is overly complex, and bespoke to whatever it is you want it to do, so really cannot be seen as a template that could be adapted easily for someone else's purposes.

    I hope this critique is useful to you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    Mixed russian/english
    removed all not neccessary formats - 270 kb

    edit: Times New Roman changed to Calibri , font size: 9 (I hate TNR)
    freezing - removed
    orientation - removed
    merged cells - removed
    two blank sheets (hidden also) - removed
    Instruction "How to use it" - required

    that's all ( I hope )
    Last edited by sandy666; 08-08-2017 at 04:18 AM.

  4. #4
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    Quote Originally Posted by sandy666 View Post
    Mixed russian/english
    removed all not neccessary formats - 272 kb

    edit: Times New Roman changed to Calibri , font size: 9 (I hate TNR)

    Instruction "How to use it" - required
    Thank you for your reply.
    1) Do you mean that a mixture of languages is a bad idea, and it's better to stick with English? Does this mixture add to size?
    2) Which formats did you remove, and which you think are not necessary? Please, explain your view point, so I will be able to make changes.
    3) Why do you hate TNR? ) What's so bad about it? I am very keen to understand the topic about languages.
    4) Yes, I did miss the instruction, as I did it for learning purposes. Sorry about that.

    Also, I thought what if I add tables, and put everything in tables' formats. Do you think it can help to optimize things?

  5. #5
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    Quote Originally Posted by AliGW View Post
    I hope you see the irony in what you are saying, even if the copyright symbol is 'for fun'. I'm really glad that you'll be happy for us to use the things that we have helped you to create - that's very generous of you.
    I would truly advice you to take things in a more light hearted fashion. ) Yes, of course, people here helped me a lot with my understanding of formulas, and I did stated many times how grateful I am, and this is also why I shared the result, though, of course, far from everything I did there comes from the help I got here.
    Last edited by AliGW; 08-08-2017 at 04:50 AM. Reason: Post de-cluttered for thread legibility.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    Quote Originally Posted by Vitalite View Post
    I would truly advice you to take things in a more light hearted fashion. ) Yes, of course, people here helped me a lot with my understanding of formulas, and I did stated many times how grateful I am, and this is also why I shared the result, though, of course, far from everything I did there comes from the help I got here.
    Who is not taking things in a light-hearted fashion? Did you not see the wink emoticon???

    I see you are still cluttering threads with unnecessarily long quotations, which is ironic since the only thing you have responded to in my thread is the opening paragraph!

    Have you read the other feedback I gave?
    Last edited by AliGW; 08-08-2017 at 04:30 AM.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    @1 No, you can use languages as many as you want but create dictionary for used languages and choice for users between them
    @2 All backgrounds and some conditional foramtting. All borders - default,
    @3 TNR was imposed by MS long time ago as default in Word. In Excel it was Ariel (If I remember well) - but it should be my choice which kind of font is used, isn't it?
    @4 no problem, I asked for instruction because I don't know what I can do with this file
    @Tables (structured or range?) Structured tables will increase file size. But this is your choice.

    edit:
    there is many DataValidation with many blank rows (look for DynamicDataValidation List on this forum)

    dvwithblankrows.jpg
    Last edited by sandy666; 08-08-2017 at 04:48 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    3) Why do you hate TNR? ) What's so bad about it?
    It's a pretty well-known fact that serif fonts read better on hard copy prints (letters, etc.) and sans serif fonts read better on screen. I always use Calibri, which is a pleasant font on screen and not as hard on the eyes as Arial.

  9. #9
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    Thank you very much. I will switch to Calibri now, hopeful this will ease the great pressure on eyes, especially after 16 hours at the computer.
    Last edited by AliGW; 08-08-2017 at 04:46 AM. Reason: Unnecessary quotation removed.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    You're welcome!

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    Do you have any questions or can I delete the file?

    no answer: file deleted
    Last edited by sandy666; 08-08-2017 at 05:20 AM.

  12. #12
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    Quote Originally Posted by sandy666 View Post
    Do you have any questions or can I delete the file?

    no answer: file deleted
    Why did you delete the file? I needed it, because I do have questions. You have to understand that sometimes people can't get to answering right away - there might be circumstances. This is a horrible move.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    You want me to keep your file 4 days????
    I can re-download file from your first post but without my correction of course.

    If you correct something in your file then the first file will be outdated. If so attach a new updated file
    Last edited by sandy666; 08-12-2017 at 02:58 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    Sandy - please do not delete solutions provided in workbook format. They need to stay here for future reference (not just for the OP, but others using this thread for help in the future) and are part and parcel of the thread. The only time we remove attachments is if someone has accidentally posted sensitive information. Please put the attachment back. Thanks.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    I didn't delete anything from here, just deleted from my HD only

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    Understood. Thanks.

  17. #17
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    But I don't see my file as well, not only the file Sandy provided. ( Anyway, I marked the thread solved. I will ask other questions in another thread.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    Re: Excel file optimisation

    There's a tiny blue link ("File") in your opening thread to a Google Drive document - you posted it there, I think, because it was too huge to post here.

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel file optimisation

    your file is on Google Drive (yet, until you remove it) with link in the first post.

    and I repeat: I didn't attach any file to this thread.
    Last edited by sandy666; 08-12-2017 at 03:36 AM.

  20. #20
    Forum Contributor
    Join Date
    05-20-2017
    Location
    Boston, US
    MS-Off Ver
    Mac 2011
    Posts
    139

    Re: Excel file optimisation

    Quote Originally Posted by AliGW View Post
    There's a tiny blue link ("File") in your opening thread to a Google Drive document - you posted it there, I think, because it was too huge to post here.
    Ah, yes. ) I panicked a bit. ) Thank you! I forgot that I did download it to google drive as the file is indeed too huge. ) Sorry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to solve such an optimisation problem?
    By andre199017 in forum Excel General
    Replies: 0
    Last Post: 01-18-2017, 12:58 PM
  2. optimisation
    By rtedesco in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-03-2016, 08:42 PM
  3. Workbook optimisation
    By tuph in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-05-2015, 04:31 PM
  4. Solver using VBA for portfolio optimisation
    By natalie28 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-16-2014, 04:16 AM
  5. Optimisation of code
    By munkee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2009, 10:26 AM
  6. Downloadable optimisation programs (Excel 2007)
    By Sam_Gregson in forum Excel General
    Replies: 5
    Last Post: 04-08-2009, 10:17 AM
  7. Help with optimisation problem setup
    By kostas in forum Excel General
    Replies: 0
    Last Post: 06-06-2008, 09:52 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1