Attribute Analysis Spreadsheet

I’m a self-confessed spreadsheet geek and Football Manager addict, so when I stumbled on an opportunity to combine them both, of course I couldn’t resist!

What started as a simple spreadsheet for my own use in July 2019, it quickly became a project I have continuously worked on and developed for the last 8 months, turning into what I can now proudly release for public download.

Before I introduce you to the features of v2, I would like to say that the formula’s used in the spreadsheet and the results it gives are based on I have found works during my own saves. I can’t guarantee that they’ll work for every team so please don’t always take what it says as golden as I cannot promise the results are perfect.


How does this differ to v1.5?

  • New Club DNA Analysis feature
  • New Player Analysis sheet
  • New Squad Notes sheet
  • Top 3 Overview is now Top 5
  • Behind the scenes formula update to lower file size and increase speed
  • New colour scheme and layout

Feature Guide

After setting up the spreadsheet by following the installation guide in the zip file, you’ll be given several pages of data.

POSITIONS (GK, DEF, DM, MID, AM, ST)
Each positional area has its own sheet, which is broken down further into the relevant roles.

Every squad player is given a rating based on how suitable they are for that role.

If the player is a natural in the position the role matches – e.g. ST as a Target Man, D (R) as a Wing-Back – they will receive a bonus rating


OVERALL
Another way you can view role suitability is on the ‘Overall’ sheet. This shows every player and every role on one sheet.

→ The yellow cell can be edited to any percentage based on your required levels and, in turn, any matching ratings will be highlighted in light purple.

As an example, I find 70% is a good level for high-quality teams playing in Europe

→ The row to the right of the yellow cell shows how many of your squad have above your chosen percentage so you can see where you have the best depth for that role.

→ The column directly below the yellow cell shows how versatile a particular player is and how many roles, based on your percentage, they could comfortably play or even be retrained in if young enough.


Top 5
An ‘at a glance’ view of the top 5 players for each role, separated into sections based on the positional areas.


NEW – DNA Analysis
Club DNA is an often overlooked part of FM which I touched upon on in the first part of my DNAjax blog. In short, DNA is all about building a squad that share the same qualities. There are plenty of blogs around that go into a lot of detail so I’d recommend a quick Google search if you are interested in learning more.

This sheet will populate after you’ve imported your data into the ‘DNA Base’ sheet. The 5 core attributes (Bra, Cmp, Det, Tea, Wor) are my own personal choices so you may want to switch them out for 5 that suit your style of play better. Changing the attribute names on the ‘DNA Base’ sheet will update them on the ‘DNA Analysis’ sheet too.

Along with the 5 attributes, the DNA rating is also made up of the following:

Country Where Club is Based: Players who play in the country of their Nationality receive a 5pt bonus on their DNA score.

Core Squad Personalities: Up to 5 of the strongest personalities you’d like in your squad – a 20pt bonus goes to those who possess one of them.

Secondary Squad Personalities: Not every player will be perfect so choose up to another 3 personalities you’d find acceptable – a 15pt bonus will be applied.

Personalities to Develop (u23): Young players still have room to develop their personality with mentoring or experience. There will be a 10pt bonus given to anyone under 23 years old who has one of the personalities you enter here.

IMPORTANT NOTE: I have left in the examples used in my DNAjax save so you can see how the data is filled in – you just need to change your country to the three-letter abbreviation used in FM and the personality types to suit your style of play.

Once all the above data has been filled in, the spreadsheet works its magic formula and gives a suggested squad for the player to be part of and even if you’d be better off without them.

IMPORTANT NOTE: The formula takes into account the suitability percentage you enter on the ‘Overall’ sheet so don’t forget to fill it in or the calculations won’t work!

→ Players between the ages of 17 and 21 can be recommended for a loan spell away from the club in order to develop.

→ Players between the ages of 19 and 21 can be put forward for exposure to your First Team if they show high potential to be a star. I’d suggest training them with the senior squad and using them as a substitute or in cup games.


NEW – Player Analysis
Type the name, or use the drop-down menu, for one of your squad into the top box and it will pull the most important data from the other sheets into one place.

→ Club DNA % is calculated from the information inputted into the ‘DNA Analysis’ sheet. Edit the Target DNA box on the right to the lowest percentage you’d consider as an acceptable match for your DNA vision.

→ Use the Formation Suitability menu to type in, or select using the drop-down menu, the formation and roles being used in your tactic and it will work out whether they are a suitable fit (based on their top 3 roles) for your team.

