.JPG){kind=small}
Get ahead in your career by learning Excel tips and tricks that will help you maximize your productivity and efficiency at work. This content delves into practical and insightful shortcuts and techniques to manipulate data and design advanced spreadsheet functionality.
Key Insights
- In this workbook, the defined names can be viewed through the name box above column A. The selected name shows the corresponding selected range.
- One Excel tip is to go to View/Zoom and select 39% or less to see all range names as long as they’re more than one cell wide or tall.
- There are a set of shortcut keys that involve holding Ctrl+Shift down, then using the values 1 through 6 and more to perform various functions within Excel.
- If you have the page setup just the way you like it on one sheet and want to use the same setup on another sheet, you can do so by placing the desired sheets into “group” mode.
- A tip for those who write VBA code and use Userforms is that there is a simple userform that displays a listbox with the months in them. This can be achieved by initializing the form with one line.
- The list of months is automatically generated in Excel's File/Options, in the Advanced section, near the bottom where you can Edit Custom Lists. The 4th one of these is the list of months which you can reference by Application.Getcustomlistcontents(4).
Excel Analysis Workflow
-
Clean the Data
Remove duplicates, handle blanks, normalize text.
-
Add Helper Columns
Compute the derived values you'll analyze on.
-
Build Pivot Tables
Drag dimensions and measures to summarize.
-
Visualize with Charts
Add a chart, format axes, and tell a clear story.
This is the first of a series of articles on Excel tips and tricks. Let’s dive right in. In this workbook, there are 3 names defined:
If I select NYC, I see this:
You can see in the name box (above column A) that NYC is the selected range. The other two ranges’ addresses can be seen here:
So what’s the tip/trick? Go to View/Zoom:
Select 39% (or less):
You will now see all range names show up (as long as they’re more than one cell wide or tall):
Pretty cool, eh?
OK, let’s look at another set of tips. Here, we’ll examine many shortcut keys. They all involve holding CTRL+Shift down, then we’ll see the effect of using the values 1 through 6 and more:
Here’s a tip on printing – suppose you have the page setup just the way you like it on sheet1, but you want to use the same setup on sheet2, or other sheets. Do you have to redo all the setup steps again? No – simply place the desired sheets to inherit the desired page setup into “group” mode. You do this by CTRL/clicking another sheet tab, or shift/clicking to put all the sheets between the active sheet and the one you shift/clicked into group mode. When you do this, you will see the word “Group” at the top:
Here's sheet1 preview:
Note it’s landscape, has row and column headers showing, has a header of “My Center Header”, and date and time at the lower right corner.
Sheet2 has no settings. Once Sheet1 and Sheet2 are in group mode, and Sheet1 is the active sheet (the one with the settings you want to copy), you simply visit the Page Setup dialog and click OK:
Then you see 🡪
And that’s it. All the sheets in the group now have the same page setup as the active sheet. What’s left to do is take the sheets out of group mode. The easiest way to do that is to right-click the sheet tab and select Ungroup Sheets:
Here’s one for those of you who write VBA code and use Userforms. Here’s a simple userform that displays a listbox with the months in them:
How do you get the months in the list? I’d guess most either write initialize code to fill the listbox, something like this:
This works – the value of i the first time is 1, and formatting 1/1/,000 with MMMM is January. The day and year are pretty arbitrary.
Or something like this:
This also works, but is a lot of typing. Since the array is 0-based, the subscript is i-1 instead of i. We could have used i if the loop were For i=0 to 11.
Another way would be to put the months in cells – pretty easy because you need only to type January then use the fill handle to get to December, like this:
And then in the user form design, you can select the listbox and simply point to that range in the Rowsource property:
But here’s the tip – you don’t need any of those ways – there’s a direct way:
All you need in the initialize event of the form is one line:
What’s GetCustomListContents(4)?
In File/Options, the Advanced section, near the bottom is Edit Custom Lists:
When you click this, you see:
The 4th one of these is the list of months! You reference this list by Application. Getcustomlistcontents(4)! Had I tried Application. Getcustomlistcontents(2), I would have seen this:
Hope you enjoyed the first article of Excel tips & tricks.
.JPG)
