Home Data Analysis

Data Analysis

Excel Data Tools, Merge and Split Tools, Data Analysis, Advanced Data Tools

tabular format - 2

Convert Tabular Format into Excel Data Table

The Excel Table is relatively seldom used and underrated feature in Excel. But in today's article we'll show you the advantages of its use. It makes it easy for a user to manage data if it is in a tabular format. Just by converting a data range into a table its usage is even easier. After this little intro, let's see the step by step tutorial!

Dynamic Scroll Bar in Excel

The dynamic scroll bar belongs to Form Controls. With its use we open up many great opportunities in Excel. Its biggest advantage appears when we work with a large data table. With its help we will be able to manage any size lists in a small space. Sounds good, isn't it? Do you still remember the rule of the one-page dashboard? Very important to work in a space-saving mode. With this method we gain free spaces which are very valuable. The advantage of this that we'll have more opportunities to use great charts! In today’s step-by-step tutorial, we’ll show you how to manage a large dataset easily.

Consolidate Multiple Worksheets

In this tutorial we will show you how to consolidate multiple worksheets into a Pivot table using Excel. If the data is arranged properly, then you can do that. Most of the time when you create a Pivot table in Excel 2013 or Excel 2016, you’ll use a data list, or an Excel table. There might be some different worksheets (or workbooks) that you have in your collection with data arranged differently, but you’ll still want to create a pivot table. If the data is arranged properly, then you can do that. In this tutorial, we will use the consolidating sample file. You can download from here.

Drop-down list in Excel

The Excel drop-down list is a great tool that belongs to the data validation group. Can be used almost all of the making of special dashboards or templates. Its main function is the limitation of data input, in a given cell we can only chose the elements of one fixed list. The drop-down list can be imagined like a little menu from where we can only chose the value that was previously specified. Just think about all the mistakes and errors we can avoid with the help of this method! There is no mistakenly entered name anymore, the specified list will not allow incorrect data entry.

Clean Raw Data in Excel

How to clean raw data? – eternal problem and not only in Excel! Clean data is a key question either we create a simple analysis, or a professional excel dashboard. In today’s article we will go through the most important technics. Besides the well-known methods we will introduce special VBA codes that will make the process quicker and more effective. There are many data cleansing technics, although we often forget about this obvious method.

Frequency Distribution Table

Frequency Distribution Table - Create excel frequency distribution table in seconds with help of our free DataXL automation add-in! It is not a new thing that we want to give you better and smarter tools day by day. This is why we have created the website. The free Data Explorer add-in has again expanded with a great function! How can we make statistical analyzation even simpler? Read on, we are offering a very interesting Excel tutorial now! What is the Excel frequency distribution table? And how does it work?

Excel Formula Audit Tool

Our Excel Formula Audit tool is a part of free DataXL automation add-in. The Excel default formula audit tool caters only to the basic functions. If we work on large tables and many formulas then we have to find a much more effective solution. This is why we created the Excel formula audit add-in. We endeavored to keep the user experience in mind. The goal of the Excel productivity tool is to portray in visual format the connection between data. Please read through today’s lesson because you will become richer with useful information!

Combine Multiple Workbooks

How to combine multiple workbooks into one worksheet? Our Excel data processing tools series continues! A short time ago we got acquainted with the Excel worksheet splitter that gave us a big help cutting up and grouping data tables. It is possible that we don’t have to cup up the data table but we need to do just the opposite. As a business analyst and advanced Excel user I often have the task of merging more (usually a 100+ piece) Excel worksheets or workbooks into one single table, possibly in a very short time. One of the favorite habits of managers is to try to over-persuade their employees to better performance by impossible deadlines, but that’s how the cookie crumbles.

Split Excel Worksheet

How to split Excel worksheet to separate workbooks or tabs? Our Excel data processing series continues with the introduction of a new Excel tool. We strongly believe that today’s presentation will bring data processing professionals again one step closer to success. The cleansing, merging and grouping of data frequently causes difficult moments but there is always effective solution! We don’t want to get cut up in clichés but time is scares and the workload is large! Keeping this in mind let’s start today’s lesson and by the end of the day it will be child’s play for you to handle large amount of data.

String Manipulation Functions

The string manipulation functions is our first article of the Excel productivity tools subject. In this series we will show you the most effective data cleansing techniques. Before we would start this magnificent subject, allow us a little detour. We would like to summon an old memory from those times when I didn’t even know what Excel was. After college studies I got employed at a small firm. As a beginner they didn’t dish out the premium tasks for me, but I got the seemingly very boring data cleansing task. This wouldn’t have been a problem in its own, the trouble was that for this kind of analysis there was only a very short period of time. And I have never heard of the Excel string manipulation function, so the solution wasn’t simple; these tasks consumed many night shifts.

Excel Sorting Data Tips

Excel Sorting Data Tips - With the help of Excel Sorting Data Tips we looking for solutions for very interesting problems today. How to sort dynamically a list containing two columns ('Product' and 'Sales' columns) at will only using excel functions? On the second worksheet we will show you a solution based on the on the fly sorting technique. It is well capable of give us help in creating an excel dashboard or other visualization tasks especially when available space is scarce.
excel job interview 1

Excel Job Interview Tips

The following excel job interview tips we have put together particularly for those who seek jobs. In the past 15 years we have written a few of them ourselves, there were more simple ones and complex ones also, we would like to share with you in this article the experiences about these interviews.