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.
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.
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.
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
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
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.
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.
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!