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.