When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution.
Tag Archives: data
Free Webinar Recording – Introduction to Power Query
Feedback score 94.5% based on 91 responses
In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network.
The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video.
Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources. Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
This session covers
- fixing dates so that Excel can recognise them
- formatting columns as text – retaining leading zeroes in CSV files
- deleting unwanted rows and columns from your data
- removing leading and trailing spaces
- populating blank values with zeroes
- populating blanks with entries from above
- correcting trailing minus signs
- unpivot a report – how to convert a report layout into a data table layout
- converting a MYOB report into a data table
Hiding Zero Rows in Excel with No Macros
Filter technique
Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution.
Promoting Headers Issue in Power Query
Be careful if a first row name changes
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.
Restricting column inputs based on the current month
A data validation solution
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
Power Query Fill Down Doesn’t Work
The fix is easy
Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix.
Format as Table Extends Data Validation
Another advantage to formatted tables
Excel’s Format as Table feature on the Home ribbon has many advantages. One advantage that isn’t mentioned much is the automatic extension of Data Validations.
Excel Comments (Notes) and Freeze Panes
Row height solution
Inserting a Comment (renamed to Notes in newer versions of Excel) in the first row and then using Freeze Panes to keep that first row visible can cause issues displaying the Comment (Note). Here’s how to fix it.
Free Excel Webinar Recording – Mastering Form Controls
Feedback score 92%
In March 2019 I demonstrated how to use some of Excel’s Form Controls.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Learn how to build better spreadsheet interfaces using form controls like check boxes, scroll bars, option buttons and combo boxes.
Form controls can reduce the number of input errors; simplify and speed up input plus add structure to a file.
Discover the function that you need to use to get the most out of these controls.
Find out a few tips and tricks to make creating and using form controls easier.
As always there are a few more tips and tricks shared in the session.
Pasting or inserting with a Formatted Table
When a little icon makes all the difference
When you need to paste new rows to the bottom of a formatted table, or insert blank rows at the bottom, one way is much quicker than others when working with large, complex models.
Unique Entries in Excel via a Conditional Format
Filtering to the rescue
Excel’s Conditional Formatting feature has a built-in unique option. Its unique option only identifies entries that are not repeated. This is different to the Advanced Filter Unique option which lists each unique item from a range once. To filter by entries only appearing once you can use Conditional Formatting with filtering. No formulas required.
Free Excel Webinar Recording – Format As Table Features
Feedback score 93%
In February 2019 I demonstrated how to use the Format as Table feature in Excel, including some advanced techniques.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
- advantages and limitations of formatted tables
- keyboard shortcuts
- using formatted tables with formulas
- solutions to some of the limitations of formatted tables
- using range names with formatted tables
- using formatted tables with data validations
- creating a running total
- using PivotTables
- Relationships (Data tab)
As always there will are a few more tips and tricks shared in the session.
PivotTable Grouping
Create your own categories
Grouping of dates in Excel’s PivotTables is fairly common and in the most recent versions of Excel, automatic. Many people don’t realise that you can perform other types of grouping in Excel.
Improved Data Structure
Tables rule
I ran a webinar in January 2019 where I presented and explained a budget challenge file I had submitted in November 2018. I mentioned during the session that I didn’t like the layout of the Data tab. Well someone asked how should it look? So here is how I would have arranged it.
Free Excel Webinar Recording – Budget Challenge
Feedback score 93%
In January 2019 I presented a webinar that examined a solution to a 4 dimension budget challenge. Download the materials using the button below and watch the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
NOTE: This is not a beginner’s session.
This webinar is based on a budget scenario which you need to read before the session starts. It is only 3 pages long and included in the materials.
Topics covered include
- using INDEX-MATCH (better alternative to VLOOKUP)
- 3-D formulas and techniques to make using them easy
- using a reporting template
- validations
- extracting sheet names
- automating reports
As always there will are a few more tips and tricks shared in the session.
Data Validation Shortcut
Two options
The Data Validation dialog allows you to limit or control what a user enters into a cell. There are two keyboard shortcuts to open it.
Extracting entries from a slicer
A simple hack
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
Sort by value ignoring the sign
An absolutely simple function to the rescue
I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44.
Distinct Count in Excel
The Data Model to the rescue
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.
Updates to Pivot Tables
Time-saving improvements
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.