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.
Discover the functions that handle different types of calculations.
See how to avoid some of the frustrations that dates and times can cause. Topics covered include:
adding months to a date
formulas for the first and last day of the month
using today’s date in a formula
handling days of the week calculations
automating dates in headings
how to avoid the traps in time calculations
remove times from dates
generating random dates (for testing)
As always there will be a few other tips and tricks shared during the session.
In my previous post I showed how to have a flexible data validation and how to validate it. This post will look at adding conditional formats to inputs and validations.
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
The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.
You can create a hyperlink to another Excel file in Excel but you can’t control what sheet or cell will be active. Well you can actually, but you need to know how.
It is considered best practice to use Measures for all your values in Power BI reports and dashboards. One way to make it easier to find Measures is to group them together.
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 year’s webinar includes
a new logic function IFS
how to do budget allocations across months using the MOD function
how to do complex monthly budget allocations
using helper cells to achieve advanced conditional formats
As always there will be other tips and tricks shared along the way.
Once you start to use Power Query you may find yourself with quite a few queries in the one file. To make it easier to control them you can use groups to keep similar queries together.
Did you know you can sort by colour in Excel? Did you know you can sort ascending or descending within that colour? I was asked a question in a recent webinar and in answering I found out that you can sort within a sort.
One of the most common custom number formats used in Excel is the mmm-yy format. As an example this format displays all the dates in January 2019 as Jan-19. This format is used in most reports, budgets and financial models. There is quicker way to apply it than using the Format Cells dialog.
The Duplicates option under conditional formatting is useful to identify when there are duplicate entries within a range. This requires you to review the range to see if there are any duplicates. You can use a formula to identify ranges that contain duplicates.
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 webinar is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.
The Chart interface changed in Excel 2013 and I take you through some of the changes.
You will see best practice design and formatting techniques demonstrated and explained.
See how to create dynamic charts that automatically change based on selections made or data added.
Learn about the feature added in Excel 2010 called Sparkline charts.
If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it.
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula.
Power Query feedback
Received this email after a recent Power Query session – some things may take a few goes to understand, that’s OK, that’s how we learn.
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 lots of tips, tricks and techniques to speed up the formula creation process. You will learn:
how to quickly start a formula
the benefits of the numeric keypad
to use AutoComplete to save typing
how to easily insert $ signs to fix references
about the calculation sequence
how to use the colours Excel displays when editing formulas
the tricks on selecting large ranges
using formulas and Format as Table together
a helpful technique when working with ranges in other sheets
