Active discussions

Please or to access all these features

Talk
AIBU?

Share your dilemmas and get honest opinions from other Mumsnetters.

Original poster

To think I can do this in Excel?

35 replies

Notworryingdarling · 15/01/2024 13:27

Looking for some help kind Mumsnetters! Not sure if I am missing an obvious way to do this. I have a table in Excel, it's not showing data in a way that needs to be analysed just representing something. So it's a list of criteria down the side, with a list of elements across the top and Xs in certain cells to show which criteria appear in which elements.

I want a more visually appealing version of this to share with stakeholders. Anyone got any ideas on quick simple ways to do this, either in Excel itself or using another (preferably free!) tool? I have visions of being able to upload it somewhere to get a nice visual graphic version but can't find anything that does that, maybe it's a pipe dream and I need to spend time manually making it look good but I thought I would throw the question out there! Any help gratefully received!

OP posts:
New posts on this thread. Refresh page
Original poster
Notworryingdarling · 16/01/2024 08:22

Thanks @FrangipaniBlue I wouldn't usually try PowerPoint and I know someone else suggested it as well so I will give it a go today!

Once again thanks so much to everyone who's replied, I think I've been overthinking a bit as in a pretty new job and a bit of imposter syndrome and this seemed like it should be simple but got frozen with it so you've all helped massively 😊

OP posts:
FrangipaniBlue · 15/01/2024 21:31

I just inserted a table into PowerPoint same way as you would in a word document. Then when you highlight the table you can select any of the colour presets from the format tab along the top.

For the ticks just go to insert symbol, again on the bar across the top and it brings up all different ones, you can use a tick or a cross or anything.

I made the font format of the ones for "new syllabus" to bold and for old syllabus made the font colour 50% transparent.

Original poster
Notworryingdarling · 15/01/2024 15:38

You guys are amazing, thank you so much for all the brilliant suggestions! Yes @FrangipaniBlue that would do the trick, thank you! Would you mind giving me some pointers about how you got to that please?

OP posts:
PrincessNannie · 15/01/2024 14:43

First highly recommend saving a back up copy before you try any of this so you can get back to the original.

Simplest way is to apply a filter to the top row.
filter each column with non blanks
color each cell that has an x in it.

then if you want centre the x and make it bold.
Unfilter the column and move onto the next column Filter and repeat
you could use alternate colour for each column.

you could also use find and replace so find any cell with an X and replace with a blank and cell colour.

you can then copy to any other programs - word power point etc but use Paste Special and paste as picture.

Good luck.

HeidiIeigh · 15/01/2024 14:33

How basic are you talking? This is just conditional formatting to pop the "x". But to be fair @FrangipaniBlue example is great.

To think I can do this in Excel?
FrangipaniBlue · 15/01/2024 14:03

Like this?

You really don't need power bi or any of the software/apps people are suggesting.

You could literally use "fill" to edit colours to make it even more "jazzy"

Literally did the attached while making a cuppa........

To think I can do this in Excel?
Original poster
Notworryingdarling · 15/01/2024 14:00

Ha ha yes in a way it is making it look pretty for no reason to do with the data as such, more so it can be used in a presentation/ information leaflet and look nice 😀

OP posts:
MereDintofPandiculation · 15/01/2024 13:58

Notworryingdarling · 15/01/2024 13:36

Thanks for the replies. I'm struggling a bit to think of a way to do it in conditional formatting. Colouring the Xs might work thank you.

Its not this , but say its a list if children at the side and a list of classes they are in at the top, with Xs marking which child is in each class. It just looks like a really basic black and white list and I'd love it to look more like a graphic representation if that makes any sense!

So it's basically a look up table, but you want it to look pretty for no reason which has any connection of making your message any easier to understand?

How have you ordered the rows and columns? It will look better if you shuffle the order so that, taking the analogy further, and assuming it's children and the subjects they're taking, so that the children taking very similar combinations of subjects are together, and the subjects which tend to be taken together are together.

As well as looking tidier, it'll show which groups of subjects tend to be taken together, which might be interesting.

But it will make it less easy to see which subjects Amelia is taking, or who is doing French.

I'd also, instead of using X's,simply colour in the square.

Original poster
Notworryingdarling · 15/01/2024 13:58

Thank you I will look at all of these! It's not a huge list and it's not actually children in classes, the better example kis probably showing how units in one exam spec are moved across to units in a new spec.

