Excel from Microsoft is a very important application when it comes to carrying out office chores, sorting out information, making reports, and analyzing business data. No matter whether you are an administrator, accountant, marketer, human resource manager, or project manager, having some knowledge on how to handle Excel will help you perform all the tasks effortlessly.
With the right Microsoft Excel tips, you will be able to reduce the amount of repetitive work, arrange your spreadsheet and analyze data without spending many hours on making calculations manually.
Keyboard shortcuts are one of the simplest methods for speeding up your work in Excel. Instead of constantly accessing the menus, you can carry out tasks using keyboard shortcuts.
The following is a list of some of the useful shortcuts of Microsoft Excel:
Action
Shortcut for Windows
Copying of selected cells
Ctrl + C
Pasting of copied material
Ctrl + V
Reversing an action
Ctrl + Z
Saving of the workbook
Ctrl + S
Finding specific information
Ctrl + F
Selecting all data
Ctrl + A
Creating an Excel table
Ctrl + T
Adding or removing filters
Ctrl + Shift + L
Format cells
Ctrl + 1
These shortcuts function in most versions of Excel on the desktop, but there are differences in commands depending on the operating system and version used.
First of all, learn the shortcuts that you use most frequently. For instance, those employees who make regular reports will benefit from using these shortcuts because they will not have to switch back and forth from the keyboard to the mouse.
In cases where you have huge volumes of data, creating an Excel table from a range of selected cells is helpful in making the data more organized.
Select your data and press Ctrl + T. Confirm the selected range and specify whether your data has headers.
There are many benefits of using tables in Excel:
Formatting automatically expands when adding new rows.
Filtering buttons can be included in the table headers.
Sorting and filtering become easier.
Formulas and formatting expand along with the table.
References in formulas become easier.
For instance, the sales team may make a table that consists of employees’ names, sales figures, date and regions. The team members will be able to filter the records by region or sort the sales figures to find out which products perform well.
Every column should be related to only one kind of information, while you should not have any empty rows in your dataset.
formulas will enable you to make automatic calculations rather than making manual calculations. In Excel, formulas always start with an equal sign (=) followed by a function or calculation.
These formulas and functions in Excel are important for common office tasks.
SUM: Adds numbers in a selected range.
=SUM(B2:B20)
This formula will add the total of numbers in cells B2 to B20.
AVERAGE: Finds the average of numerical values.
=AVERAGE(C2:C20)
It will be used to find the average of sales, employee score, or project cost.
IF: Produces results based on a true or false logical test.
=IF(D2>=10000,"Target Met","Below Target")
This formula will determine whether the number in D2 cell is above or below 10,000.
COUNTIF: Counts cells that meet criteria.
=COUNTIF(E2:E50,"Completed")
It will be used to count completed tasks in a project tracker.
XLOOKUP: Finds a value from a set of values and produces corresponding value from another range.
=XLOOKUP(A2,F2:F20,G2:G20,"Not Found")
The formula will search the value of cell A2 in column F and produce its corresponding value in column G.
Knowing these formulas and functions is important in the office setting.
Larger spreadsheets tend to be hard to manage when all records are placed together. Sorting and filtering allow users to concentrate on needed data.
Sorting allows arranging records based on their values, which can be dates, names, or prices. Filtering will hide records that don't meet certain criteria temporarily.
For example, sorting of employee records managed by an HR professional can be done through the department column and selecting the marketing group. Sorting of invoices by a financial department employee will help to see what payments are pending soon.
In order to use filters, the user should select the needed data and press Ctrl+Shift+L in Excel on Windows. Dropdown arrows in the column headings will provide criteria selection.
Before sorting, it is necessary to select the whole dataset, because sorting of only one column can lead to a split between values and records. It will be incorrect information.
Conditional formatting changes the appearance of cells automatically when certain conditions are satisfied. It makes it easier for readers to find patterns, anomalies, and significant data.
It can be used for:
Marking late assignments in red.
Determining high sales against targets.
Searching duplicate employee identification numbers.
Reporting low inventory amounts.
Comparing performance through colors.
Conditional formatting involves selecting the necessary cells, clicking on the Home tab, then clicking on “Conditional Formatting” and choosing an appropriate rule.
A project manager can use conditional formatting to highlight those deadlines that have passed yet are marked as incomplete. It will be helpful when reviewing projects.
Do not overuse formatting. Using too many colors will confuse the reader; thus, use formatting only when necessary.
Pivot tables can be helpful when summarizing huge chunks of data without having to write complex formulas.
Let us consider that your business has thousands of transactions data in which each transaction consists of the name of the product, region, date and the total revenue earned. A pivot table can help you sum up the total revenue earned in each region, compare the products and even the sales in each month.
All you have to do is to select your data set and then click on Insert>Pivot Table. Choose your data range and also the destination of your pivot table report. You can then drag the necessary fields to the Rows, Columns, Values and Filters area.
You can change these fields in order to view the same data from various perspectives.
Make sure that your data source is well organized and formatted before creating a Pivot table. Refresh the pivot table when there is any change in your data source.
The validation of data will limit the nature of data that the user will be allowed to input within the selected cell(s). It becomes especially useful where there is more than one person editing the workbook.
For instance, in a task tracking sheet, one will have the ability to limit the status to Pending, In Progress, and Completed.
To create a drop-down list, one will need to select the cells that need it, go to the Data tab and select the Validation of data option, select List and then insert the acceptable items or cells range.
If you have several hundred rows in your worksheet, there is a chance that the column headers will be lost when you scroll through the document. Freeze Panes allows you to keep specific rows or columns always visible when working with the worksheet.
Click View in the ribbon, go to Freeze Panes, and then choose one of the options – Freeze Top Row, Freeze First Column, or Freeze Rows and Columns from the Selected Cell.
It comes in handy when you work with employee directories, inventory listings, financial statements, or customer databases.
The professional spreadsheet must be clear and reliable. Prior to distributing the workbook, you should review the information and watch out for common mistakes.
Follow these practices:
Use uniform formats for date, currency, and percentage.
Verify the formulas for cell referencing errors.
Delete any extra blank rows and duplicate data entries.
Name the worksheets meaningfully and descriptively.
Label the graphs and provide explanations for significant numbers.
Save the workbook before making big changes.
Secure sensitive data through access controls.
Always avoid manually inputting computed data where there is an option to compute the values using formulas. Nonetheless, always ensure to validate computed values.
Microsoft Excel tips are an efficient method of improving productivity in any office job. Shortcuts allow saving time, formulas automate the process of calculations, tables are used for organizing information, and PivotTables will assist in converting raw data into useful reports.
It is unnecessary to learn all the features of Excel in one moment. Start with several shortcuts, learn basic functions and gradually add some other tools according to your work needs.
Using your experience and proper handling of the data, you will be able to use Excel as an efficient tool for report preparation, progress tracking, error minimization and decision-making.