In October 2008, I have started an ambitious series of posts on this blog called – Spreadcheats. These are little tricks, nuggets, tutorials on using Excel that would make anyone a spreadsheet guru.
The spreadcheats series has been wildly successful. I am compiling all this useful information and articles in to one big post so that anyone can follow the links and become good in Excel. Read on,
[Note: This is not for beginners. If you know what a formula is, you would enjoy this 31 articles]
1: Insert Line Breaks in a Cell
Press ALT+Enter keys in a cell to make a new line inside the cell. [Read this]
2: Select all cells in a range
Use these keyboard shortcuts to select all the cells in a range or group. You can find 90 more shortcuts on that page. [Read this]
3: Using Mouse in Excel
Many of us know at few keyboard shortcuts. But what about mouse short-cuts? Read this post to learn interesting mouse shortcuts that can boost your productivity. [Read this]
4: Using Mouse in Excel – Part 2
In the second part of Mouse shortcuts, we explore double click tricks in Excel. [Read this]
5: Save time by using chart templates
In this spreadcheat, learn how to make your own chart templates and re-use them to save time. [Read this]
6: Make ToC (Table of Contents) in Excel – and other tricks
We all run in to large excel workbooks one time or other. Read this post to find out how you can manage when you have a large file. [Read this]
7: How to print spreadsheets one just one page?
Use the little trick in print settings to print any worksheet on just one page. [Read this]
8: Write better formulas by knowing the difference between relative and absolute references
Quick, what is the difference between A1 and $A$1? If you said 2 dollars, you are the right person to read this article. Learning the differences and usages of various reference types in Excel is important if you want to write better and simpler formulas. [Read this]
9: Remove duplicate items using formulas
Learn how to remove duplicates, identify unique values etc. using formulas in this article. [Read this]
10: Introduction to VLOOKUP Formula (and MATCH, OFFSET Formulas)
VLOOKUP remains one of the most important and very useful formulas in Excel. Learn how to write vlookup formulas by reading this article. [Read this]
11: Introduction to 3D References in Excel (a tutorial on Employee Satisfaction Surveys in Excel)
In this tutorial, we will explore a feature called 3D References in Excel and build an employee satisfaction survey form in Excel. [Read this]
12: Introduction to SUMPRODUCT formula
Learn how to use SUMPRODUCT to find sum of values that meet more than one condition. [Read this]
13: Introduction to ROWS and COLUMNS formulas
One of my recent favorites, ROWS() formula is very useful to generate sequential numbers in Excel. [Read this]
14: Calculate Moving Average in Excel
Use excel formulas and relative references to calculate moving average from your data. [Read this]
15: Introduction to FREQUENCY formula
We use FREQUENCY formula in Excel to generate statistical distribution of a set of values in this example. [Read this]
16: How to understand and fix excel formula errors
If you are ever perplexed by #N/A, #NAME! and said #$#@ to excel, this is the article for you. Read it to learn what these errors actually mean and how to fix them. [Read this]
17: Quick tip to Debug Complex Excel Formulas
Use Function key F9 to debug lengthy and complex excel formulas. Select a portion of the formula and press F9 to instantly evaluate that portion and see the result. Read this article to find out how to use this trick. [Read this]
18: Use Find / Replace tool to change formulas
Learn how to use Find / Replace tool in Excel to quickly edit formulas and change them. [Read this]
19: Introduction to COUNTIF and SUMIF Formulas
COUNTIF and SUMIF are very simple yet very powerful formula tools for anyone using Excel. In this article, explore these formulas and learn to use them. [Read this]
20: Introduction to Array Formulas in Excel
Array formula is like a mega formula that would work on an entire range of cells and return another range of cells. They are useful for scenarios where the output we need is not one value but a set of values. In this introductory example, learn how to write your first array formula. [Read this]
21: Introduction to Excel Conditional Formatting
Excel conditional formatting is your way of telling excel to highlight / change formatting of cells that meet certain criteria. This is a good way to draw attention to few points from a large table. Learn how to use Excel CF in this introductory article. [Read this]
22: Introduction to Excel Camera Tool
In this article, learn about Excel Camera (or Snapshot) tool which is useful for making a live snapshot of a range of cells. [Read this]
23: Introduction to Excel Pivot Tables
Pivot tables are a powerful way to analyze and report your data. In this introductory post, you will find all the basics of pivot tables and learn some tricks. [Read this]
24: Introduction to Excel Goal Seek
Goal Seek is opposite of formulas. In formulas we tell excel a bunch of values and excel finds the result. In goal seek, we tell excel what the result should and excel tells us what kind of parameters should be given. This is useful to find, for eg. Your retirement nest egg value … [Read this]
25: Introduction to Combination Charts in Excel
Learn how to combine 2 different chart types in to one chart in this article. [Read this]
26: Make a Dynamic / Interactive Chart using Data Filters
Do you know that we can use data filters to filter charts as well as data? Well, in this article, you can learn a powerful yet simple trick to make a dynamic chart in Excel using data filters alone. [Read this]
27: Make a Dynamic / Interactive Chart using INDEX Formula
Learn how to set up a dynamic or interactive chart using INDEX formula and Camera tool in Excel. [Read this]
28: Make Collapsible Charts using Group – Outline Tools
We can collapse / expand charts using the group and outline tools in Excel. Learn how to set up such a collapsible chart in Excel. [Read this]
29: Showing Chart Labels in Different Colors – Charting Tricks
Learn how to use custom cell formatting codes to show chart labels in different colors based on a criteria. [Read this]
30: Advanced Data Validation Tricks in Excel – Part 1
In part 1 of excel data validation tricks, we will learn how to use excel formulas to control the way data validation works. [Read this]
31: Advanced Data Validation Tricks in Excel – Part 2
In part 2 of data validation tricks, we will learn how to prevent duplicate data entry using data validation formulas. [Read this]
Happy learning 🙂
23 Responses to “Shift Calendar Template – FREE Download”
Hi Chandoo,
your recent postings include only Excel 2007 templates. Unfortunately the company I work at still runs Excel 2003. Is it possible to get your awesome files in other excel version as well?
Thanks so much for your great excel stuff!
Is it possible to do this for shifts with hours instead of days? To organise a three shift day?
Thanks in advance,
Stelios
In my organization there are 45 employees i need split then into three shifts ex:A shift:14,B shift:14,C shift:14 and week off:3 kindly help me on this.
@Masthan
You need to understand what rules your company has for the various shifts / roster combinations
Chandoo, I once did a shift control spreadsheet for my team. I put one person in each line, the columns were the days. I put a shift code in each cell indicating in which shift that person should work, or if the person were out that day. I have two codes for being out. One is for vacations and one is to compensate days worked in weekends. This way I was able to count how many persons I have in each shift, how many were on vacations and how many were out compensating (that's the term we use here) weekend worked hours.
Later I included the possibility of a person be in two lines one for normal hours other for overtime. This is mainly used for planning purposes. If you would like I can send you an example. The only problem of this spreadsheet is that we don't have a person view, only this consolidated view.
Hi George, I would like to have a copy of your spreadsheet if you can share it.
Thanks in advance, Chuck
Hi Chandoo,
Where is the code located ? is it VBA ? If so , how do you hide it ? Or it is .NET ?
Thx
@Idan
.
No VBA or code, it is all done with Mirrors.
Only Joking,
.
But there is no VBA or code,
It is all done with Named Formulas and Lookups.
Have alook at the cells in the calander area and Named Formulas in the Formulas, Name Manager Tab.
How can i calculate between two or more different workbooks? Please, reply me as early as possible.
@Anand
Open the workbooks you want to link to
Start a formula = and click and change between workbooks as required.
You can use the View, Switch window menu to change workbooks mid formula
The format for using workbooks is
=[Workbook.xlsm]Sheet1!$A$1
or
=SUM('[Book2.xls]Sheet1'!$A$1:$D$10)
etc
Hi Chandoo,
I am working with a call centre wherein i ned to update at the month end 20 to 30 employees login hours which are defict to track it at the month end is very difficult is there any template which can be made to track that why on a particular day a guy who needs to be on calls was why not on calls.
Thank you so much Chandoo. This is really helping me. As usual, you rock.
What's FortyTwoDays and Calendar in Name manager?
Both are unused and FortyTwoDays doesn't make any sense.
I have a SQL db that contains records of events scheduled/completed on a particular date. Can this method ous building a calendar be used to display those events on the respective day?
Positively awesome!
I'm attempting to help a friend create a schedule for adult classes - and of course its not"paid help". Here is the scenario:
20 classes, instructor, room#, student class size, start date, number of class days (need to subtract weekends)
class
instructor
room
students
start
#days
PATH
karen
201
21
01/01/13
11
BILLING
jane
401
15
01/12/13
13
MEDISOFT
mike
301
11
01/25/13
9
he'd like to see these classes show up in different colors within the same month's calendar chart. He can draw it, but I'd like to see it done automatically through data, and I just can't visualize it, but I KNOW this will work - can you help?
Jan 🙂
Dear chandoo,
Try many way to download still can't access. Any way we want to try out 3 shifts with 3 guys in a group .eg Group A Morn, Group B Night and Group C Rest. And every each group must work on sunday to take turns. In fact we are security teams so that's why sunday is required to work. Pls guide and show how to put in the working calendar. Thank you in advance.
I've been trying to copy and/or recreate this to use in a workbook I'm doing for the transportation department I'm working for. I need to have the calendar on the first sheet in my document (it has graph's from data on another sheet). I'm trying to use it to track (with the conditional formatting) accidents and injuries. I've redone the conditional formatting to do 4 different accident types (no injury, near miss, OSHA recordable injury and work loss injury), but when I enter the formula's you have in the calendar portion where it says "DateOfFirst-FirstWeekDay" I can't figure out how you did that. Are you able to help?
I would like to use Excel to solve the following problem for a community work. I want to create a Driver schedule for a given month from a pool of volunteers for a community service. Each of these volunteers can drive only on specific days in a week. I would like to populate the driving schedule for each weekday with primary, secondary and tertiary drivers in a random fashion so that I do not overburden one person. I would greatly any help you can provide.
Hi chandoo,
Thanks for your valuable effort for create this template and let me know how to add multiple employees in the the Roaster.
Hi Chandoo,
This article on shift roaster is very helpful. Could you please let me know how i can use the same for n number of resources who work 24/7, considering their leaves and holidays?
Thanks,
Savitha
Hi Chandoo,
This article on shift roaster is very helpful to all. Could you please let me know how i can use the same if I want to add for some more shifts, since the color is not getting change if I add more shifts like 4,5 etc.,
Thanks,
Murali
nice post
How can I change the date to 2017 under Shift Data worksheet.