It is an interesting idea, but not an easy one to pull off. If I’m not mistaken, you have verifiable observations of 556 species. Imagine you make a spreadsheet, with all 556 species along the top, and all 556 species at the left column, and in each cell record whether the species at the top eats the species at the left (cannibalism occurs, so those cells would need to be included). This yields 308,580 cells in which you would want to try to fill a yes or no answer. That is a lot of relationships to investigate, and in most cases you would find no data. Even where you did, you would most often find a single positive instance (e.g., a report that someone found an Eastern Gray Squirrel gnawing the bone of a Mute Swan) rather than any statement that X is an important part of the diet of Y. Doing this well for even 20 species would be a major task. Doing it well for 556 would require years of research and writing just to define the terms and set limits. How often does X need to feed on Y to be included, and what standard of evidence is required? Does it need to happen in native habitat? How best to present the findings in a clear and appealing way would, I guess, be a bridge to cross if it is ever arrived at.
That is a good point. I would probably exclude gnawing on bones for calcium, and only examples of what a species would intentionally be seeking out to eat.
you should be able to represent a food web as a directed graph or some other network graph. i doubt Excel has any standard chart type that can visualize this kind of data other than in maybe an organizational chart, but you search for add-ins maybe that might handle such a visualization. outside of Excel, i’m sure there are many options in R, Python, Javascript, etc., to visualize graphs.
here are a few random visualizations in Observable that might give you some ideas for visualizing networks, as well as how you need to structure your data:
(Observable is more or less Javascript, but the visualizations above use the D3 chart module, which should also be ported to R and Python. there are other chart modules which should also handle these kinds of visualizations, too.)
You might want to check out Gephi. It is pretty complex but can produce link charts from imported spreadsheets or databases. Best of all it is free and doesn’t require any coding.
I suspect that even data on who intentionally seeks out whom for that many species would require several lifetimes worth of work to amass, and even then much guessing would be required. And don’t forget that food webs shift over time, based on what other foods are available, population sizes, even weather.
Pringle, R. M., & Hutchinson, M. C. (2020). Resolving food-web structure. Annual Review of Ecology, Evolution, and Systematics, 51, 55-80.
Food webs are a major focus and organizing theme of ecology, but the data used to assemble them are deficient. Early debates over food-web data focused on taxonomic resolution and completeness, lack of which had produced spurious inferences. Recent data are widely believed to be much better and are used extensively in theoretical and meta-analytic research on network ecology. Confidence in these data rests on the assumptions (a) that empiricists correctly identified consumers and their foods and (b) that sampling methods were adequate to detect a near-comprehensive fraction of the trophic interactions between species. Abundant evidence indicates that these assumptions are often invalid, suggesting that most topological food-web data may remain unreliable for inferences about network structure and underlying ecological and evolutionary processes. Morphologically cryptic species are ubiquitous across taxa and regions, and many trophic interactions routinely evade detection by conventional methods. Molecular methods have diagnosed the severity of these problems and are a necessary part of the cure.