Monthly Archives: September 2016
The Checklist Manifesto (2011)
It is amazing that a such a simple concept can be so effective.
Easy to implement and adapt to different situations.
Good insights into what makes an effective checklist.
It works in aviation and medicine.
Most accountants have a month-end checklist. Can you apply it to other areas?
I use a checklist for all my webinars.
Worth a read.
Open Paste Special Dialog
To open the Paste Special Dialog after copying or cutting use
Ctrl + Alt + v
WARNING: This conflicts with a default Evernote clipping shortcut. You can change it in Evernote use Tools > Options > Shortcut keys.
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.
A great site from Rob Collie and his team on PowerQuery, PowerPivot and Power BI.
Lots of free content and great ideas.
I have a few of Rob’s books and his knowledge is top shelf.
Don’t judge each day by the harvest you reap but by the seeds that you plant.
Robert Louis Stevenson
Refresh a PivotTable
The keyboard shortcut to refresh a PivotTable is Alt + F5.
PivotTables don’t automatically update if the data changes.
Matt Allington is an Aussie who is a Power BI expert and spreading the word on Power BI.
This is his blog, but the rest of his site has other resources as well.
His site has lots of content to help you dive into Power BI.
I have his book and am currently going through it.
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.
You Can Undo After You Save
I amazed how many people are unaware of a change that happened way back in Excel 2007.
In Office 2007 and later versions you can use Undo after you have saved the file.
In the old days (Excel 2003 and earlier) saving used to clear the undo list – it no longer does.
So undo to your heart’s content.
Remember there is a keyboard shortcut to undo.
Ctrl + z
Summing a range with Errors
If you have a column of values with errors, but you want to see what the values add up to, use the AGGREGATE function (added in Excel 2010).
If column A has the values and errors use
=AGGREGATE(9,6,A:A)
The 9 means SUM. The 6 means ignore errors.
I alone cannot change the world, but I can cast a stone across the water to create many ripples.
Mother Teresa
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.
Dustin Ryan works at Microsoft and he shares a lot of his knowledge for free on his website.
This great site has lots of free content to help you get the most out of Power BI – from beginners to old hands.
Free videos and other content make this a great site to bookmark and check regularly.
It covers a lot of other topics that are related to Power BI as well.
Enjoy!
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.
Seth Godin
March 7 2016
March 7 2016
Its a great question. The answer has changed over the decades.
This is a great piece of writing. Its a long read.
I recommend you read at least some of it if you have kids or grand kids or are interested in education or teaching.
RSM UK
2016-07-06
2016-07-06
“A new financial modelling guide authored by the ICAEW Corporate Finance Faculty and RSM aims to help businesses of all sizes plan and reduce risk. ” – website
If you use or build financial models then this pdf guide may be worth downloading – its free and no email is required – at least when I downloaded it.
Happy reading.
Don’t forget you can read pdfs on your kindle and iPad.
You can’t use up creativity. The more you use, the more you have.
Maya Angelou
Working with Quarters in Excel
You might not know that Excel knows about quarters. That’s 3 months, not 25 cents.
If you type Q1 in a call and drag the cell with the Fill Handle (bottom right corner of cell) you will see that Excel repeats the sequence Q1, Q2, Q3 and Q4. It doesn’t do Q5 or above.
This works for Qtr1 as well.
Show Horizontal Axis Entries Below the Chart
Great for displaying negatives in a line or column chart
I learned about a chart Axis option in Excel during a recent webinar – thanks to one of the attendees. You can show the Axis entries below the chart – this is handy for column charts that display negatives.
You have to know how to accept rejection and reject acceptance.
Ray Bradbury
Entering Today’s Date in a Range
If you need to enter today’s date in a range try this. Select the range and then press
Ctrl + ;
With the Ctrl held down, press Enter.
Done!
This will also work on a range in a filtered list.
A great site that has lots of free videos and other Excel content.
It covers the newer Power features of Excel plus the old standards of VBA and Dashboards.