›

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.
Dan Atkinson FPFS CFP APP Chartered FCSI
Chartered Financial Planner
Certified Financial Planner
Head of Technical at Paradigm Norton

Twitter: https://twitter.com/danatkinsonuk
Instagram: https://www.instagram.com/danatkinsonuk/
Share on Google+

Comments

  • DanAtkinsonUKDanAtkinsonUK Member
    I'll start us off! I found out last week that pressing Ctrl+PgUp / Ctrl+PgDown lets you flick between sheets in a workbook
    Dan Atkinson FPFS CFP APP Chartered FCSI
    Chartered Financial Planner
    Certified Financial Planner
    Head of Technical at Paradigm Norton

    Twitter: https://twitter.com/danatkinsonuk
    Instagram: https://www.instagram.com/danatkinsonuk/
    Share on Google+
  • benjaminfabibenjaminfabi Moderator
    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 Fabi 
    Share on Google+
  • 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. 

    Share on Google+
  • amarshallamarshall Member, Moderator
    DanAtkinsonUK said:
    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: 
    Share on Google+
  • amarshallamarshall Member, Moderator
    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  ;)
    Share on Google+
  • NathanNathan Member
    I love the =Today() formula, this puts in the current date, useful for checking time to retirement.
    Share on Google+
  • benjaminfabibenjaminfabi Moderator
    Dan's tip also applies to tabbed browsers  :)
    Benjamin Fabi 
    Share on Google+
  • JoCHagueJoCHague Member
    One of my tips is to remember in excel that a year is 365.25! Stop your dates from drifting...
    Share on Google+
  • PoppySheppardPoppySheppard Member
    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 :)
    Share on Google+
  • Jamie_BarnesJamie_Barnes Member

    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.

    Share on Google+
  • RoxyParaplannerRoxyParaplanner Member
    benjaminfabi said:
    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!
    Share on Google+
  • JudithJudith Member
    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.
    Share on Google+
  • Colinstewart76Colinstewart76 Member
    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
    Share on Google+
  • Claire Goodwin Claire Goodwin Administrator
    This is a bit old but there are some shortcuts on here: https://excelzoom.com/excel-shortcut-keys/
    Share on Google+
  • 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
    Share on Google+
  • 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
    Share on Google+
Sign In or Register to comment.