41
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 1 5
74.
SUBTOTAL – most people don͛t know the power of SU TOTAL, let alone that it exists.
http://www.myonlinetraininghub.com/excel-2007-subtotal-formula-explained
75.
ROUND, ROUNDUP, ROUNDDOWN – efore long you͛ll need to round your numbers with
more accuracy than simply formatting the font.
http://www.myonlinetraininghub.com/how-to-round-numbers-in-excel-using-round-
formulas
76.
FV Calculate compound interest on savings – Want to save some money for a rainy day
and curious to know what it͛ll be worth? Excel can tell you:
http://www.myonlinetraininghub.com/how-to-calculate-interest-on-savings-in-excel
77.
IFERROR - Once you start using some of the formulas above you͛re likely to have errors
returned. If you͛re expecting errors and want to hide them you can use the IFERROR
formula (Excel 2007+ only).
http://www.myonlinetraininghub.com/excel-2007%e2%80%99s-iferror-puts-an-end-to-
messy-workarounds
78.
MIN, MAX, SMALL and LARGE – These may seem straight forward, but I͛ll show you a few
tricks that you won͛t find in the Excel Help Files.
http://www.myonlinetraininghub.com/excel-min-max-small-and-large-functions
79.
UPPER, LOWER and PROPER – Fix text that isn͛t formatted as you want. hange all
CAPITALS to all Lower, or make the first letter in each word a capital (PROPER).
http://www.myonlinetraininghub.com/excel-upper-lower-and-proper-functions
80.
TRIM, CHAR and SUBSTITUTE – Get rid of extra spaces in your text and SUBSTITUTE text
with something else.
http://www.myonlinetraininghub.com/excel-trim-function-removes-spaces-from-text
81.
Time Calculations – Most people at some stage or another need to calculate time in Excel,
but if you don͛t understand how Excel treats time you͛ll find it infuriating!
http://www.myonlinetraininghub.com/calculating-time-in-excel
82.
NOW() and TODAY() – simply type =NOW() in a cell and you will get the date and time as
per your computer clock or =TODAY() will give you the date only. Handy for date/time
stamping your printed reports.
39
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 1 6
Power Formulas
83.
INDEX and MATCH – With these powerful formulas combined you can get around the
limitations of VLOOKUP and look up columns to the left.
http://www.myonlinetraininghub.com/excel-index-and-match-functions
84.
CHOOSE – This function isn͛t much use on its own but it͛s powerful when you use it with
other formulas, like the VLOOKUP.
http://www.myonlinetraininghub.com/excel-choose-function
85.
VLOOKUP with CHOOSE – An alternative to INDEX and MATCH that enables you to get
around VLOOKUP not allowing you to lookup columns to the left.
http://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose
86.
OFFSET – Tired of having to update your totals to incorporate new rows? This is one great
feature of the OFFSET function, but it has many more uses.
http://www.myonlinetraininghub.com/excel-offset-function-explained
87.
FLOOR and CEILING – If calculating prices is something you do in Excel and you want to
round them to end in 99 cents or 97 cents then the FLOOR function is your best friend.
http://www.myonlinetraininghub.com/excel-ceiling-and-floor-functions
88.
RAND and RANDBETWEEN – Generating random data is something I do regularly as I need
to create data to support the tutorials I write, but you could also use it in your work to do
the same, or team it with CHOOSE and use it to choose random values from a list. Read the
tutorial for more examples:
http://www.myonlinetraininghub.com/excel-rand-and-randbetween-functions
89.
SUMPRODUCT – This is a great alternative to SUMIFS, COUNTIFS and AVERAGEIFS for
those Excel users still stuck with 2003. If you͛re an Excel 2007 or 2010 user SUMPRODU T
is one formula that will take you to the heady heights Excel Guru.
http://www.myonlinetraininghub.com/excel-sumproduct-an-alternative-to-sumifs
90.
Array Formulas – Similar to SUMPRODUCT, but you can use some of the existing functions
in an array formula to increase their capability. These are for people serious about getting
the most out of Excel. http://www.myonlinetraininghub.com/excel-array-formula
42
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 1 7
Cool Tools
91.
Shapes and SmartArt – In Excel 2007+ the sophistication of Shapes and
SmartArt can give your workbooks and reports a truly professional finish.
Don͛t be surprised if people think you got a graphic designer involved.
http://www.myonlinetraininghub.com/microsoft-excel-shapes-smartart
92.
Camera Tool – You won͛t find any mention of the camera tool in a
standard Excel course. After all you won͛t even find it in the tool bar or
ribbon. It͛s handy if you only have one monitor and pine for two, or if you want to create
dashboards with lots of small charts. http://www.myonlinetraininghub.com/microsoft-
excel-camera-tool
93.
Conditional Formatting – In Excel 2007+
Conditional Formatting is substantially improved.
Use it to bring life to your drab data, make
highlighting duplicates instant and much more.
http://www.myonlinetraininghub.com/how-to-
use-excel-2007%E2%80%99s-conditional-
formatting
94.
Filters – If you work with large amounts of data then Filters can give you instant data
mining abilities. You can also use them in conjunction with Conditional
Formatting. http://www.myonlinetraininghub.com/how-to-use-filters-in-excel-
2007
95.
Drop Down Lists – or Data Validation as it͛s called in Excel. If you build forms or reports for
other͛s to use Data Validation can make them more interactive and reduce the chances of
the wrong data being filled out.
http://www.myonlinetraininghub.com/excel-drop-down-lists
96.
Insert Subtotals – unlike the SUBTOTAL function mentioned earlier, this tool actually
identifies changes in your data and inserts subtotals where you want.
http://www.myonlinetraininghub.com/how-to-insert-subtotals-in-excel
97.
Outlines – The subtotal tool above inserts outlines automatically for you
when it inserts the subtotals, but you can insert outlines yourself using
the Group and Ungroup tools. I love using these instead of hiding columns
and rows as it allows me to hide and unhide again at the click of only one button.
14
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 1 8
http://www.myonlinetraininghub.com/excel-2007-group-and-outline-data
98.
Importing Data Into Excel – You can import data from the Web, an Access Database, text
or SV files and more. Once you create the link to the data you͛re importing you can
update it as frequently as every minute, to every time you open the workbook.
http://www.myonlinetraininghub.com/importing-data-into-excel
99.
Excel Tables – In Excel 2007 tables are vastly improved. Formatting your data in a Table
allows you to add to the content and have any formulas or pivot tables that reference the
data automatically pick up the new cells. Plus there͛s a range of other benefits like great
predefined formatting and more. http://www.myonlinetraininghub.com/excel-2007-tables
8
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 1 9
100.
PivotTables – A lot of people are afraid of PivotTables, but they really are quite simple
once you understand the basics. If you work with large amounts of data PivotTables can
create dynamic reports in seconds from tens of thousands of rows.
http://www.myonlinetraininghub.com/pivot-tables-explained-excel-2007
34
My O n l i n e T r a i n i n g H u b
ht t p : / / w w w . M y O n l i n e T r a i n i n g H u b . c o m
Pa ge 2 0
Thank you for taking the time to read these Tips & Tricks. Enjoy using your
new Excel powers. Feel free to email me with feedback or ideas.
Kind regards,
Mynda.
Now that you’ve read the whole book please forward it to your friends and colleagues and share the
knowledge. Please just make sure you follow the conditions below.
You have permission to share this e-book via email, printed or even post it on your website,
Facebook account, Twitter or LinkedIn.
The only conditions are:
1.
You don͛t charge anyone money for it. That͛s my right.
2.
You don͛t change, edit, or alter the digital format or contents.
3.
All links must remain in place.
My hope for this e-book is that you please share it with as many people as possible, and by
sharing the knowledge many more people will love Excel and love their work.
Did you like these tips & tricks? Want more?
Video Training:
You can find more Microsoft Office training, including Excel, Word and Outlook, plus other
free software and resources at http://www.MyOnlineTrainingHub.com
Sign up for our free video training and get instant access to over 150 video tutorials on Excel, Word and
Outlook.
Excel Newsletter: http://www.myonlinetraininghub.com/sign-up-for-100-excel-tips-and-tricks
Follow My Online Training Hub:
RSS Feed:
http://feeds.feedburner.com/MyOnlineTrainingHub/feedme
Facebook:
http://www.facebook.com/pages/My-Online-Training-Hub/143694365645900
Twitter:
http://twitter.com/onlinetrainingh
Documents you may be interested
Documents you may be interested
