professional diploma in excel keep organised
Post on 14-Jan-2022
5 Views
Preview:
TRANSCRIPT
1 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Keep OrganisedLesson 4: Summary Notes
Professional Diploma in Excel
2 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
3
4
7
9
12
Contents
Lesson 4 Objectives
Introduction
It’s a date
Referencing
Organising data
Additional resources
References
3 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
• Use 3 functions relating to dates (NOW, TODAY, DATE)
• Use the ‘Insert function’ wizard to create a function
• Format dates
• Understand the difference between relative and absolute
referencing
• Use mixed referencing
• Sort data using basic sorting options
• Filter data using basic filtering options
By the end of this lesson students will be able to:
The practical outcome of this lesson is to organise and
analyse data using filtering and sorting techniques, use
referencing and basic date functions.
Lesson 4 Objectives
IntroductionWorking towards a goal tends to keep one motivated when embarking on a journey. To achieve your desired goal, you need to think about the steps required to get there. The process of thinking about and documenting these steps is known as planning. Having a solid plan in place keeps you organised and focused. Humans have 60 000 to 80 000 thoughts a day on average.Organising your data does not need to be one of them as Excel makes this job a breeze!
Did you know?
Excel skills stand you in good stead in the workplace. But have you ever thought about how it can boost your career? Excel is not only for the finance departments in an organisation. Over 27 million vacancies across multiple recruitment sites will show that there is an increase in the need for employees with killer spreadsheet abilities. According to a study by Capital One and Burning Glass Technologies, the probability of a promotion or a rise in the figures in your bank account increases by 12% on average if you are a certified Excel user. (Mapue, 2020)
4 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Formula bar
It’s a date
About the Formula Bar
• The formula bar can be found below the ribbon• In the View tab you can hide or show the formula bar• Next to the formula bar is a button with the letters • The command opens the Insert Function dialogue box, which is a directory where you can search for functions• The command acts as a wizard for entering functions
Entering Data
There are three ways to enter data using the bar: • You can enter data directly into the active cell and a copy of this is reflected in the formula bar as mentioned in
Lesson 1• You can enter directly in the formula bar and a copy is reflected in the active cell as mentioned in Lesson 1• You can use the command button that launches the Function Arguments dialogue box
Benefits of Insert Function
1. Once you have selected a function the next dialogue box is called Function Arguments2. You can check the inputs of a function for accuracy. This decreases the risk of human error.
5 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
3. You can obtain information about the function to ensure you are selecting the correct one4. Guidance is provided for each argument entered5. You can check the outputs of a function for accuracy. This decreases the risk of human error6. There is a link to the Microsoft support website if you need help on the function
Date functions
• Dates are very fussy and need to be in a specific format to work• Once in the correct format they become very powerful and you can use them in more complex formulas• Dates are great for date stamping your work • A static function is where the date does not change• A live function is where the result of the function updates over time • The live functions are useful in more complex formulas to create tools such as countdown timers
DATE function
• This is a static function• It displays the values entered as arguments in the
function as a date• The format of the date can be changed using Home
> Number • Syntax: =DATE(year, month, day)
6 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
TODAY function
• This is a volatile (live) function• It displays the current date• It updates daily• The format of the date can be changed using Home
> Number • Syntax: =TODAY()
NOW function
• This is a volatile (live) function• It displays the current date and time• The seconds update with every change made to
the spreadsheet• The format of the date can be changed using Home
> Number • Syntax: =NOW()
Keyboard shortcuts
To enter the current static date: Ctrl + ;To enter the current static time: Ctrl + Shift + :
Did you know?
• The first date recognised by Excel is 01 Jan 1900.• It uses this as a starting point and counts the numbers of days that have elapsed since then to work out dates in
the date functions mentioned above.• Excel was developed after Lotus 1-2-3 (another spreadsheet programme) and adopted the same serial date system
for compatibility reasons.
7 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Referencingxxx
Absolute vs Relative referencing
Mixed referencing
Absolute referencing
• A picture says a thousand words and you can think of an absolute reference as a brick wall• The result of a function with absolute referencing never updates as the full reference is locked
• Mixed referencing is a combination of the above• You can lock the row reference or the column reference • The result of a function with mixed referencing updates depending on the direction the data is copied
Relative referencing
• Relative references change like the leaves blow relative to the direction of the wind• The result of a function with no referencing updates relative to the direction the data is copied
8 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Locking
• When we speak about absolute or mixed referencing, we refer to it as the act of locking cells• The symbol to lock cells is the $ (dollar) symbol• The cell address is made up of two parts being a column and a row number• In relative referencing, neither the column nor row is locked• In mixed referencing, either the column or row is locked• In absolute referencing, both the column and row references are locked
Note: For a refresher on linking cells refer to Lesson 2.
NOTES
9 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Organising data
• Like sorting jellybeans into the different colours, you can sort your data• You can sort your text in ascending or descending order • Numbers from lowest to highest • Dates in chronological order• You can even sort your data by colour (if you wanted to log your jellybean collection)• You can customise your sorting options, but this will be covered in Lesson 8
Sorting
10 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
• Filtering is very similar to sorting and you can add a filter to one column or the entire table• The types of filters and the ability to customise your filter are also very similar to sorting
• You can filter by ticking or unticking the relevant items using the checkbox• You can tick or untick multiple items• You can filter by typing the item in the search bar• You can expand the filter drop-down menu by hovering your mouse over the bottom right of the menu window and
dragging. This will reveal more items in the filter box.
Filtering
Keyboard shortcut
To add or remove a filter: Ctrl + Shift + L
11 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
NOTES
• If a filter has been applied to your table or data, it will be indicated by an icon in the filter drop-down box
• Sorting organises your data in a specific way • Filtering will only show the relevant data based on the criteria you have specified
Difference between filtering and sorting
Did you know?
An interesting fact is that Ctrl + L creates a table, and so does Ctrl + T.
12 www.shawacademy.com
PROFESSIONAL DIPLOMA IN EXCEL
Additional Resources
Sortinghttps://support.office.com/en-us/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654
Filterhttps://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e
Absolute, relative, and mixed referenceshttps://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-DFEC08CD-AE65-4F56-839E-
5F0D8D0BACA9
References
Mapue, J., 2020. A complete breakdown of jobs that require Excel Skills. [Online]
Available at: https://www.goskills.com/Excel/Articles/Jobs-require-Excel
[Accessed 26 May 2020].
Microsoft, 2020. Support - Date systems. [Online]
Available at: https://support.microsoft.com/enus/office/datesystemsinexcele7fe716748a94b96bb535612a800b487
[Accessed 26 May 2020].
Microsoft, 2020. Support - Referencing. [Online]
Available at: https://support.office.com/enus/article/
switchbetweenrelativeabsoluteandmixedreferencesdfec08cdae654f56839e5f0d8d0baca9
[Accessed 26 May 2020].
Sasson, R., 2020. How many thoughts does you mind think in one hour?. [Online]
Available at: https://www.successconsciousness.com/blog/inner-peace/how-many-thoughts-does-your-mind-think-
in-one-hour/
[Accessed 08 June 2020].
top related