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.
|Publisher:||Smart Method Enterprises Ltd|
|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
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
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
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
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
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
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