Author: Jingjing Lin



After you export citations to an excel sheet, you might want to understand the distribution of publications by year over time. How to visually show this to yourself and your potential audience out there?

I am sharing my often-used method on counting the frequency of publication years in your excel form in this article.

So, this is a quick example in the video that I have exported a total 182 citation records from the Scopus database using the keyword of “research literacy”.

You will have a column called year, which lists all the publication years of these research publications.

And let’s say I want to see how to visually show what year publishes more and the trend over years. How to quickly do it in Excel?

I will copy this year column and paste it in another sheet in the same excel. Just to temporarily put it there for processing. And now I will go to Data in the menu, and I will click the icon “remove duplicates”. The purpose is to remove duplicate years from the list and only keep the unique years in the list.

After this action, you can see that out of the list of 182 items, only 23 unique values remained.

Now I will paste this list of unique years next to the full list of 182 items of year values.

The magic comes in this column, where I will use the formula COUNTIF.

COUNTIF(A:A, C2)

In the above formula, A:A means the whole column A. The whole formula basically tells the excel function to search the whole column A in the active sheet for the value that appears in the cell C2. In this example in the video, it is to search in the 182 items of year values for the value of 1996.

Once you complete inputting the formula, click enter. And drag this formula to apply to all cells below it.

Now we can see that all the values in this list have been updated.

It shows the count for each publication year value in the full list.

Now it is time to visualize this list of data. We can just select this data area and go to Insert on the menu and search for recommended chart and the first recommended chart already is suitable for the presentation.

This is my way of displaying the number of publications by year. If you have a better approach, please remember to comment and share.


最后修改: 2022年10月26日 星期三 15:38