If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter.
Tag Archives: data
Filter by Cell Value Keyboard Shortcut
Its quicker to use the keyboard
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
Understanding Filter Context in DAX
An Excel-based explanation
Are you trying to get your head around Filter context in DAX? I watched a video from the sqlbi.com guys and it explained it well. I thought I could add an Excel flavour to it.
Creating a Unique Dynamic List in Excel
A Power Query technique
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
Automatically Populating a Column with Text in a Formatted Table
A formula trick
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
Excel Subtotal Sort Magic
Sorting by subtotals is possible
Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.
Do You Have Excel Inquire?
Its a built-in audit add-in
Inquire is a new add-in in some versions of Excel 2013 and later versions It is an auditing Add-in that can analyse and report on your Excel files.
Trailing Minus For Negatives in CSV vs TXT Files
You can control TXT files much better
Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.
Alt Key Shortcuts
Two more
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Creating a Hyperlink in Excel Based on a Search
Teleport to a cell in a data table
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
Totalling Tip For Excel
Make the most of SUMIF
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Using Lists in Excel
Make the most of Format as Table
It is common to work with lists in Excel. Lists of departments, names and other categories you frequently use. This blog post covers a few techniques that work really well together to create robust reporting systems.
VLOOKUP and COLUMN Function Warning
Be very careful using these two together
I saw a technique demonstrated recently with VLOOKUP that I hadn’t seen used before and thought at the time, that’s handy. Upon reflection however, I thought that’s a bit dangerous.
Publishing a Power BI Report
Getting it out there
Well after getting the data and creating a report and then a chart, let’s get the report onto the web.
Sorting By Colour in Excel
It is possible
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.
Power BI – A Chart
The ultimate goal
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
Vote For a New UNIQUE Function
Dynamically extract a list of unique entries from a list
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.
Creating a Report in Power BI
Converting Data into Information
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.
Custom List Trick
Create looping sequential codes
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!
Power BI – Just starting out
The start of my journey
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.
Power BI Desktop – Download
Its FREE!
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.
Click the here to go to the Microsoft site to Download Power Bi Desktop
It is a straight forward process to install.
Handling Text and Real Numbers with VLOOKUP
Helping you work with imported data
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.
PivotTable Listing Macro
Create a list of all PivotTables in a file
If you inherit a file or you haven’t used a file for a while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
Getting a unique list in Excel
A PivotTable trick
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it too.
When a Bar Chart is Better
Before and after charts
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.
Useful Excel VBA Range Technique
Titles made simple in VBA
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.
Why Does My Pivot Table Default to COUNT?
And how to fix it
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.
Another Excel Date Issue Solved
Times Affecting Date Calculations
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.
VBA to Open CSV and Avoid Date Error
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.
Sequential Numbers With a Twist
How to ignore hidden rows
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?