Excel tricks office workers need to know

As an office worker, surely using Excel in your work is almost regular. When you grasp the Excel tips will help you handle the work faster and more efficiently. Understanding this, WebTech360 has synthesized and provided you with some very useful tips in the office world. Let's explore those tricks too.

Quick calculation without entering formulas

This is a quick trick in spreadsheets for those who are not familiar with formulas in excel. This is very simple, you just need to select (black out) the data area to be calculated, then click the icon on the toolbar and select the function to calculate.

Then the results will be automatically added to the box below the selection you selected, assuming here we choose the Max flagship to find the largest MONEY, then we get the result 39000.

• Sum: Calculate the total
• Average: Calculate the average value
• Max: Find the maximum value
• Min: Find the minimum value.
• Count Number: Count numeric values

If you only sum in a formatted spreadsheet, just select the area you want to calculate and press Ctrl+Shift + T , the table will automatically add a total line at the end of the table.

Quickly turn a data area into a table in Excel

Want to turn ordinary original cells into a full-fledged Excel table? Click on any cell in the data area, then press  Ctrl+T , confirm the data area OK again

The panel provides all the useful functions, coloring lines and adding a Filter button. In addition, the table also looks better.

Looking for functionality, use Tell me!

You've used a certain Excel feature before… but you can't remember where it was. No problem, Tell Me can help!

Just click on the box labeled Tell me what you want to do (Ask me) and you can already search for a button, menu, function, whatever you want. In this example, we searched for “Freeze Panes” to find the button for the columns and rows to freeze.

Visualize data with Data Bars 

Want to convey numbers visually? Let's use data bars in Excel

Select the data area, then go to  Home > Conditional Formatting > Data Bars and choose a color scheme.

Use Slice data however you want

Slicers are buttons that allow you to filter data easily.

Select the data range in the table or PivotTable, and then go to  Insert > Slicer. Select the column you want to filter the data for. When done, you have convenient buttons, just click on the information you need to filter.

You can choose many fields, here I choose the row name field as an example

 Ultra-fast graphing of your data

Select any cell in your data range, and then press  Alt+F1 . Look! Now that you have a chart, that's great, right?

Using the IFS function for analytical problems

When you need a formula to do multiple “if this then that” calculations, use the IFS function. The IFS function evaluates multiple conditions so that a nested formula is not required. In this example, the IFS function looks for a cell in the Score column and, depending on the score value, displays the score in words.

The IFS function is available in the latest version of Excel for Office 365.

Create extremely fast PivotTables

If you still have no knowledge of PivotTables or have no experience in creating PivotTables, you can still easily apply PivotTables to analyze data visually.

Go to  Insert > Recommended PivotTables , select your data range, and choose the best range.

To rotate or not rotate (PivotTable)

Suppose, you receive a report with data, but you cannot create a PivotTable because the data has been rotated. The following steps will help you to disable data rotation:

1. Select your data, click  Data > From Table/Range > OK  to create the table.
2. When the Query Editor opens, press and hold the CTRL key to select the columns that you want to unrotate.
3. Go to  Transform > Unpivot Columns > Only selected columns.
4. Go to  Home > Close & Load to place the unrotated data in a new worksheet in Excel.

You can now create your own PivotTable.

The above are just some basic tips to help those who are new to Excel, hopefully it will also help you increase your work efficiency. Follow the page to keep up to date with the latest tips.

Good luck