Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
I cannot remember the books I have read any more than the meals I have eaten; even so, they have made me.
Ralph Waldo Emerson
A little knowledge that acts is worth infinitely more than knowledge that is idle.
Kahlil Gibran (1882 to 1931)
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.
Knowledge without Action is useless.
Action without Knowledge is foolishness.
Sai Baba – Indian spiritual master
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
Filter in Power BI Data View
Filtering coming soon to the Data view in Power BI. Fingers crossed this gets done soon – patience everyone.
The man who does things makes many mistakes, but he never makes the biggest mistake of all – doing nothing.
Benjamin Franklin (1706 to 1790)
You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas.
Copy Across Shortcut
In the structure below let’s assume you want to copy the SUM formula from cell B5 to cell C5.
Obviously you could use copy and paste, but that would require a few keyboard presses or mouse clicks.
Given that we already have cell C5 selected we can use
Ctrl + Shift + >
This copies whatever is in the cell on the left to the current cell.
Adversity is the state in which man most easily becomes acquainted with himself, being especially free of admirers then.
Samuel Johnson (1709-1784)
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation.
When data is imported into Excel sometimes the values come in as text rather than values. Most functions can’t perform any calculations with text numbers, but one can. See how easy it is to add up text values.
All glory comes from daring to begin.
Eugene F. Ware (1841-1911)
Word tip – increase font size
I recently found this keyboard shortcut for MS Word to increase the font size of the selected word(s).
To reduce the font size use
Our grand business in life is not to see what lies dimly at a distance, but to do what lies clearly at hand.
Thomas Carlyle (1795-1881)
Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
We could never learn to be brave and patient if there were only joy in the world.
Helen Keller (1880-1968)
Some articles make a greater impression than others. I was recently asked about a magazine article from August 2011.
Someone had changed jobs and needed to be reminded of the rolling 13 month technique.
The congratulations relate to 15 years of Excel Yourself!