It’s Friday again, so time to brush up on your Excel skills and learn some new tips and tricks. In this week’s 3 tip Friday we cover organizing information in Excel spreadsheets, how to return the week number of a date, and how to quickly display the macro run box with a shortcut. Let’s get started!
A quick win you can learn today -
I’m a big fan of making my spreadsheets as easy as possible for other people to use. This can mean a little extra work adding in information, organizing and naming it well. Depending on what type of file you’re creating, why not consider adding sheets such as the following:
- About: describe who made this file, when, and what is its purpose.
- How to use/Glossary: show definitions or a step by step guide on how to use the file correctly.
- Settings: consider gathering all the static data in the spreadsheet (dates, lookups, references) into one, a well-organized sheet where it can be viewed and amended. This can make your Excel file much easier to work with.
Check top ten ways to clean data here!
A keyboard shortcut I'm adding to my list -
If you have a workbook with lots of macros that you run manually, you’ll find yourself constantly clicking into the Developer tab in Excel to select ‘Macros’ or the Visual Basic window. You can do this quicker with a keyboard shortcut:
Alt + F8 displays the (Tools > Macro > Macros) dialog box
Alt + F11 displays the Visual Basic coding window where you can write and edit your macros.
A function I've found super useful this week -
Many businesses run their reporting periods on the week number in the year, running from 1 to 54 (a year can start and end with part weeks, so it’s not just 52). This might sound complicated to work out in Excel using only dates, but there’s a function that will convert any date into a week number automatically.
Use =WEEKNUM( and first select the date you wish to convert, which should be in a different cell. Follow that with a comma, and the second part of the function is a number to represent what you call the first day of the week - use 1 for Sunday, 2 for Monday, and so on.
No more manual counting to work out your week numbers!
As always, I hope you find these Excel tips useful, and please do reply if you have any comments or questions!