Excelling with Microsoft Excel 7: Excel 2010 ( Date formulas and shortcuts)

This is the final post of my Excel 2010 journey  with Alison.  Finally! Although I missed my target timeline of completion but the important thing is that I have completed and passed my test. You can check out the previous post on the “if” function.

Dates and formulas

This is useful when the task involves finding the days between two dates. That sounds straightforward, just subtract one from the other. It gets complicated when you are interested in finding the number of week or work days between two dates.

  1. To find the number of weekdays between two dates (=NETWORKDAYS)
WEEKDAYS

Formula requirements to determine the number of workdays between two dates

In the above scenario, I assumed there were no holidays. Results indicates that there are 261 working days in 2016. This formula natural excludes the weekend ie. Saturday and Sunday.

What of places like Dubai that have Friday and Saturday as their weekends? How can one determine the number of working days?

The formula to use is =NETWORKDAYS.INTL

workdays_dubai

Formula requirements to determine the number of workdays between two dates with custom weekends

Results of the above indicated 260 workdays in 2016 for workers in Dubai.

2. To determine the date after a specific number of workdays from a start date (=WORKDAY) or (=WORKDAY.INTL)

workday formula

The above results in an end date of 2 January 2017

workdays intl formula

The above results in an end date of 30 December 2016.

Excel shortcuts

  • ctrl+shift+$ = currency format
  • ctrl+shift +% = percentage format
  • ctrl+shift+L =filter
  • ctrl+ z = undo
  • ctrl +y = redo
  • ctrl+ o = open
  • ctrl +alt+ v = paste special
  • ctrl+ w = close
  • ctrl +f4 = exit
  • ctrl +shift+ (+) = insert a row or column
  • ctrl+ (-) = delete a row or column
  • shift+ f11= insert new worksheet
  • ctrl +shift+ ! = format number to 2 decimal places
  • ctrl +1 = opens format cells dialogue box
  • alt+ (displayed character) = Access keys  are easy to use and there is no need to memorise it

Hope you picked up something new from my entire Excel 2010 posts. Don’t forget to share your excel tips and tricks with us (E-mail omtsdigest@gmail.com and your tip might just be featured on this site!).

What would you want to study with me this time? Take a vote and that would be my next study topic on my self-learning journey. Best of luck!!!

Video

Excelling with Microsoft Excel: VLOOKUP Illustration

As promised in the previous post, I worked up a simple illustration on VLOOKUP (takes less than 3 minutes).

Take a quick look at the post before watching this video especially if you are new to this functionality.

I hope this clarifies all that was outlined in the post.

Cheers!!!

Excelling with Microsoft Excel 4: Excel 2010 (Vlookup and formatting)

This post is evident that we are making progress with the self-learning series on Microsoft Excel. I appreciate the positive feedback that I have been receiving so far. Currently I have completed 41% of the course so I presume we still have more new tips to learn together.  I hope that at least the previous posts have been beneficial to you as they have been for me.

The previous post was on utilising the F4 as a shortcut function and other Excel worksheet tips. If you are interested in taking the online e-learning course administered by Alison independently, you can check out the link here >>>Microsoft Excel 2010 course. These posts are essential for those who do not have the time to go through the entire course on their own.

Key learning points for today

  • Vlookup (Vertical lookup)

Vlookup helps look up the value in one column (value must always be in the first left column of the data) to find its corresponding value on the same row in another column. Personally this excel function has really saved me time and has assisted me vastly with the preparation of financial statements in excel, ie after I have successfully classified the account codes. The key thing is for both sets of information to have something in common, in my example it would be the account codes.

Function: =VLOOKUP (lookup_value, table_array, col_index_num,range_lookup)

Lookup value: This is usually the point of reference or the column with information unique to the data sets.

Table array: Table or data from which the retrieval is made.

Column index number: The column number from the table array from which retrieval is made. Always start the count from the lookup value column.

Range lookup: Choose ‘FALSE’ if an exact match is required and ‘TRUE’ when an approximation is needed but data must be sorted in ascending order (this is usually used for discount and data ranges)…….Would illustrate further in another post.

Don’t forget to use the insert function discussed in the previous post should you face any challenges. There is no need to memorise the function!

vlookup function

Vlookup function

Would be working on developing a vlookup illustration to make it easier to follow and it would be more practical than reading all this plenty grammar I have written.

  • Conditional formatting

When faced with presenting reports, it’s important to highlight relevant information. This does not mean we should go about colouring and shading our excel worksheets. The essence of conditional formatting is to make relevant data standout and not for beautification purposes.

How to access the conditional formatting tool bar

  • Go to Home
  • Select conditional formatting

conditional formatting bar

  • The following options would be displayed: highlight cell rules, top/bottom rules, data bars, colour scales, icon sets, and others.

Would illustrate a few and as I always encourage you to do, explore the other options on your own. Don’t forget to highlight the data range before selecting the conditional formatting style you are comfortable working with.

