Excel 2016 Construction Kit #1: Calendar and Year Planner

Excel 2016 Construction Kit #1: Calendar and Year Planner

by Mke Smart


Choose Expedited Shipping at checkout for guaranteed delivery by Wednesday, April 15


This book is suitable for both Excel beginners and seasoned experts.

In this Excel 2016 book you’ll learn how to use and apply advanced Excel skills to construct a robust Excel business application that would be well beyond the powers of most advanced Excel users.

Even if you only have very basic Excel skills, the book is designed in such a way that you’ll be able to construct a complex, polished professional Excel 2016 application. In constructing this project you’ll discover new and interesting ways to use many of Excel 2016’s more powerful and complex features.

This book will teach you best-practice when applying your Excel skills to large real-world projects.

This book won’t only teach you Excel skills. You’ll also learn a best-practice design and development methodology that will stand you in good stead when working on future Excel projects.

What you will learn

Most Excel tutorials and reference books teach how to use individual Excel features but don’t show how you can combine them into a robust and complex Excel application. In this book you will combine 80 key Excel skills to produce a single advanced Excel application. Here are just a few of the skills you’ll use:

  • Create a custom color set.
  • Use custom formats.
  • Use Spin Button and Check Box form controls.
  • Define named ranges and named cells.
  • Use the Name Manager to view, delete and edit range names.
  • Create a drop-down list using a list validation.
  • Use and understand the practical use of over 20 Excel functions including VLOOKUP, COUNTIF, DAY, MONTH, DATE, IFERROR, CHAR, IF, IFERROR, LEN, MOD, ROW, TEXT and WEEKDAY.
  • Understand date serial numbers.
  • Understand precedence rules.
  • Format date serial numbers using a custom format.
  • Create a formula-driven conditional format.
  • Use the conditional format Rules Manager.
  • Calculate the correct date (in any year) for Martin Luther King Day, President’s Day, Labor Day, Columbus Day, Thanksgiving Day, New Year’s Day, Independence Day, Christmas Day and Veterans Day, Easter Sunday and Memorial Day.
  • Use structured table references.
  • Concatenate text.
  • Calculate the phases of the moon using Synodic Months and the MOD function.
  • Create user-friendly validation error messages.
  • Use Smart Tags.
  • Lock cells, hide columns, hide entire worksheets and protect worksheets to prevent users from making unwanted changes.

Companies who have taken Smart Method courses include:

AOL Time Warner, The United States Army, Daimler Chrysler, Motorola, HSBC, Barclays, American Express, Allied Irish Banks, Imperial Tobacco,  Volvo, The BBC, British Petroleum, The Foreign and Commonwealth Office, Unilever, The Institute of Chartered Accountants, The Ministry of Defence,  Keele University, Deutsche Bank, HBOS, Transport For London, The Performing Rights Society, Scottish Power, The Office of the Parliamentary Ombudsman, BAE Systems, RBS,  The British Museum, The National Gallery,  Pokerstars, Marks & Spencer, Virgin, O2, BMW... and many, many others.

Product Details

ISBN-13: 9781909253100
Publisher: Smart Method Enterprises Ltd
Publication date: 01/17/2018
Pages: 160
Product dimensions: 8.27(w) x 11.69(h) x 0.34(d)

Table of Contents

Session One: Methodology & Functional Specification

Lesson 1 1: Understand the Waterfall Model

Lesson 1 2: Understand the structure of a functional specification

Lesson 1 3: The Functional Specification

Session Two: Create the User Interface

Session Objectives

Lesson 2 1: Check that your Excel version is up to date

Lesson 2 2: Design the user interface

Lesson 2 3: Apply background colors

Lesson 2 4: Apply borders

Lesson 2 5: Resize rows and columns

Lesson 2 6: Add test values

Lesson 2 7: Apply text formatting

Lesson 2 8: Merge cells

Lesson 2 9: Add moon phase symbols

Lesson 2 10: Add a company logo

Lesson 2 11: Add controls

Lesson 2 12: Create a table for non-recurring custom events

Lesson 2 13: Create a table for recurring custom events

Lesson 2 14: Package the user interface for review by users

Session Three: Make the calendar functional

Session Objectives

Lesson 3 1: Create placeholders for calendar control settings

Lesson 3 2: Define range names for the calendar control settings

Lesson 3 3: Make the first day of week selector functional

Lesson 3 4: Set control cell links

Lesson 3 5: Create helper cells to determine the calendar start date

Lesson 3 6: Create formulas to determine the calendar start date

Lesson 3 7: Create formulas to display each date on the calendar

Lesson 3 8: Create formulas to display the days of the week

Lesson 3 9: Add conditional formatting to the day numbers

Lesson 3 10: Add conditional formatting for weekends

Lesson 3 11: Add conditional formatting for the moon phases

Session Four: Implement fixed events

Session Objectives

Lesson 4 1: Create a table for fixed events

Lesson 4 2: Link the table of fixed events to the calendar

Lesson 4 3: Add fixed-date national holidays

Lesson 4 4: Find the first named day in a month

Lesson 4 5: Calculate dates for common US public holidays

Lesson 4 6: Find the last named day in a month

Lesson 4 7: Calculate the date for Memorial Day

Lesson 4 8: Calculate the date for Easter Sunday

Lesson 4 9: Connect Public Holidays to the FixedEvents table

Session Five: Implement custom events

Session Objectives

Lesson 5 1: Link non-recurring custom events to the calendar

Lesson 5 2: Prevent two non-recurring custom events from occurring on the same day

Lesson 5 3: Set up recurring custom events

Lesson 5 4: Link recurring custom events to the calendar

Lesson 5 5: Prevent two recurring custom events from occurring on the same day

Lesson 5 6: Restrict the length of custom events

Session Six: Implement moon phases

Session Objectives

Lesson 6 1: Create a worksheet for moon phases

Lesson 6 2: Calculate the moon’s age

Lesson 6 3: Calculate the phase of the moon based on the moon’s age

Lesson 6 4: Link the moon phases to the calendar

Session Seven: Finish the user interface

Session Objectives

Lesson 7 1: Create user-friendly validation messages

Lesson 7 2: Hide error warnings and unlock cells

Lesson 7 3: Hide settings and apply protection

Lesson 7 4: Remove test data and finalize the year planner

Customer Reviews