Well after getting the data and creating a report and then a chart, let’s get the report onto the web.
Yes, you can sort by colour in Excel! This feature makes it easy for you to colour code cells and then place them together at the top of your data set.
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
The Excel team has a great site that encourages people to post new ideas for Excel. It also encourages people to vote for the new suggested features.
I have just posted an idea for a UNIQUE function that extracts unique entries from a list.
Currently you have to manually maintain a separate list of entries for a drop down lists. You can’t use a list that contains duplicates for a drop down list.
What if that list was updated automatically via a formula from the original data source? So as new items are added at the data source they automatically appear in the drop down list?
Currently a dynamic solution requires a complex array formula or a UDF (User Defined Function – macro).
This would also make creating formula-based reports so much easier.
Please take the time to vote for my suggestion. Feel free to post your own suggestions as well.
In the previous post we extracted the data from a CSV file. Now let’s create a report. It won’t be the greatest report as the data is pretty basic, but at least its a start.
It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months? Custom Lists to the rescue!
OK I have bitten the bullet and decided to get stuck in to Power BI. I am going to start playing around with Power BI Desktop – it is free after all. I thought I would blog about the experience and share my journey. I have created a Power BI category. I now have a button on my website that will list Power BI posts.
Microsoft has released Power BI desktop edition for FREE.
Power BI allows you to create reports and dashboards from lots of different sources.
It is not an add-in to Excel, it is a standalone application.
You can download it at the link below.
It is a straight forward process to install.
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
If you inherit a file or you haven’t used a file for while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it it too.
I was reading a magazine last week and a chart caught my eye. I thought I could improve it. I recreated it in Excel – its close to the original – see below – I didn’t quite match the column colour.
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.
When you drag a value column to the value section of a pivot table, sometimes the default calculation is COUNT, not SUM. This is frustrating, but easily fixed.
Excel has a few issues with dates. Mainly they relate to the dates that are imported into Excel. Different systems present different problems. This post solves an issue with dates that include times.
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match.
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
In my latest free webinar I provided a brief demonstration of PowerPivot in Excel 2013. I forgot to show you how to enable it. Luckily its easy.