Stay up-to-date with the latest features of Excel!
Excel is one of the most widely used software programmes in the workplace. It is therefore not entirely unexpected that it has millions of users worldwide. Over the years, there have been many updates to Excel. Would you like to know which features Excel has recently released (in 2019 and 2020)? Then read this blog and find out if there are any interesting possibilities for you!
1. Basic functions
Some of the basic functions introduced in the last two years include:
- SAMENV. or This function allows you to merge text from different cells. This function is very similar to the function TEXT.COMBINE but the difference is that SAMENV. supports range references as well as cell references. This is a lot faster!
- COMBINING. This function is very similar to the join command in SQL. It allows you to combine texts from multiple ranges. You can separate each item by using separators. Separators are one or more characters enclosed in inverted commas or a reference to a valid text string.
- IF.CONDITIONS. With this statement, Excel is able to indicate the largest or smallest value in a range that satisfies one or more criteria.
2. Pivot table functions
In addition to a number of basic functions, a number of useful pivot table options have been added in recent years:
- For example, it is possible to automatically adapt names in the worksheet;
- The pivot table also indicates automatic detection of relationships;
- Moreover, you can adjust the standard layout to your personal preference;
- Also, there are now buttons that allow you to zoom in on your pivot charts;
- And finally, you can automatically adjust names in your worksheet, for example.
3. Excel charts
Did you know that Excel has also added two new types of charts in the last two years?
- Funnel diagram. With a funnel diagram, you can make a graph that indicates different values over different periods or phases. For example, think of a funnel diagram that shows the different phases towards sales on your website. At the top could be the number of consumers visiting your webpage, followed by the number of consumers clicking on a product, followed by the number of consumers putting products in his or her shopping basket and finally the number of consumers actually buying products in your web shop.
- Map graph. With this chart, you are able to compare values and display categories in different geographical regions. This function is quite specific and therefore requires your data to contain geographical regions.
Interactive webinar and Excel training
On 6 April between 9:30 - 12:30 Learnit organises an interactive webinar Excel. During this webinar you will work practically with an example of a sales file under the guidance of Learnit expert Roland. He will share his valuable knowledge and practical tips with you, enabling you to extract important insights from your data. Click click click here for more information about this webinar. Did you know that Learnit also offers nine different Excel training courses? From the training Excel Basic to Excel Expert and from Excel Pivot tables, formulas and functions to Excel PowerPivot, Learnit has it all! For a complete overview of our Excel training courses, click here for more information.
The differences between SQL and Excel
Many people use Excel for (relatively simple) data analyses. When you want to perform more complicated analyses in Excel, you run into a number of problems. You can easily make a mistake which is difficult to trace and Excel is very slow when you are working with large data sets. Moreover, it is also difficult to reproduce a previously used analysis, because it takes some effort to trace which steps were taken earlier. You can easily avoid these problems by making smart use of the SQL programming language. Are you thinking of switching from Excel to SQL? Then it is important to take into account the following differences and properties!
- SQL is a programming language, Excel is a program. Before you switch from Excel to SQL, it is useful to realise that SQL is a programming language and that its use is therefore different from Excel. When you want to use Excel, you click on the green icon on your computer. In SQL, however, this works somewhat differently. When you want to use SQL, you must first choose a database program, which can then communicate with SQL. Examples are MySQL or Oracle. Do you want to learn how to apply SQL in such databases? Then take a look at our training MySQL basics or Oracle Database: SQL Fundamentals Part I.
- Data is stored in a database versus the data is stored on your computer. Another important difference between SQL and Excel is where you store your data. If you want to use data in Excel, it is important that your data (file) is stored on your own computer. In other words, you have the data you want to use at hand. This is slightly more complicated in the case of SQL. In SQL, you write and send so-called 'queries' to a database. This database then provides you with the information you ask for in your queries. Next, the data is stored in a database and organised in tables. A major advantage of this is that it is easy to find out which data you are requesting.
- What is SQL best used for versus what is Excel best used for? SQL is best used when dealing with large data sets. Moreover, you can easily use SQL in different databases. Perhaps the most important advantage of SQL is that you can easily find errors and correct them where necessary. This makes your data analyses much more reliable. Excel has its own strengths. For example, it is possible to enter data manually, which makes the programme reasonably flexible. In addition, the programme is able to perform spell-checks, for example, which enables you to detect errors in names or columns easily. The most important advantage of working with Excel compared to SQL is the visualisation possibilities of the programme. Tables, graphs, pie charts, it is all possible in Excel.
SQL training courses
Did you know that Learnit offers various training courses in SQL? During our training SQL Basics you learn how to easily retrieve relevant data from your database. In the training SQL Advanced not only the basic concepts within SQL are dealt with, but we also play with deeper SQL commands, in the form of T(ransact)-SQL. In addition, we also offer a training SQL Report Builder training. SQL Report Builder offers various visualisation possibilities, allowing you to create a professional report from your data. Ideal, for example, if you want to make a management report! Click on the orange buttons below for more information about one of the SQL trainings mentioned or click here for the complete overview of SQL training courses from Learnit.