Welcome to the Google Sheets Tips newsletter #328, your Monday morning espresso, in spreadsheet form! This is the last Google Sheets Tips newsletter for 2024. A big thank you for being part of this journey and reading these tips. Your Monday-morning-espressos-in-spreadsheet-form will return on the 6th of January 2025. In the meantime, I wish you all a wonderful holiday season with your loved ones. Tip 328—creating a 2025 calendar with a formula—is found below, but first: ➜ 2024 In Numbers
It's been a fun, challenging, and interesting year! Thanks for being part of it 🙏 I'll share more data and takeaways in an end-of-year review post that I'll publish... well, at the end of the year. ➜ Elsewhere
➜ Sheets Tip #328: 2025 Calendar FormulaWe're going to create a calendar in Google Sheets with a formula! In row 2, starting in column B, type in the weekdays as headers, i.e. "Monday", "Tuesday", "Wednesday", etc. Then, in cell B3, add this SEQUENCE formula: =SEQUENCE(53,7,DATE(2024,12,30),1) And voila! It's as simple as that. Now, let's make it prettier. Firstly, let's change the date format to something more readable. Highlight the whole table and go to the menu: Format > Number > Custom date and time Then select the format you want. I chose this day and month combination: Next, we'll hide the 2024 dates and the 2026 dates with two conditional formatting formulas. Again, with the data highlighted, go to the menu: Format > Conditional formatting Select "Custom formula is" under the Format rules and enter this formula: =B3<DATE(2025,1,1) Set the text color and cell fill color to white: Add another rule with this custom formula to remove the 2026 dates from the end of the calendar: =B3>DATE(2025,12,31) Again, set the text and fill colors to white. Now, add another rule with a custom formula to highlight the first day of the month: =DAY(B3)=1 And set the fill color to yellow. Add the final conditional formatting rule with this custom formula, to differentiate the months: =ISEVEN(MONTH(B3)) Set the fill color to grey or light blue. The order of the rules is important. It tells our Sheet the order of precedence, i.e. which rule to apply first. Make sure the rules are in this order: Add a nice cell fill color to the header row (manually!) and we're just about done. I'll leave you to add any finishing touches! Wishing you a great holiday season and see you in 2025! Cheers, |
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #354, your Monday morning espresso, in spreadsheet form! In last week's newsletter we compared wide and tall data, and what the pros and cons of each data "shape" were. Today, I want to show you how to move from one format to the other. How to transform tall data into wide data and vice versa. Going in one direction is easy. Going back, in the other direction, is less so. Find out which way round below! ➜ News I. It's...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #353, your Monday morning espresso, in spreadsheet form! Today we're looking at the shape of data. It's foundational concept that underpins pivot tables and charts in Google Sheets. Once you understand the difference between wide data (for charts) and tall data (for pivot tables), you'll be off to the races! This week is all about the pros and cons of each data shape. Next week, I'll show you how to use formulas and...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #352, your Monday morning espresso, in spreadsheet form! If you're a parent, then you've undoubtedly watched a lifetime's worth of Disney movies. You can appreciate the genius storytelling, rich characters and beautiful visuals. (But I'm sure you'd also be content to watch a film once, not 10 times!) Anyway, there's a scene in the Disney film Ratatouille where Remy bites into a strawberry and a piece of cheese at the...