Create a Team Calendar in Excel (2023)

Introduction

Peter Kalmström shows how to create a custom team calendar in Excel: www.kalmstrom.com/Tips/Excel-Calendar.htm

Content

Hi I'm Peter Kallstrom of Kallstrom comm business solutions.

Team calendars is a popular subject.

Almost every company has won, almost every team needs one and they can be done in so many different ways.

I'm gonna show you one way of doing it in Excel.

First of all, I want to show you the building once the I go to new here and search for calendar.

You'll find a lot of different calendar templates, but I want to show you a different version than these words, but if one of these fits your needs, that's of course perfect, and you should check them out before going down doing it manually as I'm gonna do so.

What I want is a monthly calendar with the staff on the columns here and then I want the days on the rows.

So I wanna have a month template and then I wanna hack create a new one for each month and fill in the information for my staff there.

Of course, there are other ways of doing this and outlook and in planner or in SharePoint, but this is just one: do it yourself in Excel? So, first of all, we want to have the staff and then we're gonna have the people I just have two people here that I'm gonna be working with access examples and, of course, I double click here between the columns to make that wider, so that it all fits and then I'm gonna start with the first date that I'm gonna have so I'm gonna write that in Swedish format, which is the one I had know best.

That's the first of January 2018 and, of course, I have this set on American now.

So that shows up like that.

All right, yeah and the time format is American.

Then, unless II column there I'm gonna close at the same date, plus one so I'm, just gonna do b1, plus one I know.

That, of course, gives me the next day and then I'm gonna fill this out all the way out to AF, which is corresponds to the 31st and to get to the beginning in I'll, hold down the ctrl and the shift key and press the left arrow, which allows me to select everything and then I double click so that it all fits all right now.

I have all the way from first of January until the 31st all January.

As you see, this looks very unreadable, though so I'm gonna change this a bit.

First of all, I want to put the same thing in the row under I'm, just gonna do equals, and the the cell above and then I'm gonna fill that out all the way again right.

That didn't make much of a difference that certainly didn't make it more readable, but what I'm gonna do now? Instead, I'm gonna select all of this all these dates here and go all the way to the right control shift, the right arrow and I'm gonna format.

These numbers, I'm gonna, show only the weekday here I do that by entering DDD in the custom format.

Here that shows me the weekday: that's what I want in the first row, I do D D, D D, with four days and I get the full day a day, but I want to keep it readable.

So I'm, just gonna go with the three first letters of each weekday, I'm gonna do the similar thing and ctrl shift right arrow to get all these dates and I'm.

Just gonna show the weekday or actually the day number within that, not so I'm gonna format, the number again go into custom and then write just D all right: D, D and I get the zero one format which might be nice right next I'm going to manually go in.

Actually, let's not do that yet and I was gonna manually highlight the weekends, but I'm gonna do that later, because I'm gonna start by a template first, the thing I want to do in this templates.

First is just put some lines in here and the easiest way of doing that is to use the draw border now.

I can just draw our border here.

Really nice like that, and then draw a border there.

All the way out, as you see it, doesn't really fit on my screen, so I'm gonna do one more thing with these quit the drawing now I'm going to Center all of this.

Let's do that Center vertically yep and then I will make it slightly smaller and I'm gonna do that by double clicking.

So now it actually fits on screen.

On my resolution at least I think this looks rather okay as a template.

Now the next thing I'm gonna do is name this sheet template and then I will go and drag and drop that template to the left.

There we go so now.

I have template two and I can rename that to Gen 2018 like that and I can pull it in order it like that.

So that's the first one.

Let's do it a February, also just to make this clear so again, I'm take the template and pull it over there and then make that February for February.

Of course, I want to change the start date.

Just do that in one cell and that's going to be 2018 zero, two zero one right and then, of course, I need to make sure that it all fits now.

I'll go in and just color code weekends I do that by Yello, then I'm actually going to hide the roads that I don't need.

That's just gonna, do shifts, control down arrow and then right-click and hide these rows and the same thing.

I'm gonna do with these columns.

I don't need so again select and shift control right arrow and then hide all of those columns.

I should perhaps have done that in the template, but I guess you get my drift here now for actually scheduling things in the calendars.

I'm gonna recommend this method.

So let's say it's Tina.

She is actually on vacation here for this period, so I'm gonna select that period when she's on vacation just type in vacation and then enter I'm still have the whole range selected and then I'm gonna, merge and center.

That and then I'm gonna do some color coding for that.

So, let's put that on green, for example, and then we can go ahead and do cat Akula he's gonna be at mouse up for those two days again: I'm gonna, merge and center and then color code that that's a you know some kind of important project there, so I'm gonna, color code that in blue, maybe have a white text to make that more visible.

That looks good now, if I want to copy this entire, he does that every week let's see it's Monday and Tuesday at NASA and I can just right, click that and copy, and then do the same next week, ctrl V to paste that ctrl V to paste that so they have shown you how to schedule things.

I think that look should rather nice.

One further thing that you might want to do is learn how to erase these things.

Of course, you can always go back to the template, but but if that NASA thing gets council there best way to do that.

If you just press Delete, you see you still got that emergent Center and the color coding you know so that doesn't do it.

So if you want to clear that out, I was just taking an empty day, copying the format and then just erasing that and then you get blank day.

So that shows you how to build a team calendar from scratch in Excel.

Thank you for watching this demonstration.