Calculating the median in Excel PivotTables
Go to Master Data Analysis Yes, you read the title of this post correctly, you can calculate the median and lots of other functions in Excel PivotTables besides the regular options. If you’re a regular user of Excel PivotTables you might know you can change the summary function: Just right-click inside of the PivotTable → […]
Import excel files with a dynamic starting row
Go to Master Data Analysis This is the 4th post of a series that covers everything about importing all files in a folder into Excel using a tool called Power Query. Click here to see the series. The previous scenario covered how to import all Excel files in a folder getting the data below the 9th row. […]
Import specific cells from Excel files
Go to Master Data Analysis This is the 5th post of a series that covers everything about importing all files in a folder into Excel using a tool called Power Query. Click here to see the series. In this post, I’ll show you how to import the contents from specific cells of all files in a folder. […]
Import and clean each file from a folder
Go to Master Data Analysis This is the third post of a series that covers everything about importing all files in a folder into Excel. Click here to see the series. The previous scenario covered how to import all Excel files in a folder, however, for that example we assumed that all files had the right format […]
Import all sheets from all files in a folder into Excel
Go to Master Data Analysis This is the second post of a series that covers everything about importing all files in a folder. Click here to see the series of post. In this scenario I’ll show you to import all sheets from all excel files in a folder. If you want to follow along, download […]
Import all CSV files in a folder into Excel
Go to Master Data Analysis Let’s say you have 50 CSV files in a folder and you need to import them into a single worksheet in Excel. How do you do that? Well, it turns out this is the simplest scenario of importing all files into Excel. The reason for this is that CSV files […]
Ultimate guide to import all files from a folder into Excel
Go to Master Data Analysis I know what you’re thinking: “Another blog post about importing all excel files in a folder”, but this couldn’t be farther from the truth. As the title says, this is the ULTIMATE guide to import all files from a folder and I’ll cover from very simple scenarios to dealing with […]
Analyzing 50 million records in Excel
Go to Master Data Analysis A common myth I hear very frequently is that you can’t work with more than 1 million records in Excel. Actually, the right myth should be that you can’t use more than 1,048,576 rows, since this is the number of rows on each sheet; but even this one is false. […]
PivotTable Week – Learn all about PivotTables
Go to Master Data Analysis With PivotTables you can process thousands (or millions) of rows extremely fast and show the results in dynamic charts called PivotCharts. During the week (June 13 – 19, 2016) we shared several posts that ranged from very basic knowledge (how to create PivotTables) up to more advanced topics (VBA/Power Pivot). […]
8 tips to master data validation in Excel
Go to Master Data Analysis One of the great features that MS Excel has is the capability to restrict (validate) the input into certain cells. The name of this feature in MS Excel is Data Validation. A few examples of this is to restrict the values of cells to be text of a certain length numbers […]