Data Input & Hidden Features to Save Time
FINAL FILE
Link Topic Comprehension
Data Entry Data Entry, Simple Calculations & Select One…
Hidden Feature
Date Inputting Dates & Time Stamps Select One…
AutoFill Use Autofill To Do Your Work Select One…
FlashFill Flash Fill For Major Time Saving Select One…
(Become Pro)
Custom List Custom Lists With Autofill (Hidden Select One…
Feature)
Comments Inputting & Editing Comments & Select One…
Notes
FormulaCells Finding Formula & Input Cells with Select One…
ONE Click (Become Pro)
DataValidation Data Validation to Reduce Time Select One…
Checking Data
Screentip Adding Tooltips (ScreenTips in Excel) Select One…
Challenge Challenge: Data Manipulation & Select One…
Preparation
Your Notes
Data Entry, Simple Calculations & Hidden Feature
Clothing Items
by type Jan Feb Total % change
Scarf-M 100 110 210 10%
Scarf-W 150 120 270 -20%
Shirt-W 1,000 1,200 2,200 20%
Shirt-M 160 180 340 13%
Total 1,410 1,610 3,020
Inputting Dates & Time Stamps
Input Date 1/2/2019
Time Stamp (Date) 9/4/2019
Become Pro - Select the cell and
Time Stamp (Date & press Ctrl + # to convert the date
Time) 9/4/2019 14:28 to DD-MMM-YY format (this
makes it easier to read).
To insert a time stamp for date and time: Press Ctrl + ; then For inputting year:
space and then Ctrl + Shift + ; 0 to 29 = this century
30-99 = last century
If Ctrl + Shift + ; doesn't work for you, try Ctrl + shift + : To type a date before 1/1/1930, yo
type the 4-digit year.
tting year:
this century
ast century
date before 1/1/1930, you need to
4-digit year.
AutoFill to Save Time
Text Number Months Weekday Date
Scarf 1 Jan Monday 7/20/2019
Scarf 2 Feb Tuesday 8/20/2019
Scarf 3 Mar Wednesday 9/20/2019
Scarf 4 Apr Thursday 10/20/2019
Scarf 5 May Friday 11/20/2019
Scarf 6 Jun Monday 12/20/2019
Scarf 7 Jul Tuesday 1/20/2020
Fill Weekday Fill Months
option was option was
selected selected
Flash Fill - Speedup Data Entry
The data below has been filled using flash fill.
Name First Name Last Name Name
Gary Miller Gary Miller Gary Miller
James Willard James Willard James Willard
Richard Elliot Richard Elliot Richard Elliot
Robert Spear Robert Spear Robert Spear
Roger Mun Roger Mun Roger Mun
Paul Garza Paul Garza Paul Garza
Robert Marquez Robert Marquez Robert Marquez
Natalie Porter Natalie Porter Natalie Porter
Kim West Kim West Kim West
First Name Last Name Name Product
Gary Miller Gary Miller Watch-W
James Willard James Willard Scarf-M
Richard Elliot Richard Elliot Shirt-M
Robert Spear Robert Spear Shirt-M
Roger Mun Roger Mun Scarf-W
Paul Garza Paul Garza Watch-M
Robert Marquez Robert Marquez Scarf-W
Natalie Porter Natalie Porter Shirt-W
Kim West Kim West Scarf-W
Sometimes for flash fill to r
pattern, you need to give it
inputting data for more tha
Email Name Proper Case
Gary.Miller@XelPlus.com gary miller Gary Miller
James.Willard@XelPlus.com James willard James Willard
Richard.Elliot@XelPlus.com Richard elliot Richard Elliot
Robert.Spear@XelPlus.com Robert Spear Robert Spear
Roger.Mun@XelPlus.com Roger Mun Roger Mun
Paul.Garza@XelPlus.com paul Garza Paul Garza
Robert.Marquez@XelPlus.com Robert marquez Robert Marquez
Natalie.Porter@XelPlus.com natalie porter Natalie Porter
Kim.West@XelPlus.com Kim west Kim West
Woman / Man Date Year
W 2/23/2019 2019
M 4/12/2019 2019
M 8/23/2019 2019
M 5/6/2020 2020
W 3/4/2020 2020
M 3/24/2019 2019
W 2/8/2021 2021
W 7/21/2020 2020
W 10/28/2020 2020
Sometimes for flash fill to recognize a
pattern, you need to give it more info by
inputting data for more than 1 cell.
Name Initials
Gary Miller GM
James Willard JW
Richard Elliot RE
Robert Junior Spear RJS
Roger Mun RM
Paul Garza PG
Robert Marquez RM
Natalie Porter NP
Kim Sophie West KSW
Custom Lists with Autofill
Region
North America
Good to Know: As custom lists are
South America stored in your computer's registry, you
Asia need to add the list to be able to use
Europe it.
Australia
Here's How: Add the region list to a custom list:
Go to File / Options / Advanced / Edit Custom
Lists. Click in the Import box and reference the
cells under Region.
Open a new workbook (press Control + N). Input
North America in the first cell and pull down the
fill handle. What do you notice?
Inputting & Editing Comments & Notes
Excel Notes
Region Sales
North America 2500 Good to Know: Excel Notes in Office
South America 2300 365 are called Excel Comments in
stand-alone versions of Excel.
Asia 6200
Europe 9600
Australia 1800
Excel Comments (Available in Office 365)
Region Sales
North America 2500 Beware: If you don't have Office
South America 2300 365, you will not have the new
Asia 6200 comment feature - so you will not
Europe 9600 be able to respond to comments.
Australia 1800
Recognizing Formula & Input Cells
App by Segment / Revenue $
Games segment 2019 2020 2021 2022
Pes 41,722 46,358 45,316 46,222
Baden 37,023 35,260 35,980 34,901
Twistrr 26,626 28,027 27,211 27,483
Games Other 96,101 104,458 102,109 103,641
Total 201,473
### 214,104
### 210,616
### 212,247
Productivity segment
Blend 18,206 16,551 17,990 18,170
Pet Feed 18,255 18,074 16,735 16,735
Mirrrr 15,008 15,314 15,627 14,689
Productivity Other 115,682 113,414 112,291 110,045
Total 167,151
### 163,353
### 162,643
### 159,639
Formula cells are
highlighted
Shortcut for Go-to Special is
Control + G - then click on
"Special" or Alt + S
Basic Data Validation
Please input the following information for your team
Name Employee ID Segment Start Date BEFORE
Kim West 250 Utility 10/18/2015
Stevie Bridge 365 Game 18/8/2018
Roger Mun 985 Productvity 8/16/2018
Paul Hill 158 Games 12/3/2017
Version with Data Validation
AFTER
Name Employee ID Segment Start Date
Kim West 250 Productivity 10/18/2015
Data validation ensures users
Stevie Bridge 365 Game 8/8/2018 don't make mistakes when
Roger Mun 985 Productivity 8/16/2018 filling out templates or
Paul Hill 158 Game 12/3/2017 inputting data.
Adding Tooltips (ScreenTips in Excel)
My Name
My address Line 1
My address Line 2
My address Line 3
Invoice 15076
Date 3/5/2019
Payment terms 14 days net
Company Name
Client Name
Address
Price in
Services Time Period
USD
Subtotal $ -
VAT 20%
Total $ -
Bank Info
Bank Name
IBAN: 2342456435
BIC: GIBARESCXX
Segment Master data (For DataValidation tab)
Segment
Game
Productivity
Utility
Challenge: Data Manipulation & Preparation
You receive the below information for sales agents - you need to do the following:
1 Create a login user id that consists of the last name followed by the first character of the first name
2 Add the start year to column D
3 Add a note (legacy comment) to A9 "Please input first name, followed by last name"
4 Add a screen tip for C9 that describes the logic behind creating the system login
5 Add data validation for start date to ensure the date is input correctly - apply this from B10 to B50. Ensu
Sales Agent Start Date System Login Start Year
Stevie Bridge 12/11/2013 BridgeS 2013
Ewan Thompson 7/18/2012 ThompsonE 2012
Richard Elliot 1/5/2015 ElliotR 2015
Paul Hill 2/4/2016 HillP 2016
Paul Garza 10/18/2015 GarzaP 2015
Robert Musser 5/5/2018 MusserR 2018
Betina Bauer 10/6/2011 BauerB 2011
Peter Ramsy 1/11/2017 RamsyP 2017
Brigitte Bond 10/19/2015 BondB 2015
Gary Miller 3/27/2013 MillerG 2013
James Willard 11/19/2013 WillardJ 2013
Robert Spear 6/23/2011 SpearR 2011
Roger Mun 7/13/2012 MunR 2012
Robert Marquez 1/10/2013 MarquezR 2013
Natalie Porter 12/22/2014 PorterN 2014
Kim West 1/6/2018 WestK 2018
Andre Cooper 8/16/2011 CooperA 2011
Crystal Doyle 3/28/2013 DoyleC 2013
Daniel Garrett 12/12/2014 GarrettD 2014
Ann Withers 4/19/2018 WithersA 2018
Corinna Schmidt 3/21/2015 SchmidtC 2015
Walter Miller 1/23/2013 MillerW 2013
Paul Wells 12/6/2018 WellsP 2018
Daniela Schreiber 3/23/2014 SchreiberD 2014
Dan Ziegler 1/23/2012 ZieglerD 2012
Wolfgang Ramjac 7/27/2014 RamjacW 2014
Robert Richardson 10/24/2012 RichardsonR 2012
Robert Blume 1/21/2018 BlumeR 2018
Mike Saban 2/8/2011 SabanM 2011
Maria Tot 11/2/2015 TotM 2015
Lukas Hofer 2/8/2014 HoferL 2014
haracter of the first name
✘
1. Done
✘
2. Done
✘ 3. Done WELL DONE!
pply this from B10 to B50. Ensure 4.itDone
✘
works correctly if new dates are added.
✘
5. Done
