Shortcut to lock or unlock a cell
The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.
Excel VBA to select the first cell in a range
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
(more…)How to Create Linked Array Syntax in Excel
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
(more…)Windows Clipboard
Did you know you can accumulate copied entries in the Windows clipboard? The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste. You can also pin items to the clipboard to keep them there. To change the setting. Click the Windows button and choose the Settings icon The select System. Then select Clipboard (bottom left) and change the Clipboard History to On - done. …
Creating a list of files in Excel
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
(more…)Export as PDF
If you frequently export files as pdfs then this keyboard shortcut may save you a little time. Pressed in sequence, not held down use Alt F E A
Excel Slicers Trick
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post. (more…)