Data bars (shows the value of a cell relative to all other cells in the range)

Data bars

Colour scales 

The data bars is much easier to understand at a glance. It is all a matter of personal opinion.

Colour scales

Icon sets

Icon sets

You can always ‘clear rules’ from the conditional formatting menu whenever you prefer to make changes to a previous format. There are also optional rules to highlight key data such as top 10, values greater or less than a set value, etc. These can be accessed from the conditional formatting menu under ‘more rules’, ‘new rules’ or ‘manage rules’. Just play around the various options if you have the time.

All the best!

The next post would be on sparklines and excel tables and hopefully the vlookup illustration indicated in the previous paragraphs would have been completed by then.

Comment directly with your questions and corrections. Alternatively you can send them to omtsdigest@gmail.com. I will be happy to research and provide answers.

Disclaimer: I am not an expert. I choose to document, detail and share tips from my personal Excel 2010 self-learning journey with Alison with others. Knowledge is of better use when it is shared with others. 
//

Excelling with Microsoft Excel 3: Excel 2010 (Shortcuts and tips)

The key learning points I would be sharing with you today are on utilising the F4 as a shortcut function and other Excel worksheet tips. If you are interested in taking the online e-learning course administered by Alison independently, you can check out the link here >>>Microsoft Excel 2010 course.

My previous post was on Excel 2010: Accessibility options which explained how to make worksheets more user-friendly especially for the visually disabled. You can always check it out if you missed it.

Key learning points for today

  • Press F4 whenever you want to repeat the last action performed in Excel.

This saves time and there is no need to keep right clicking and going through the file menu options whenever you want an action repeated.

  • Printing worksheets with visible grid lines and headlines

This is for those who prefer to print their worksheets with all the row and column lines visible in the printout. This is nothing extraordinary but this set up option is hardly employed by users. Personally, I don’t even bother with the other options, I end up using the default print option. I guess you do too.

Here is how to access this option:

a) Press ‘Control P’ or select the print function from the file menu to go to the print view

b) Select ‘Page setup’ at the bottom of screen.

Page setup

c) Select ‘Sheet’ from the pop up window.

Page setup popup

d) Check ‘grid lines’ and ‘row and column headings’

There are other printing options such the option to display worksheet comments and how they should be displayed, cell errors, print areas and others. You can always explore them in your free time.

e) The below shows screenshots of the before and after view.

Without gridlines and headings

Without gridlines and headings

With gridlines and headlines

With gridlines and headlines

 

  • Place your cursor in the formula cell and press F4 whenever you want to change a relative cell reference to an absolute cell reference. Better than typing in the dollar sign manually to fix a cell reference.

 

  • Make use of the ‘insert function’ whenever you are stuck or unaware of a formula

a) Select formulas then click on ‘insert function’

insert function in excel

b) Type a brief description or key word in the search box and options would be made available. Voila! No need to memorise all those formulas and good thing is you can always check if a formula exists for what you seek to mathematically achieve.

Insert function popup

 

The next post would be on vlookup. For heads-up, Vlookup helps look up the value (the lookup value must always be in the first left column) in one column to find its corresponding value on the same row in another column. Sound confusing? I hope to break it down in the subsequent post.

Comment directly with your questions and corrections. Alternatively you can send them to omtsdigest@gmail.com. I will be happy to research and provide answers.

I am not an expert, just detailing and sharing some few tips from my personal Excel 2010 self-learning journey with Alison.


//

Excelling with Microsoft Excel: Self-learning series 2 (Accessibility options)

This is the 2nd of my self-learning series, the first one was on Shortcuts and sparklines.  If you are interested in taking the online e-learning independently, you can check out the link here >>>Microsoft Excel 2010 course, it is administered by Alison.

Key learning points for today

Accessibility options

The essence of this Excel functionality is to make your workbook or sheet user-friendly for all especially for the visually impaired. Just like me, am sure this does not often cross our mind. We always assume the final users would not encounter any challenges in navigating our completed spreadsheets.

Enough of the explanations, let me hit the nail on the head. I have painstakingly taken screenshots of the step by step procedure to guide anyone who would want to explore this Excel functionality.

a) Open your Excel worksheet, select Filethen click on Info.

Select check for issues.

image

b) Three sub-options would be displayed namely: Inspect document, check accessibility and check compatibility. For this session, select check accessibility.

image

c) Issues will be displayed at the right hand corner of your worksheet. Example of how its shown is reflected in the screenshot below. 

image

It would also include information oh how to fix these errors as illustrated below.image

After you successfully clear all the issues, you would finally have your user-friendly workbook. Kudos!

Hope you found this useful and learnt something new as I did today.

Comment directly with your questions or send them to omtsdigest@gmail.com. I will be happy to research and provide answers.

You can join me on this educational journey on twitter @mophie227 or on my blog


//