Excel Tips and Ticks from our Experts

In the article below we addressed a couple of our latest Excel tips to help you to work faster using Excel in your daily routine. You will learn how to deselect cells or a range from a current selection, how to quick analyse data in Excel, to add a conditional column, and finally add a column from an example.

Get your Total Without a Formula

Here’s a handy tip for tables: Add a Total Row without writing a formula. Just click anywhere inside an Excel table. Then press Ctrl+Shift+T and Excel will add the Total Row to the bottom.

 

Tables Make your Data Stand Out

You can turn your ordinary data cells into a full-fledged Excel table in a really easy way. Just click anywhere inside your data and press Ctrl+T. A table provides all sorts of handy conveniences, like alternating coloured rows and adding AutoFilter buttons. Additionally, just looks much better.

Excel can Help Your Memory

Everybody hates that common situation when you are pretty sure that you have used that Excel feature before but can’t remember where it is.

Just click in the box labelled Tell me what you want to do (it’s the one with the light bulb), and search for a button, menu, function—whatever you want. In this example, we searched for “freeze” to find the button for freezing columns and rows. What will you search for?

Slice Data Your Way

Slicers are buttons that let you filter data easily. Select any range in a table or PivotTable and go to Insert > Slicer. Select the column you want to filter by. When you’re done, you’ve got handy buttons that let you filter, as you can see in the image below.

READ:  30 Tips & Tricks to Master Microsoft Excel

Templates Can be a Practical Choice

Excel has a good selection of useful templates which can help you communicate your data to your stakeholders. To use them Click File > New, and search for “accessible.” Choose Accessible Template Sampler to see all the accessible templates available in Excel.

Find the Needle in the Haystack with VLOOKUP

VLOOKUP finds information in another table and brings back related information to the cells you’re working with. And, with the TRUE argument, it will find the information with an approximate match. In this example below it looks up 0.92 and finds the closest match: 0.9. Then it brings back the value in the next column to the right of 0.9 which is the letter A.

See your Status Instantly

The Status Bar shows averages, counts, and sums — without typing out complicated formulas! Just select some cells in your table, and then have a look at the lower right-hand corner of the Excel window. There you’ll see instant stats about your selection. In the image below you can see how it works.

READ:  How to Unhide Sheets in Excel (All In One Go)

How to Unselect a Cell From a Selection

With the whole area selected, you hold down the CTRL (or Command on mac) key and click on the cells you want to deselect. To unselect a range of selected cells hold down the CTRL (or Command for Mac) key and drag the range you want to deselect, starting from within a selected range. As you can see in the image below.

 

How to Multi Select Cells or Ranges

This feature as available before, it means that you can still use the CTRL (or Command on Mac) key to click on an unselected cell to select it. The new thing is that you can select an additional range simple use the CTRL (or Command on Mac), starting on an unselected cell and drag the mouse to select the range, as the image below is showing.

 

 

How to Quick Analyse your Data in Excel

Excel’s Quick Analysis button lets you instantly create different types of charts, including line and column charts, or add miniature graphs called sparklines. Check the example below to see how it works.

Select the data and select the Quick Analysis button in at the end of your selection (or press Crtl + Q).

Them select charts and click more to see all chart options available.

Choose the type of chart you want, and it will be ready.

READ:  7 Tips to Improve Your Basic Microsoft Excel Skills

 

How to Add a Conditional Column

Using Power Query in Excel, you can add a conditional column to your query. You can define IF-THEN-ELSE conditions in your query. When the conditions are fulfilled, the conditional column will automatically display the values that you specified. Click below to play the video and see how it works.

Add a Column From an Example

You can use Excel’s Get & Transform (Power Query) experience to add new columns of data in the Query Editor by simply providing one or more sample values for your new column. You can create a new column example from a current selection, or from providing input based on all (or selected) columns in a given table.

 

Master your Excel skills

Being Microsoft Office skilled can be handy in the daily basis, it can save your time and make your activities more productive. At New Horizons Ireland we frequently run a wide range of Microsoft Office classes either on our premises or online. Check out below our classes.

Fill Cells in a Flash

Just start typing in a column to the right of your data. In this example, we typed Smith, then North. Excel then does the rest and it will give suggestions for the rest of the cells. To accept the suggestions, press Enter, and you will get the it done.