Glide Training Logo

June 2024

Ten Tips for working productively in Excel and Google Sheets

  • Working with lists? Use Freeze to fix key columns and rows when you are scrolling

    Make sure you’re in the correct cell depending on which app you’re using:

    Excel

    Click in the cell below the row and in front of the column you want to freeze.
    Then View > Freeze Panes > Freeze Panes.

    Freeze - Excel

    Sheets

    Click in the cell in the row and the column you want to freeze up to and including.
    Then View > Freeze > Up to row # and Up to column #.

    Freeze - Sheets

  • Need to apply the same formatting to non-consecutive cells?

    Excel / Sheets

    Select the first cell/block of cells, then hold the CTRL key on the keyboard while you select other cells. This also works to select non-consecutive cells for functions such as SUM!

    Selecting non-consecutive cells in Excel/Sheets

  • Want to copy formatting from one cell to other cells?

    You can do this in an instant – in Excel you use the Format Painter, in Sheets, it’s called Paint Format.

    Excel

    Select a cell containing the formatting you like. Click on the Format Painter button on the toolbar. Then highlight cells you want to copy the formatting to.

    Format Painter in Excel

    Sheets

    Select a cell containing the formatting you like. Click on the Paint Format button on the toolbar. Then highlight cells you want to copy the formatting to.

    Paint Format in Sheets

  • Need to split data from one column into multiple columns?

    Ensure you have blank columns ready for the split data. Select the column to be split. Then:

    Excel

    Choose Data > Text to columns

    Split to columns in Excel

    choose a separator (space in this case) then click finish

    Split to columns in Excel - outcome

    Sheets

    Choose Data > Split text to columns

    Split to columns in Sheets

    choose a separator (space in this case) then press the Enter key

    Split to columns in Sheets - outcome

  • Copy the contents from the cell above

    You don’t need to copy and paste or fiddle around with the mouse, just use a keyboard shortcut:

    Excel

    Ctrl ‘ (note on a UK/US keyboard this is the apostrophe to the right of the semi-colon key)

    Copy cell above in Excel

    Sheets

    Ctrl D

    Copy cell above in Sheets

  • View two places in a workbook at once

    Excel

    You can either :

    a) Split the view of the current worksheet in two by highlighting a column or row then clicking View > Split

    Split view in Excel

    you can now scroll the two parts of the workbook separately

    Split view in Excel - outcome

    or –

    b) View anywhere in the workbook, including a different worksheet, by having two windows open side by side – click View > New Window,

    new window in Excel

    then View – Arrange All

    Arrage windows in Excel

    two copies of the workbook now appear in different windows that can be navigated independently

    Two worksheet windows open in Excel

    Sheets

    There are no menu options in Sheets as there are in Excel, how there is a simple solution to this – you can replicate either of the above by simply having two browser windows open at once side-by-side, with the same URL in each!

    Two browser windows open in Sheets

  • Editing keyboard shortcuts

    Excel / Sheets

    if you don’t know this one you really ought to…

    Ctrl X to cut

    Ctrl C to copy

    Ctrl V to paste

    (these work on most other apps too – if you right-click you’ll usually see them appear as options)

    keyboard shortcuts for cut / copy / paste

  • Clear formatting from cells

    Excel

    In the Home tab of the ribbon click Clear – Clear Formats

    clear formats in Excel

    Sheets

    From the menu select Format – Clear Formatting

    clear formats in Sheets

  • Select from a cell to the end of a row or column in the current data set

    Excel / Sheets

    Click on the starting cell, then hold the Shift & Ctrl keys and press the relevant arrow key (up, down, left or right) to select the cells. It will stop the selection when it reaches the end of the data set or hits a gap.

    selecting to the end of a row of data Sheets

    (Example 1: user has selected cell D6 and pressed Shift Ctrl ⇒)

    selecting to the end of a column of data Sheets

    (Example 2: user has selected cell D6 and pressed Shift Ctrl ⇓)

  • Select the whole data set

    Excel / Sheets

    Click anywhere in the data and press Ctrl A on the keyboard to select the entire data set

    selecting the whole data set in Sheets

  • Bonus Tip – more keyboard shortcuts!

    • Excel

      Pressing Alt allows you to access any visible menu command by following the on-screen prompts to navigate to a menu tab then a command.

      Step 1: press Alt once to show the menu tabs (and other options) that can be selected by pressing the appropriate key

      using Alt in Excel to access menu commands from the keyboard - step 1

      Step 2: after pressing a key (H for Home in the above example) to choose a menu tab, you’ll now see the menu commands that can be accessed with the appropriate key(s)

      using Alt in Excel to access menu commands from the keyboard - step 2

      Sheets

      Pressing Ctrl / (forward slash not backslash!) brings up a list of all keyboard shortcuts that you can turn on and access at any time.

      pressing Ctrl / in Sheets to view keyboard commands

    Thanks for reading our ten tips for working productively in Excel and Google Sheets

    There we go! We hope our tips help you get the most out of your spreadsheet apps and become more productive in the workplace.

    If you’d like to see more hints and tips on how to get the best out of the software and apps you use every day, why not join our mailing list or take a look at our YouTube channel for regular updates!

    Let’s Take It Further

    Could you or your team benefit from some face-to-face training?

    Glide Training is one of the leading providers of professional IT training in the UK.

    We provide training on Microsoft and Google products including Excel, Docs, Word, Sheets, PowerPoint, Slides, Project and more, delivered on-site at your premises or online.

    Unlike most training companies we won’t try to sell you a set course from a fixed list; rather we will discuss your specific requirements and work with you to deliver a training programme that gives you what you need to succeed.

    find out more

    -->

    Ten tips for working productively in Excel and Google Sheets

    It can be surprisingly easy to save yourself a bit of time and effort in the workplace just by making use of some of the many brilliant tools that are available in spreadsheet apps. Not everyone uses them (yet!) but they can help everybody work more efficiently. Here are our ten tips for working productively in Excel and Google Sheets.

    Read more on Ten Tips for working productively in Excel and Google Sheets…


    How to open a Google Slides file in presentation view

    Today’s Top Tip gives you a quick and easy way to tweak a URL so you can open a Google Slides file in slideshow presentation view.

    If you’d like to see more hints and tips on how to get the best out of the software and apps you use every day, take a look at our YouTube channel. Follow us for regular updates!

    Read more on How to open a Google Slides file in presentation view…


    Drill down into your pivot table data with a double click

    Our Quick Tip today shows you an easy way to drill down into your pivot table data without having to apply filters to get to the information you want to see. This works both on Excel and Google Sheets.

    Read more on Drill down into your pivot table data with a double click…


    Formulas for upper, lower & proper case text in Excel & Sheets

    This week’s Quick Tip shows you how to make use of simple formulas to quickly change text into UPPER, lower or Proper case, both in Microsoft Excel and Google Sheets.

    If you’d like to see more hints and tips on how to get the best out of the software and apps you use every day, take a look at our YouTube channel. Follow us for regular updates!

    Read more on Formulas for upper, lower & proper case text in Excel & Sheets…


    Select multiple tabs in Sheets and Excel

    In this week’s quick tip we show you how to select multiple spreadsheet tabs in in either Word or Sheets, and apply an action to all of those tabs at once.

    Read more on Select multiple tabs in Sheets and Excel…


    Use SORT to keep dropdown lists in A-Z order in Excel and Google Sheets

    Today’s quick tip shows you how to use the SORT function to keep your dropdown list items in A-Z sort order in Excel and Sheets. This video assumes prior knowledge of Data Validation for dropdown lists!

    Read more on Use SORT to keep dropdown lists in A-Z order in Excel and Google Sheets…


    Use TOROW and TOCOL to combine data into a single row or column in Sheets and Excel

    In this quick tip we show you how to make use of the powerful TOROW and TOCOL functions that allow you to combine data from a range of cells into a single row or column. The process is exactly the same in Google Sheets and Microsoft Excel.

    Read more on Use TOROW and TOCOL to combine data into a single row or column in Sheets and Excel…


    Online Google Sheets Training

    Or were you looking for On-site Google Sheets Training?

    Why choose Glide for your online Google Sheets training?

    We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you’re looking to achieve.

    find out more

    All our on-site training courses are delivered at our customers’ premises. Not only does that avoid any travel inconvenience for our customers, but also it means we don’t have the overheads required to maintain a dedicated training centre – and in turn we are able to pass these savings on to you.

    find out more

    Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

    find out more

    Feedback from some of our Online Google training course attendees

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “IT Training and enthusiasm do not often go hand in hand, but Jane has achieved it! The trainees leaving the sessions with Jane have all said how useful the sessions have been and with big smiles and thumbs up all round. Many are already asking for more and can’t wait to see Jane back again. Thanks Jane!”

    Get a Quote

    -->


    Online Google Sheets Training

    We provide online Google Sheets training to users of all levels, from beginners who require basic spreadsheet skills to experts who wish to get the best out of the more advanced features. We also provide transition training for those who are moving from Excel.

    Read more on Online Google Sheets Training…


    On Site Google Sheets Training

    Monday, November 30th, 2020

    Or were you looking for Online Google Sheets Training?

    Why choose Glide for your on-site Google Sheets training?

    We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you’re looking to achieve.

    find out more

    All our on-site IT training courses are delivered at our customers’ premises. Not only does that avoid any travel inconvenience for your team, but also it means we don’t have the overheads required to maintain a dedicated training centre – and in turn we are able to pass these savings on to you.

    find out more

    Our trainers have designed and delivered on-site IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

    find out more

    Feedback from some of our on-site training course attendees

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “IT Training and enthusiasm do not often go hand in hand, but Jane has achieved it! The trainees leaving the sessions with Jane have all said how useful the sessions have been and with big smiles and thumbs up all round. Many are already asking for more and can’t wait to see Jane back again. Thanks Jane!”

    Get a Quote

    -->


    On-Site Google Sheets Training

    We provide on-site Google Sheets training to users of all levels, from beginners who require basic spreadsheet skills to experts who wish to get the best out of the more advanced features. We also provide transition training for those who are moving from Excel.

    Read more on On Site Google Sheets Training…


    Online Google Workspace Training

    Thursday, October 22nd, 2020

    Or were you looking for
    On-Site Google Workspace Training?

    Why choose Glide for your online Google Workspace training?

    We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you’re looking to achieve.

    find out more

    All our on-site IT training courses are delivered at our customers’ premises. Not only does that avoid any travel inconvenience for your team, but also it means we don’t have the overheads required to maintain a dedicated training centre – and in turn we are able to pass these savings on to you.

    find out more

    Our trainers have designed and delivered online IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

    find out more

    Feedback from some of our online training course attendees

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “I am so pleased I allowed myself the time to come to this course. I am now confident I can sort out my overly complicated spreadsheets and actually understand the data and use it effectively. Thank you so much!”

    “IT Training and enthusiasm do not often go hand in hand, but Jane has achieved it! The trainees leaving the sessions with Jane have all said how useful the sessions have been and with big smiles and thumbs up all round. Many are already asking for more and can’t wait to see Jane back again. Thanks Jane!”

    Get a Quote

    -->


    online Google Workspace Training

    Many organisations are making the change from Microsoft Office to Google Workspace (formerly known as G Suite). Google Workspace has some great tools for collaborative working and we help organisations to maximise the opportunities that it offers.

    Read more on Online Google Workspace Training…