Microsoft Excel and Other Office Apps - Qs&As
‹ Previous1234567
  • There have been quite a few questions about how to do certain things in office, mainly excel with its myriad of mysteries. So here's a thread in case you need it. Here was my question :

    I have high, medium, low priority items and then completed. High = red, medium = amber, low = green and complete = blue. I want to make it so the priority column had drop downs in it, and then when I select high it automatically colours the row red. Oh and also the whole table be in order so high is at top and complete at bottom.

    Muzzy said:

    For the drop down you want data validation, choose from a list (which you either type in, or choose from a set of cells if you have "high"/"low"/"blah" in cells)

    For the colouring, you need conditional formatting. There's a wizard, but you'll need to be a bit careful to get the whole row referencing the right column.
    I'm falling apart to songs about hips and hearts...
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    Edit:

    Not in response to Matty, apologies.

    Is it possible to do a box plot chart in excel as part of a combo chart?

    For example, I have 3 groups, a, b and c, each containg a number of individuals (number not important). Members of the groups have been taking IQ tests for a couple of weeks. Not all groups have taken the same amount of test though.

    What I would like to do is show the range of each groups scores as a box and whisker plot (not an issue) but have a line chart (or similar) plotted on a secondary axis to show total number of tests taken by each group. Is this possible, or is there a better way to do it?
  • Excel for that sort of stuff is fine, but I can 100% recommend Planner as a O365 app that can help you manage your workload without messing around with setting it up. It’s all configured for you and you just type out the projects/tasks/allocation/priority/deadline etc., and it has a lovely dashboard to help you track them.
  • davyK
    Show networks
    Xbox
    davyK13
    Steam
    dbkelly

    Send message
    Only advice I can offer re Excel is that it's a powerful tool but you can get yourself into a pickle with it very quickly. It can become an obsession for some and then it has you - with your working life subservient to its weaknesses.
    Holding the wrong end of the stick since 2009.
  • Bump. So I'm doing lots of stuff with with excel in the new gig. And it's fun.

    Sorting a lot of forms etc, trying to work out how to be able to search in a drop down. 

    ie, blank cell, I type in Da and it skips to all names with Da in them and I can select one.

    I know it's possible. As a wizz at my last job did it, what I can't recall is exactly how, and I need to test how much it'll freak out people's laptops.



    This isn't quite what I mean, and it's also 365 specific, and the work excel is 2016.
    I'm still great and you still love it.
  • Both useful. But not for this. Because my "search box" would be in up to 2100 cells. And the results end up in there too.

    Might have to message the dude in the new year.

    Might be a moot point because data limits for email attachments and all sorts of stuff.

    I already ran into that issue with a spreadsheet with pretty basic conditional formatting.

    I'm still great and you still love it.
  • Pivot Tables. My staff are obsessed with them.

    I have a rudimentary understanding of Excel so I just get them to do it all.
    Gamertag: gremill
  • Pivot tables are funny. I've only just had to play with them, but they seem to have some limitation.

    Basically, the pro is they pretty well automatically get you to a graph that you can rearrange easily quickly.

    The con is there's a bunch of charts you can't use and it seems pretty easy to create tables as is then mess with all the charts you want.
    I'm still great and you still love it.
  • Setting up a desk roster for work as we slowly go back to higher capacity. (I don't have to go in. Yay)

    Classic excel issues: it's not just name, desk day, we also need to show who's got cleaning duty and department etc.

    Its always those extra bits that make a good spreadsheet look messy real quick.
    I'm still great and you still love it.
  • acemuzzy
    Show networks
    PSN
    Acemuzzy
    Steam
    Acemuzzy (aka murray200)
    Wii
    3DS - 4613-7291-1486

    Send message
    Pivot tables are the best shit.

    A pivot graph is a different thing, though, isn't it? I guess they're related, but I've only used the table variant.
  • mannaboy
    Show networks
    Steam
    mannaboy
    Wii
    mannaboy

    Send message
    I may have been missing out for a long time, but formatting data as a table (not a pivot table) makes working with raw data so much easier, particularly when using that table elsewhere, be it pivot tables, vlookups, etc.

    I got it in my head formatting data as tables was cosmetic, but of course it’s much more than that, helps with enforcing column types and referring to the table’s name when linking to it, rather than cell ranges with particular syntax.
    Things can only get better.
  • I just had my mind blown by alt a when I was doing the linkedin learning basics course.
    And yeah with tables, alt T.

    When in doubt, there's probably an alt or shift option you've missed.
    I'm still great and you still love it.
  • Anyone done much with mail merge?

    Sorted spreadsheet, have done my first batch of letters. It's all good, except I can't easily create different individual files.

    It creates 41 pages in 1 doc.

    Google tells me macros is the answer. That's quite a "my first macros."

    I'm still great and you still love it.
  • I just cut and pasted into new docs today. But I'd like to find a better solution. I suspect I'll have at least 2 more batches of 41 letters to do.
    I'm still great and you still love it.
  • davyK
    Show networks
    Xbox
    davyK13
    Steam
    dbkelly

    Send message
    When you print the doc are the letters not on individual pages?
    Holding the wrong end of the stick since 2009.
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    Are you wanting to seperate out into seperate documents for each letter? Also, is there a reason you're doing it in excel and not word?
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    This is how you would do it in word, BTW.
    Create individual files based on a Mail Merge.

    1. Your template should contain a Heading 1 at the top of the letter – This can be hidden text (i.e in white font).


    2. Finish and merge to edit individual documents


    3. In the newly created document Select View from the top tab options


    4. Select Outline in the views area


    It will provide an outlined view of the document:


    5. Select ALL (Ctrl +A)


    6. Click Show Document


    7. Click Create to create a sub-document


    This will create your individual documents:


    8. Click on Collapse Subdocuments


    9. Save as per instructed to your desired folder:


    This will then save your current document as a Master Document with links to the individual documents:


    You can then open each file by clicking on each link

    Or,

    Open the folder you saved your individual documents to and open form there.
  • 1. Excel is where the source data is for all the names etc. Word is where the letter template is. Excel is defo where we want the list, because I have addresses and all sorts. That part is easy.

    2. I'm not doing the other end of things. I'm just getting the letters ready. They've requested sep files. (actually, I know why, they'll end up saved in the case files for relevant clients, so yeah, need to be seperate.)

    Yes, they're on seperate pages.

    I'm still great and you still love it.
  • This is how you would do it in word, BTW.
    Create individual files based on a Mail Merge.

    1. Your template should contain a Heading 1 at the top of the letter – This can be hidden text (i.e in white font).


    2. Finish and merge to edit individual documents


    3. In the newly created document Select View from the top tab options


    4. Select Outline in the views area


    It will provide an outlined view of the document:


    5. Select ALL (Ctrl +A)


    6. Click Show Document


    7. Click Create to create a sub-document


    This will create your individual documents:


    8. Click on Collapse Subdocuments


    9. Save as per instructed to your desired folder:


    This will then save your current document as a Master Document with links to the individual documents:


    You can then open each file by clicking on each link

    Or,

    Open the folder you saved your individual documents to and open form there.

    Oooh, this looks promising! Thanks.
    I'm still great and you still love it.
  • davyK
    Show networks
    Xbox
    davyK13
    Steam
    dbkelly

    Send message
    Interesting. I've only ever done bulk letter generation out of systems. It makes sense to have different files if being stored in a case management system.
    Holding the wrong end of the stick since 2009.
  • Goober, you are a genius. Added name fields to the heading section and it saves exactly as I want it to as well.
    I'm still great and you still love it.
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    Happy days.
  • Lolz, thought I'd try macros on one of my other spreadsheets on my own laptop. Even it chugs along trying to deal.
    I'm still great and you still love it.
  • Dammit, got my first macros to work, on my laptop, asked IT for macros to be turned on on my work laptop, they've obliged, and it's still saying I need to turn on permissions for macros. Even though I can see they've changed the setting for me and they are enabled.
    I'm still great and you still love it.
  • Accept my steam fr Goober, ya big Jessie.
    I'm still great and you still love it.
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    Ah, will do. My pc is broken still so haven't been on in a while.
  • For my sins I am now dealing with office 365 etc browser based office suite. So forms and Power Automater etc.

    Jfc MS know how to fuck up simple things.
    I'm still great and you still love it.
  • Sharepoint/teams have some great aspects, but then also absolutely baffling counter-intuitive shite.

    Trying to get a sharepoint list to populate from a form excel sheet. Should be obvious and easy. And it's just..... Not populating for no reason.
    I'm still great and you still love it.
  • GooberTheHat
    Show networks
    Twitter
    GooberTheHat
    Xbox
    GooberTheHat
    Steam
    GooberTheHat

    Send message
    The power automate flow stuff of office 365 always baffles me, but I think it's as much to do with the arbitrary, undisclosed restrictions my group admin have enacted as much as it is O365
‹ Previous1234567

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!