If you need to find if text is in a column you can use the Text.PositionOf function to confirm it exists.
Monthly Archives: May 2021
Excel Power Query and Multiple Files [one hour video]
Includes detailed pdf manual
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.
Power Query can automate the importation process. It is now a vital skill to possess.
This is a follow up session to the Introduction session which you can view here.
This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables.
This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.
(Apologies I did have a coughing fit – I thought I had turned off the microphone but I hadn’t.)
Use the button below to download the pdf manual and example files so you can follow along.
We need to get the word out on this powerful Excel feature.
(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)
Filter Issue with Excel
Be careful with formatted tables
When you have a filter in place in Excel you typically only affect the visible cells when you edit multiple cells. There is a case when you are affecting all cells not just the visible ones.
Introduction To Excel Power Query [one hour video]
Includes detailed pdf manual
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.
Power Query can automate the importation process.
It is now a vital skill to possess.
This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.
Use the button below to download the pdf manual and example files so you can follow along.
We need to get the word out on this powerful Excel feature.
(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)
Missing Chart Data in Excel
Another macro to the rescue
The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
The Proof is in the Plants (Penguin, 2021)
A great book that brings together all the science that supports a plant-based diet.
The book is easy to read and accessible with lots of good analogies.
Plant foods are great for you and the planet and you can read about the science that backs that up.
The book pushes a mostly plant-based diet. It doesn’t push vegan or vegetarian. Being mainly plant based is the aim. The more the better.
Highly recommend the book.
I follow the Plant Proof podcast.
One Minute to Excel #13 – Create and Use a Check Box
Tick it to the next level
A check box is an easy interface to create and use.
See how to add one to a sheet and use it in a calculation.
Distinct Count Formula in Excel
New and old functions combined
It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.