2017 was a great year for Excel and Excellers (those of us who love using Excel for work or study purposes and can’t wait to learn more about this wonderful piece of software) – alike.
Excel turned 30 this year and it seems like just the other day, Excel was running on the very first version of Windows and didn’t have great mouse implementation.
Yes, the software we all know and love was born in 1987 and has come a long, long way since then.
This year in Excel related news – 17-year-old John Dumoulin from Northern Virginia, USA won the Excel World Championships and $10, 000 in prize money. Microsoft also announced plans to integrate Artificial Intelligence (AI) into Office 365. Among the updates, an AI tool called Insights for Excel was introduced. This tool specifically evaluates complex patterns and highlights the patterns it detects.
So, in order to celebrate the year that has been, we at ExcelDemy have put together – a list of our Top Ten ExcelDemy Blogs of 2017.
Table of Contents
- 1 1. Charts, Charts, and More Charts
- 2 2. Excel Books
- 3 3. Books on Excel VBA
- 4 4. Free Online Excel Courses with Certificates
- 5 5. The Great Debate – VLOOKUP versus INDEX & MATCH versus DGET
- 6 6. How to Use the Excel Database Functions DGET, DAVERAGE, & DMAX
- 7 7. Matching Data in Excel
- 8 8. Accessing Excel Worksheet Functions in VBA code
- 9 9. More Excel Books
- 10 10. Books on Data Analysis and Business Modelling in Excel
- 11 Bonus Tip
1. Charts, Charts, and More Charts
Charts captivate many Excel users and non-Excel users alike. Charts or graphs are visual representations of data that can be used to and are often used to, enhance reports and presentations. One can easily create a variety of charts in Excel. However, there are a few tips and tricks that you will need to know, in order to make the charts in your workbook really stand out.
Coming in first place in terms of views, was our How to Make Excel Graphs Look Professional & Cool ~ Excel Chart Tips Tutorial.
This tutorial covers various chart related tricks and tips, such as how to remove gridlines and how to use chart templates to ensure consistency.
Plus, as a bonus tip – there is also one crossover PowerPoint trick that shows you how to animate individual chart elements in PowerPoint charts. All in all, once you actively implement the tips and tricks, mentioned in the tutorial – you will create dazzling charts that are guaranteed to impress any viewer.
2. Excel Books
Many students these days, utilize a wide variety of learning resources – from online websites to forums and video tutorials. Nonetheless, books are still a very popular method of learning Excel and other subjects. Fortunately, in the Excel domain, there are many excellent authors with many years of experience willing to share their knowledge.
So, it comes as no surprise that coming in second place was our Best 78 Excel Training Books for Beginners, Data Analysis & Advanced Users Article.
This article includes a recommended learning plan for delving into Excel, as well as a list of Excel tutorial books. The books listed, are divided into categories – so you can quickly identify your specific area of interest.
For example, the article lists the best books for beginners, as well as advanced users of Excel and good books on VBA. There are also books listed in the article that covers Excel Dashboard creation specifically. If you are wondering about good books for learning Excel – then look no further than this article, since it lists many of the good books currently available.
3. Books on Excel VBA
Visual Basic for Applications or VBA is a programming language that comes with Office applications, such as Microsoft Excel, Microsoft Word, Microsoft Access and Microsoft Outlook. One can use VBA to automate repetitive tasks as well as create one’s own custom functions and code. In addition, there is a variety of other non-standard tasks, one can use VBA to accomplish. You can also manipulate other Office programmes from within the Excel environment through using VBA.
Macros that are recorded in Excel – using the macro recorder are written in VBA. You can also write your own VBA code using the Visual Basic Editor (VBE) as mentioned above.
Third place on our list was our Best 6 Excel VBA (Macro) Books for Beginners & Advanced Users Article.
This article lists books on VBA, for absolute newbies to programming and the VBA language, books for advanced VBA development as well as books for modelers.
Once you go through the books in this article, you should find that you will be pretty competent in VBA. VBA is also a good starting point for those wanting to get into programming in general since it is relatively easier to master than more advanced programming languages such as C++, but one still gets familiarity with key programming concepts through learning VBA.
4. Free Online Excel Courses with Certificates
Free online Excel courses, complete with certificates, are great for people who are on a budget but still want to learn Excel in a structured environment and earn recognition for the milestones they achieved.
So, it comes as no surprise that fourth place on our list was our 40+ Free Online Excel Courses with Certificates Article.
There are beginner Excel courses, Excel to MySQL courses as well as refresher type courses (for those of you who are familiar with Excel or a previous version of Excel but would like to update your knowledge), highlighted in this article. Best of all did we mention that all these courses are FREE.
Even if you are currently studying for a paid Excel exam or course at a university – it is worthwhile to sign up for one of these free courses, just to gain extra practice.
5. The Great Debate – VLOOKUP versus INDEX & MATCH versus DGET
Lookup functions are utilized extensively in Excel spreadsheets – since you will often need to lookup a value based on certain criteria or another input value. VLOOKUP or vertical lookup can lookup data in a table or range, based on specifications given by the user.
It is an extremely useful function and every Excel user needs at least a passing familiarity with VLOOKUP. Since you will more than likely need to use it in your own spreadsheets or you will encounter it in the spreadsheets of others. However, there are some limitations associated with VLOOKUP namely that it can only do lookups from left to right and is prone to error in large spreadsheets. Enter the INDEX & MATCH combo which can address some of the issues associated with VLOOKUP, and the database function DGET.
In our VLOOKUP versus INDEX and MATCH versus DGET Tutorial, which comes in fifth place on our list respectively, we ignite the great debate and show you how to use the functions and compare the advantages and disadvantages associated with each of the lookup functions and formulas.
6. How to Use the Excel Database Functions DGET, DAVERAGE, & DMAX
Some Excel users view the Excel database functions with suspicion. However, there is absolutely no need for this suspicion since they are extremely useful lookup functions. They look up data quite effectively and while they do have a SQL-ish feel to them, they are very efficient provided one’s worksheet is set up correctly.
Coming in sixth place was our How to Use Excel Database Functions DGET, DAVERAGE, & DMAX Tutorial.
In this tutorial, we reviewed how to use DGET which is similar as mentioned to other lookup functions, the DAVERAGE Function which returns the average of the data specified by the criteria, and the DMAX Function which returns the maximum of the data, specified by the criteria.
Microsoft Access users will also be familiar with many of these functions, since they are often utilized in Access databases, and thus being able to also use them in Excel is beneficial.
7. Matching Data in Excel
One of the challenges, Excel analysts are often faced with – is receiving columns or data and being asked to find the matching data and thus the differences will be highlighted. The seventh blog on our list confirmed that this is indeed, a challenge that often needs to be resolved.
In our How to Compare Two Columns in Excel to Find Differences Tutorial, we review different ways of finding duplicate data using different functions, in conjunction with conditional formatting.
Thus, you can visually identify the duplicate data and consequently, the differences are emphasized. We look at matching data or duplicate data on the same sheet as well as matching data on different sheets, in the same workbook in this tutorial. You will also learn more advanced conditional formatting techniques.
8. Accessing Excel Worksheet Functions in VBA code
Excel allows one to create one’s own custom or user-defined functions in VBA. These functions allow you to handle custom calculations and requirements efficiently. However, you can also access the built-in Excel worksheet functions (if there is not already a VBA equivalent) in your VBA code. In this way, you do not have to reinvent the wheel, if the functionality you want to accomplish already exists in Excel. In addition, the chances of errors decrease significantly when you access a built-in Excel function in your VBA code.
Our eighth most viewed blog was accordingly – our Using Excel Worksheet Functions INDEX & MATCH in VBA Code! Tutorial.
In this tutorial, we show you how to use the super combo lookup functions – INDEX & MATCH, in VBA code, to create a VBA solution to the standard lookup dilemma.
9. More Excel Books
ExcelDemy viewers certainly love Excel books and that is only a good thing since we are more than willing and happy to list great Excel books, in our articles.
Coming in ninth place on our list was our Best 12 MS Excel Books for Beginners & Intermediate Users Article. In this article, we review the twelve books that in our opinion, will quickly take you from Excel zero to Excel hero. This article is especially useful for beginner and intermediate users of Excel who want to take their Excel skills to the next level.
10. Books on Data Analysis and Business Modelling in Excel
Data analysis refers to the use of tools and techniques to collate, organize, study and draw meaningful conclusions from a specific set of data. Data analysis allows you to gain insights into your data on a more meaningful level.
Excel is frequently utilized in the world of Data Analysis and Business Modelling and it comes as no surprise that this article featured on our top ten list.
In our Best 40 Excel Books for Data Analysis & Business Modeling Article, we review great books on financial and scientific data analysis, SQL integration, and visualization.
Today’s world of data analysis sometimes involves millions of rows of data. Consequently, there is a need for data scientists to expand their knowledge of data analysis techniques and modeling. These days, knowledge of multiple techniques is no longer a rarity, it is a necessity for every serious data scientist.
These books by top-rated authors and minds, in the fields of data analysis and business modeling, cover most of the aspects and techniques that you will require. A reference list that every serious data analyst needs access to, in our humble opinion.
And there you have it – that was the complete year of 2017 summarized in the top viewed ExcelDemy blogs.
Please feel free to tell us what your favorite ExcelDemy blogs of 2017 are. If you need some reminders of those tutorials and articles do the following:
1) In order to search through ONLY Exceldemy articles and blogs using Google, go to Google Search as shown below.
2) Enter the site: term as well as our website and the topic – for example if you want to search through only ExcelDemy, for chart related tutorials and articles, enter the following search term:
3) Once you click on Google Search, only results from ExcelDemy that are chart related will be returned, as shown below.
4) You can use this method to search through only ExcelDemy for any other Excel related topic such as Pivot Tables, at any time.
5) You can then further filter your results by time, by clicking on Tools>Any time> and selecting Past year, in order to see the results filtered by past year as shown below.
6) And there you have it the results are filtered by past year.
Happy New Year and Happy Excelling.