Microsoft Excel and Other Office Apps - Qs&As - The Bear and Badger

MattyJ

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.

GooberTheHat

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?

Syph79

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

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.

Facewon

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.

GooberTheHat

https://www.google.com/amp/s/www.thespreadsheetguru.com/blog/2014/11/3/filtering-search-box?format=amp

Is that helpful?

GooberTheHat

https://trumpexcel.com/dynamic-excel-filter/ or maybe this, looks cleaner.

Facewon

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.

Gremill

Pivot Tables. My staff are obsessed with them.

I have a rudimentary understanding of Excel so I just get them to do it all.

Facewon

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.

Facewon

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.

acemuzzy

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

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.

Facewon

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.

Facewon

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."

Facewon

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.

davyK

When you print the doc are the letters not on individual pages?

GooberTheHat

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

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.

Facewon

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.

Facewon

GooberTheHat wrote:

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.

davyK

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.

Facewon

Goober, you are a genius. Added name fields to the heading section and it saves exactly as I want it to as well.

GooberTheHat

Happy days.

Facewon

Lolz, thought I'd try macros on one of my other spreadsheets on my own laptop. Even it chugs along trying to deal.

Facewon

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.

Facewon

Accept my steam fr Goober, ya big Jessie.

GooberTheHat

Ah, will do. My pc is broken still so haven't been on in a while.

Facewon

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.

Facewon

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.

GooberTheHat

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

Howdy, Stranger!

Categories