Advanced Tips and Tricks for Using Excel

    If you’re interested in learning how to use the more advanced features of Microsoft Excel then you’ve arrived at the starting line.

    Even though you probably already know how awesome this program is, allow me to refresh your memory, and if you don’t then here’s what you need to know to get up to speed.

    Why Should You Learn To Use Excel?

    Even with all of the technological and software leaps we’ve made in the past decade alone, there is yet to be any other system that can so easily sort, categorize and set out so many types of different data (and that can be used by absolutely anyone I might add) as Excel.

    So why should you learn how to use Excel properly?

    • Because it’s versatile and can be used across all walks of life.
    • It’s easy to use (with some training you could become a pro in no time)
    • Helps you sort data and information logically.
    • Can be used for anything from a wedding guest list to business accounting.
    • Anyone can easily learn how to use it.
    • A little bit of learning and tinkering can greatly improve your efficiency in excel and make it even simpler to use.

    Now for the how.

    Here are some tips and tricks to get you started:


    Here are some keyboard shortcuts that will certainly make your life easier if you are a frequent excel user.

    (Note that the + symbol indicates that you need to press those buttons together, not use the actual + symbol)

      • Ctrl + Shift + : will enter the current time to the selected cell.
    • Ctrl+Shift +; will enter the current date to the selected cell.
    • Ctrl + ‘ will copy the formula from the cell above.
    • Ctrl + Shift + = Insert a new column or row.
      • Ctrl + Shift + @ applies time formatting to the selected cell.
      • Ctrl + Shift +! Applies comma formatting to the selected cell.
      • Ctrl + Shift + $ Applies currency formatting to the selected cell.
      • Ctrl + Shift + # Applies date formatting to the selected cell.
      • Ctrl + Shift + % Applies percentage formatting to the selected cell.
      • Ctrl + Shift + ^ Applies exponential formatting to the selected cell.
    • Ctrl + Shift + & Puts a border around the selected cells.
      • Ctrl + Shift + { Selects all the cells that are used in the formula in the selected cell
      • Ctrl + Shift + } Selects all cells with a formula that has the selected cell in it.
      • Ctrl + Shift + | (pipe) selects the cells in the column that don’t have the same formula or statistical value as the selected cell.
    • Ctrl + Spacebar selects the entire column.
    • Ctrl + Shift + Spacebar selects the entire worksheet. You could also use the classic Ctrl + A for this.

    There are many more you can learn but these are my favorites.

    Drop Down List

    An extremely useful tool for recurring data with different values such as a collection of names of team members that need to be assigned to individual tasks.

    Or days of the week. Or a numeric value that is always in the same range such as a date.

    If you don’t know how to insert a drop-down list, now is the time to learn.

    Sorting Data

    If you regularly work with excel and a ton of data then I’m sure you know the frustration of trying to find something specific within all that data.

    Talk about finding a needle in a haystack, am I right?

    Excel’s data sorting function is gold.

    Go to the headers of the data you want to be sorted and select them, then go to the ‘Data’  tab in the top bar and select ‘Sort’

    You will then see there are options on how to sort your data based on Names (this option will sort your data alphabetically) or the numerical value (which will sort in ascending or descending numerical value)

    You can play around with this function so you can make it as functional as possible for your use


    A formula in excel a bit like a fancy built-in GPS in a car.

    You could get directions without it but why bother when it’s so freaking convenient?

    Formulas are how you calculate numbers in excel, whether it is sum, subtraction, multiplication or division, excel can do it all.

    A simple way to get started with formulas is to go to an empty cell where you want the answer of the equation to be and hit the ‘=’ button, then you can select a cell or manually type in your equation like you would on a calculator.

    • Use + for adding
    • – for subtracting
    • / for division
    • And * for multiplying.

    Password Protection

    Another rock star function is password protection for your Excel files.

    It’s extremely useful for sensitive information such as accounting, bank statements or whatever sensitive information one may want to put into a spreadsheet.

    Learning how to do that, is of course not as difficult as you might think.

    Freezing Rows Or Columns

    Oh, another frustration of working with a ton of data and a spreadsheet that seems to go on for miles?

    Disappearing headers and tabs that need to tell you which data to put where.

    You can learn how to freeze those columns or rows so that no matter how far you scroll, you can still see those fields.

    Select the ‘View’ tab at the top and then select ‘Freeze Panes’ where you can select which rows or columns you’d want to freeze.

    Nifty, right?


    • Tom La Vecchia

      Founder of New Theory & X Factor Media

      Founder and Publisher of New Theory Magazine and Podcast. Serial Entrepreneur who loves wine, cigars and anything that allows to people to connect and share experiences.

    You May Also Like

    Android Text Hack

    Researchers at Zimperium zLabs have uncovered a series of vulnerabilities affecting Android operating systems that could ...

    Hacking Team Hacked

    Over the past couple days, we’ve seen a rapid influx of Zero-Day vulnerabilities hit ...