To think I can do this in Excel?

[Notworryingdarling]

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!

Could you colour code the cells to differentiate between the different criteria? If you click on conditional formatting in Excel, that should give you options to choose from to colour code different cells.

What message do you want your clients to receive? Answering this question will help you decide how to present it.

Can you use a table in word? Or do you need to be able to filter it?

Not entirely sure what you're looking for.

You could use conditional formatting - create a rule for "Format only cells that contain..." and then the drop downs "Cell value", "Equal to" and then enter X in the final box.

Click the 'Format' button underneath and set the Fill colour and the Font colour to the same. Then for all the Xs you'll get a coloured box with no visible X (although it is still there). Could go column by column and have different colours for each?

How is your side criteria listed? Could you change the order to make it more representative of your message, or so the point you want to make stands out?

Have you searched a pivot table? You could then put it into graphs

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!

Could you colour the cells directly (e.g. highlight the cells in different colours) using conditional formatting?

If you don't need to filter it or play with the table then just put it in a table in word. You're only using excel for the square boxes otherwise

Good luck! (I hate Excel and have only just started feeling a bit more confident with it 😂😂)

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.

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!

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

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

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?

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.

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!

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 😊

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

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

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.

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

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

If you can use Power BI then try looking at a Chord diagram, which might do what you want:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/wa104380761?tab=overview

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”?