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!!!

Excelling with Microsoft Excel 6: ‘If’ function

Just in case, you missed out on my earlier posts, you can check them out here:

  1. Excelling with Microsoft Excel: Self-learning series 1 (Shortcuts and sparklines)
  2. Excelling with Microsoft Excel: Self-learning series 2 (Accessibility options)
  3. Excelling with Microsoft Excel 3: Excel 2010 (Shortcuts and tips)
  4. Excelling with Microsoft Excel 4: Excel 2010 (Vlookup and formatting)
  5. Excelling with Microsoft Excel 5: Excel 2010 (Sparklines and Tables)
  6. Excelling with Microsoft Excel: VLOOKUP Illustration

Excel tips published on this blog are based on new information I come across in my online self-learn Excel 2010 course with Alison.

Excel tip for today: Use of the ‘if’ function in Excel

  • This is used to specify if a condition is true or false.

if formula

*Remember to use quotation marks if you want text to appear in a cell as displayed above. “True” and “False” are in quotation marks to display the text when the condition indicated (A2=B2) is or is not met.

Personally, I use it mostly to sort out my trial balance (TB) in the preparation of month end reports, it saves time in ensuring that the TB is balanced before proceeding with any form of analysis. See below for a quick pictorial presentation of what can be achieved with this excel function.

if function

*Take note of the “if” formula which I have circled in blue above*.

  • Lastly it can be used to determine if an expense is within or over budget.

if_budget

*Take note of the formula circled in red above.

Hope you picked up something new from this post and remember to share your views on this post. This will help improve future posts on my self-learning journey.

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!).

Cheers!

Excelling with Microsoft Excel 5: Excel 2010 (Sparklines and Tables)

I am almost through with the Alison online Excel 2010 course with four modules left to complete. Remaining modules are on macros, if functions, Microsoft Office security, working with dates in Excel and planning payments and savings in Excel.

If you have missed my earlier posts on tips I have come across in my Excel 2010 e-learning journey, you can check them out here:

Excel 2010 tips for today

SPARKLINES

  1. Sparklines make it easy to spot patterns and trends. It is especially useful for management reporting. I use sparklines for our monthly financial reporting at work mainly for the income statement and expenditure analysis section of the report. You should try it!

sparklines final 2
2. For column sparklines, to make it readable you can play around by making edits to the vertical and column axis.

column sparklines

Steps to make edits to the axis of column sparklines

  • Click on any area within the sparkline.
  • The design sparkline option would pop up to the far right of the displayed options.

design sparkline popup

  • Click on the design tab, then select axis. All options under the axis are available for you to explore to know the impact of each option on your sparkline.

axis

EXCEL TABLES

1. Always convert data to table to make it easier for excel to work with. Working with this option automates sections of your data for you which saves time plus makes additional options available. How can this be done?

Steps to convert data to table

  • Click on any cell within the data.
  • Click “Insert” and select “table”.
  • A window will pop up to enable you select the data range.
  • A table would be automatically created. Voila! You are done.

2. Removal of duplicates is an additional option available when you work with your data using tables as indicated above.

How to remove duplicates

  • Click on any area within the table.
  • The design option would pop up to the upper right of your sheet.
  • Click “Design”, then select remove duplicates. This would only be possible if your data is in a table format.

duplicates

 

Explore the options under the design tab in your free time.

That’s it for today, hope you picked up something new to apply for work and personal use.

You are welcome to share your tips and feedback with us.

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. 
//