OP posts:
Talipesmum · 15/01/2024 13:55

OP on the most straightforward level, if you want your table just to look a bit prettier and not totally basic black and white, take a look at table styles and formatting - it will make it look a bit slicker.

https://www.ablebits.com/office-addins-blog/excel-tables-styles/

If it’s lots of children and classes, is it a huge list? Does it need summarising? Is it “these children are in red class, these are in green class and these are in purple class”? Or is it “these secondary school children are taking these varied mixes of history, geography, art, DT etc”?

How to change Excel table styles and remove table formatting

The tutorial explains how you can quickly apply or change table styles in Excel, and remove table formatting keeping all useful features of an Excel table.

https://www.ablebits.com/office-addins-blog/excel-tables-styles/

Original poster
Notworryingdarling · 15/01/2024 13:54

Ooh thank you, will have a look at Canva and Menti! @JohnLapsleyParlabane could you expand on how to do a hierarchy chart please?

OP posts:
Moodycoo · 15/01/2024 13:53

Notworryingdarling · 15/01/2024 13:42

Yes I am just using Excel for boxes really , just feel like if I put it into Word will look the same. I will play around with using conditional formatting to colour the cells. I appreciate all the responses, I might be hoping for something that doesn't exist I would just love to be able to very easily transfer it to look like a pretty graphic picture of what I'm conveying!

Press F11 and it'll give you various types of graphs/charts - would that help?

Original poster
Notworryingdarling · 15/01/2024 13:52

Sorry for not tagging people in but I am reading and appreciating every suggestion! A slightly better example of what it's like is criteria within an exam specification at the side by unit, with the top showing elements of a new spec and Xs show which parts of the old spec appear in the new.

OP posts:
Daftasabroom · 15/01/2024 13:52

JohnLapsleyParlabane · 15/01/2024 13:43

If you replace the Xs with 1s and the blanks with zeros you could make it count totals and extract graphs

I was going to post similar, a hierarchy chart could work well.

girlwhowearsglasses · 15/01/2024 13:47

You can export the data to Canva and put it into all sorts of fancy pie charts and graphs.

Get a free canva account - there are lots of tutorials on exporting excel data to Canva

Ineedanewsofa · 15/01/2024 13:46

Have a look at Menitmeter, it is great for making data look visually appealing. It’s free for a certain amount of uses (maybe 3?) and they will spam you regularly to sign up to the paid version but if you can ignore that it might do the job. Good luck 😊

Original poster
Notworryingdarling · 15/01/2024 13:45

Thanks for the luck! Yes maybe Power BI Iis what I really need, I was a very beginner user in a previous job but aren't aware of having access in my current role. I have been pretty good with Excel to analyse etc in the past, but quite rusty and I think as this is just very basic it's escaping me if there are solutions just to make it look jazzy! I think I was hoping there'd be a jazzy AI type tool that took a file and made it look great!

OP posts:
Ponderingwindow · 15/01/2024 13:44

using your example data description

start with a bar graph showing the number of children in each class

The actual names of the children are not relevant. If you have no information about them, then you are likely looking at the mix of overlap. That is where you need to study the data a bit and see what kind of patterns actually exist in order to decide how best to display them to your audience.

MILTOBE · 15/01/2024 13:44

Do you want to show, for instance, the number of boys and girls in each class, or anything like that, or just how many are taking each subject? Could you post a screenshot with fictionalised names?

JohnLapsleyParlabane · 15/01/2024 13:43

If you replace the Xs with 1s and the blanks with zeros you could make it count totals and extract graphs

FrangipaniBlue · 15/01/2024 13:42

I would create a table in PowerPoint, use some fancy colours/shading and the insert symbol to put ticks or crosses etc

Original poster
Notworryingdarling · 15/01/2024 13:42

Yes I am just using Excel for boxes really , just feel like if I put it into Word will look the same. I will play around with using conditional formatting to colour the cells. I appreciate all the responses, I might be hoping for something that doesn't exist I would just love to be able to very easily transfer it to look like a pretty graphic picture of what I'm conveying!

OP posts:
JustAMinutePleass · 15/01/2024 13:41

You can do this in Excel using conditional formatting. You can also use PowerBi if you want it to be dynamic. Or, if you’re good with macros, try PowerPoint linked to either BI or Excel as a backend.