The Excel Expert Blog
Tracking Training Records in Excel
Upgrade on Time/Date Entry post – using TimeValue function
Suggested revised formula. A while ago I posted a blog post with a suggestion from a reader about how he quickly formatted batches of dates and times. He saw the post and came back with a revised formula - using TimeValue. He had originally suggested...
Excel Path – Step by Step
Faster Filtering with Filter()
Filter() is one of the new dynamic array functions in Excel. So what does that mean? If you have used Excel you will know that generally it has made like a single celled organism - create your formula in ONE cell and then copy it down or across. The new range of...
14 – Meet IFError() – the blankie function of Excel
Many years ago when my son was small and loved a certain purple dinosaur, there was also a smaller dinosaur called BabyBop who had a comfort blankie that he carried around at all times. You can remind yourself about it here. Excel has one of those - it's called the...
07 – Get your list to answer a question – cool filtering..
This is my first blog post of 2017. I have to say I do love the energy of a new year. My favourite time of the year are those days between the "two Christmases" i.e. 25th December and 6th January. There is no pressure. Just movies to be watched, sweets to be eaten,...
17 – Planning a Pivot Table | Guidelines – from Debra Dalgleish MVP
Debra Dalgleish (http://www.contextures.com) is one of my Excel heroines and the pivot table guru. She is someone whose work I regularly reference in class. I recently asked her to help me answer a question that regularly comes up in class when people ask about pivot...
13- Do you have to set up a spreadsheet from scratch? | Excel Novice Special
One of the topics I cover in my new book Your Excel Survival Kit: A guide to surviving and thriving in an Excel World (ooh, still get a thrill when I say that!) which will be out in June 2016 is how to set up your data to make it easy to work with. If you...
Case Studies – How To
Creating a simple accounting system – Part Three – ~Another way to summarize your payments
So this is part three of a series of posts I am doing on creating a simple accounting system. Just a gentle reminder that if you think your business could grow - I would really recommend that you look at investing time and money into an actual accounting software...
Creating a simple accounting system – Part Two – Summarizing your payments
Last week I showed you how to set up your payment journal in a way that would allow you to both track the total amount and break it down across different categories and VAT Amounts. You can check that blog post here.. In this week's blog post I look at how you would...
Creating a simple accounting system – Part One – tracking your payments
In some recent training I did I showed the participants a template I had created to allow a small business to track their expenditure. If I am really honest I'd actually recommend that if you are self-employed or running a business - a better investment would be to...
How to summarize multiple CSV files – in minutes
This scenario came from a recent dilemma a customer had. They got basically the same CSV file every month from a customer but they wanted to have an ongoing summary of it. Of course they could always have done copy and paste - Ye Olde Reliable Standyby...BUT I...
Summarize same item across multiple worksheets – even if they are in different locations…
Another Judge Judy entry - "real cases real people" although the numbers are fake - sorry to disappoint you there... In this one I show you how to combine using the SUMIFS function with a slightly trickier function called INDIRECT to create a summary sheet that...
Excel – General
Using index/match/match to locate a value in a grid
You may be familiar with index/match but there's another variation that I like to call index/match/match (like New York - so good they named it twice). This is about how to use a Match function (which I describe as being a bit like salt - you generally don't eat it on...
May I recommend….Recommended Pivot Tables and Analyze data
One of the most common questions I get asked in class about pivot tables is "Well, how do I know what goes where?" . In previous versions of Excel I would have recommended the following.. Go to the ever-wonderful Google and type in "How to plan a pivot table in Excel"...
Some lesser known charts in Excel – Treemap and Funnel
Excel has really upped its games around charts in the last couple of editions but there are some charts that are not so well known. In this blog post I am going to introduce you to two of them: The Tree Map and the funnel. You can use this file to practise on and here...
Pivot table from multiple tables in a single file – without a vlookup?
The usual wisdom around pivot tables is that they are generated from a single source of data. That is generally true. However from Excel 2013 onwards you can use the data model feature. The same rules still apply as using a vlookup (or indeed index/match) in that you...
You’ve heard of Pivot! Now let me introduce you to Unpivot!
Often when we get data it's done in the format as seen below However quite often we need it to be organised differently - for example if I want to set this up to be able to calculate the monthly total for each product in a pivot table - not really possible as...
Random
What I wanted to say…to people who arrive late to class (Inspired by the Guardian’s “the letter I always wanted to write”)
You know the movie Jerry Maguire where Renée Zellweger says to the Tom Cruise character.."you had me at hello"...well when you arrive in late to my class, I feel exactly the opposite. You arrive in late - 5 minutes, 10 minutes, 20 minutes. Sometimes you apologise....
Excel tip cards from me & MrExcel (we haven’t got a thing goin’ on)
First of all, if you don't recognise the allusion in the title - here's the Michael Bublé version.... MrExcel (the leading Excel publisher) and I have collaborated to create some Excel tipsheets. These would be useful for trainers, or for reference guides for your...
Meeting Excel Academy Award nominees or Excel MVP Conference – April
Last month (can hardly believe it!) I attended a two day conference in Amsterdam on Excel. It was with the Excel MVPs. (Most Valued Professionals). For me, it was a big thrill...(it's OK...no sniggering!) as at the end of every class I give a list of resources which...
What’s happening? | Powerpivot, Breaking Bad and Behind the Candlebra
Well, have to say I've been off the grid (at least for this blog post) for the past while but my intention is certainly to begin blogging again - bringing you lots of tutorials and interesting tricks and tips from the world of Excel. Recently I have been getting...
02 Powerpivot | Installing Powerpivot – and how do I start?
So if you have read my first post in this series on Powerpivot, and you are thinking about exploring it a bit more here are some resources to check out. Installing Powerpivot There are two versions one for 32 bit machines and the other for 64 bit machines. You can...
Personal
No Results Found
The page you requested could not be found. Try refining your search, or use the navigation above to locate the post.