In this week’s 3 Tip Friday from DataMinded, read about freezing rows or columns in Excel to keep them visible while scrolling, how to enter today’s date using a shortcut and learn to rotate data from rows to columns or vice versa.
A quick tip to make navigation easier - Freeze Cells
I’m constantly working with large spreadsheets, scrolling down and across to get to where I need to be. This usually means I can’t see the column headers or first few columns on the left containing vital information. You can make Excel ‘freeze’ parts of the sheet to make the first few columns or rows stay visible as you scroll.
To do it, select the columns or rows that you wish to freeze, or if you want to freeze both columns and rows, select the cell at the bottom right of the area you want to freeze (e.g. to freeze row 1 and column A, select A1).
Go to the View menu, and select Freeze Panes. There are some pre-set options there too to help you get started.
A shortcut to insert today’s date
A super useful shortcut, if you don’t already know it is Ctrl + ; to insert the current date - as a fixed value. You could use =TODAY() or =NOW() to insert the current date as a function, but bear in mind that this will update each time Excel recalculates.
A function to convert a horizontal table into a vertical table
In my Excel training, I am always an advocate of creating tables vertically, rather than horizontally wherever possible. It makes data management and analysis much easier, and everything is easier to work with. If you have a horizontal table and you need to switch to a vertical table (with headers at the top of columns rather than the left of rows)... there are two ways to do it.
First, you can use ‘Paste Special’ and check the Transpose option - this is a good choice if you only need to change the table once. But what if you want 2 versions of the table, and you want one to automatically update with the other?
There’s a powerful function you can use, =TRANSPOSE, that will do this. Simply find space for your new table, and type =TRANSPOSE( followed by the range of the table you’d like to ‘flip’. Here’s the important bit - instead of hitting Enter, you need to use Ctrl+Shift+Enter for this special kind of array function. Excel will turn your table 90 degrees to ‘transpose’ the data!
I hope you are able to put these tips into action next week in Excel!
DataMinded Excel Instructor