When learning how to use business intelligence (BI) software, we can find many examples using text or Excel files. Microsoft's Excel and Power BI also have the capability to connect to databases and extract data directly from them for analysis. Microsoft provides sample databases for download, and we can restore these databases to an SQL [...]
In a previous article we demonstrated how Excel responds when we try to load too many rows of data into an Excel worksheet. The problem is that a worksheet has a maximum of 1,048,576 rows and any additional data is truncated. Large datasets must then be loaded into multiple worksheets for analysis which can become [...]
For many of us, Microsoft Excel might be either the only tool, or a favourite tool, that we have available for data analysis. Excel can work with large amounts of data, but there are limitations on how much data we can load into a worksheet. In particular, the maximum number of rows in a single [...]
Microsoft Excel has a powerful and relatively new tool to add to the arsenal of data analysis tools that we are already accustomed to using, this is the Excel Data Model. Working together with Power Query and Power Pivot, the Data Model enables Excel users to work with much larger data sets than we could [...]
Microsoft Excel is a spreadsheet program with an interface formed from a set of cells arranged in rows and columns. The columns are labelled with letters along the top of the work area, and the rows are numbered down the left side of the work area. Every cell has a unique address formed by combining [...]
Excel's What-If Analysis tools allow us to change the values of inputs to a calculation and explore different possible results. In the following article we will consider a single, simple calculation and discuss how each of the three What-If Analysis tools: Goal Seek, Scenario Manager and Data Tables, can be applied. The commands for What-if [...]
Animated GIFs are a great way to demonstrate a series of changes. Here we are going through the process of creating an animage GIF using the GNU Image Manipulation Program (GIMP). We previously discussed how to create Animated GIFs With Adobe Photoshop CS5. If Adobe Photoshop isn't available, it is of course possible to do [...]
The Internet is full of images. These are a great way to capture attention and convey information. Adding motion to images can provide even more information and keep people interested. Video clips are great, but downloads can become tedious with longer videos at higher quality. The animated GIF is a great option if we wish [...]
Excel has 4 functions that allow us to find a value in a column or row, and return a value from a corresponding column or row. The lookup functions can be very useful and are easy to understand when they provide an exact match for a search item. In addition to exact matches, the functions [...]
I was reading about one of the new functions that Microsoft has added to Excel, XLOOKUP(), and I started wondering where the different LOOKUP() functions came from. This little rabbit hole turned out to be quite revealing in terms of the way that Excel looks and works - the search took me to some history [...]
