Excel power tips!

DanAtkinsonUKDanAtkinsonUK Member
edited March 2016 in Technology & IT
Excel is so powerful and is one of the most useful tools I use as a Paraplanner. Please share your top tips for getting the most out of it.
Chartered Financial Planner FPFS MCSI APP
Head of Technical at EQ Investors

Twitter: https://twitter.com/danatkinsonuk
Instagram: https://www.instagram.com/danatkinsonuk/

Comments

  • I'll start us off! I found out last week that pressing Ctrl+PgUp / Ctrl+PgDown lets you flick between sheets in a workbook
    Chartered Financial Planner FPFS MCSI APP
    Head of Technical at EQ Investors

    Twitter: https://twitter.com/danatkinsonuk
    Instagram: https://www.instagram.com/danatkinsonuk/
  • The sumproduct formula is one of the most useful and yet underused paraplanner formula in excel. 

    Used with a column of fund values and a column of OCF/TER it will give you the weighted OCF/TER. No third column required.

    I have seen so many portfolio charge sheets that are made more complex and open to inaccuracies by not using sumproduct.

    I'm not exaggerating, if you often have to calculate weighted portfolio charges and you don't currently use sumproduct, it will be like finding the holy grail! 
  • richallumrichallum Administrator
    We've had lots of requests for a Powwow on Excel.  We've got Judith from the main Powwow 2015 lined up for a half day but it would be really good if some paraplanners would be happy doing an online one with top tips etc.  Any volunteers? 

    Paraplanner. F1, Apple, Nutella, ice cream. No trite motivational quotes. Turning a bit northern. Republican.

  • I'll start us off! I found out last week that pressing Ctrl+PgUp / Ctrl+PgDown lets you flick between sheets in a workbook
    Genius! This usually drives me round the bend. To paraphrase an old BT advert, "its just possible you've saved my life" or at least the life of my PC :smiley: 
  • Similar to Dan's first tip, if you use CTRL+ any of the arrow keys, it will take you to the beginning/end of a row/column of data. Handy for zippy around a sheet without scrolling  ;)
  • I love the =Today() formula, this puts in the current date, useful for checking time to retirement.
  • Dan's tip also applies to tabbed browsers  :)
  • One of my tips is to remember in excel that a year is 365.25! Stop your dates from drifting...
  • Nathan said:
    I love the =Today() formula, this puts in the current date, useful for checking time to retirement.
    FYI Nathan, Ctl with : also puts in today's date :)
  • Where to start?

    When comparing between options for a client (maybe choice of platforms based on price, or choice of income routes when doing cashflow) Nested IF statements are quite incredible once you have the hang of them :) I know they're the bane of most Excel users lives though...
    Combine them with TRUE/FALSE statements, or radio buttons and you can build very useful toolsets!

    As I think has been already mentioned, Sum product is also a godsend and is recommended.

    Late to the party but looking to launch a new platform due diligence tool and consultancy business later this year - watch this space and feel free to message if you would like any initial info.

  • The sumproduct formula is one of the most useful and yet underused paraplanner formula in excel. 

    Used with a column of fund values and a column of OCF/TER it will give you the weighted OCF/TER. No third column required.

    I have seen so many portfolio charge sheets that are made more complex and open to inaccuracies by not using sumproduct.

    I'm not exaggerating, if you often have to calculate weighted portfolio charges and you don't currently use sumproduct, it will be like finding the holy grail! 

    Benjamin - can you explain how sumproduct formula works -  I need this in my life!
  • amarshall said:
    Similar to Dan's first tip, if you use CTRL+ any of the arrow keys, it will take you to the beginning/end of a row/column of data. Handy for zippy around a sheet without scrolling  ;)

    one of my favourites here and if you combine that with using the shift key (in other words, Ctrl +Shift +Arrow, it will select/ highlight the entire row, column etc.  I use this to select large areas of data but be warned, it sometimes doesn't jump past blank cells.
  • Here's one that I find very useful XIRR - it calculates the internal rate of return for a cashflow where dates of inflows and outflows are irregular.  Say you wanted to work out what  annual return has been achieved over the life of an investment which has had irregular withdrawals, this function would work it out.  

    This article explains it in detail https://support.office.com/en-us/article/XIRR-function-de1242ec-6477-445b-b11b-a303ad9adc9d
    Outsourced paraplanner for The Paraplanners.  President of the Scottish Petanque Association
  • Claire Goodwin Claire Goodwin Administrator
    This is a bit old but there are some shortcuts on here: https://excelzoom.com/excel-shortcut-keys/
  • NathanNathan Member
    Discovered PMT function yesterday, can be used to calculate a sustainable drawdown given an estimated annual growth rate, number of years and the starting amount. 

    Love it
  • Claire Goodwin Claire Goodwin Administrator
    I like the COUNTIF function to count how many times things occur in a list. https://support.office.com/en-us/article/COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34
Sign In or Register to comment.