IMPORTANT NOTE: When inputting your formation, please make sure you state both the role and mentality using the format in the example or the sheet will return an error.


NEW – Squad Notes
This was a last-minute addition to the spreadsheet after it was something I found came in useful whilst setting up my DNAjax save.

There isn’t anything automated about it but it enables you to have a sort of notepad for when you’re moving players around squads, setting up individual role training, or simply just wanting to keep track of who you’re offering out for transfers and loans.


Purchase

The download you receive contains the following in a zip file:

  • Analysis Spreadsheet
  • Installation Guide

    Attribute Analysis
  • Custom Squad View
  • Custom Player Search View
  • Custom Shortlist View

    Squad DNA Analysis
  • Custom Squad View
  • Custom Player Search View
  • Custom Shortlist View

IMPORTANT NOTE: If you are using different core attributes for your Club DNA than what I have, don’t forget to change them on the views before exporting to the spreadsheet.


Because this version of the spreadsheet has taken so much work, I have made the decision to offer it as a download for a minimum £1 donation

Purchase

If you’d prefer not to donate, then is a free version of the spreadsheet available which doesn’t include the Club DNA feature

v2.1 (no DNA)
Purchase

14 Replies to “Attribute Analysis Spreadsheet”

  1. […] The same cannot be said for the Female Football Manager (Twitter), who — in addition to running a killer blog, which details her engaging CSKA-Sofia and DNAjax saves, and (more recently) some tactical analysis — created a deliciously-detailed Attribute Analysis Spreadsheet, […]

  2. What an amazing piece of work!

    I recently got in to FM20 and just downloaded the attribute analysis sheet and was fiddling around in it, and some questions arose. For instance, if I look at the rating for a Ball Playing Defender (Defend), the attribute Composure is not included in the calculation, even though it is a “vital” attribute according to the in game role highlight, is this an oversight or intended? Additionally, what specifically determines the number that you normalise with (560 for the Ball Playing Defender (Defend) role)? I can understand that it depends on the number of attributes that are important for a given role, and the significance of each of those attributes, but there is something else that I can not really see.

    Cheers,
    Nicolai

    1. The calculations behind the formula is not something I’ll be sharing as it’s something I’ve devised myself. Can I also ask where you have seen the calculations as this is something that’s supposed to be hidden so I’d like to rectify this

  3. Hi! Great work!! I’ve just purchased the spreadsheet but i’ve few questions. 1) I’m managing Inter Milan, but which are the best 8 player personality to have a balanced team? Because setting up with the default personality you set up the spreadsheet tells me that i have to sell 22 players out of 26, and 2 transfers to team b.
    2) How is it possible that for the complete wingback position the best 5 players are a striker, an inside forward, a left defender and 2 central midfielders? very strange
    thank you in advance!!

    1. 1) I can’t really give advice on the best personalities as that’s something that needs to suit your own squad and your own style of play, it will be different for each person. I can only suggest making use of the squad comparison screens in game and seeing where yours is strongest compared to others.

      2) the rankings are based on player attributes and are only a guide. if no players are ranking high that are naturals in that position, you’ll probably find they have weak attributes and you’d be better off not using a CWB role in your tactics.

  4. I just bought and really like the spreadsheet analysis. However, i consider a lot more personalities as viable, and sometimes a few more attributes as basic need for my DNA and its a pita to work it out with several spreadsheets (but ofc still easier than doing it the old way 😉 ). Would there be a way to expand the DNA attributes and/or personalities list or would it prevent your calculations from working. (I’d gladly pay again for the custom setup).

    In addition: a function that sorts the lines by columns value (i.e. -> click BPD suitability % column to have the entries sorted ascending/descending would improve the usability by a great margin.

    anyways… great work
    best regards

    1. You can already edit the sheet to any personalities and attributes you want, it’s all in the blog posf

      1. thanks for your reply.
        aye. sorry about not being clear enough. english is not my native language.
        its not about the “what”, its about “the amount” of personalities and attributes i am allowed to enter for my DNA. i would like to enter maybe 7 DNA attributes and 10 personalities i consider. (as an example, i did not count. but i think its about 15-18 personalities that are decent for a mid tier team with limited transfer budget). maybe if the sheet would consider “fairly professional” as decent (with a slight point reduction) when i enter “professional”, and “model professional”, once i enter “model citizen”.. that would help to get a more precise overview without several runs.
        best regards

Leave a Reply