Microsoft Excel is one of the most useful programs in the Office 365 lineup. If used correctly, it can streamline your accounting, data entry, and business analytics processes, but that’s a big if. Excel can be a confusing mess of features, especially for those who don’t have much experience working with spreadsheets. If you’re an Excel newbie or if you just want to be more proficient with Excel, consider the following tips.
Learn time-saving shortcuts
Pointing and clicking with your mouse may help you sort your spreadsheets, but it’s not the most efficient way to use Excel. When you have massive spreadsheets with almost an infinite number of cells, keyboard shortcuts can save you plenty of time. You may already know the basics like Ctrl + Z to undo changes and Ctrl + C/V to copy and paste. However, Excel has a complete list of shortcuts for data entry, formatting, and more. Here are a few you should learn:
- Ctrl + F – Lets you search for specific words or figures
- Ctrl + K – Inserts a hyperlink
- Ctrl + N – Creates a new workbook
- Ctrl + T – Creates a new table
- Ctrl + ; – Enters the current date
- Ctrl + Shift + : – Enters the current time
- Ctrl + Shift + % – Applies the percentage format on selected cells
- Ctrl + Shift + $ – Applies currency format with two decimal places
- Ctrl + Spacebar – Selects the entire column
- Shift + Spacebar – Selects the entire row
Take advantage of Data Types
Data Types uses live connections with online resources, allowing you to plug real-time stock market and geographic data into your spreadsheets. Stock market data types enable you to pull current share price, trading volume, and employee headcount. Meanwhile, geographical data types let you add population, GDP per capita, time zones, and other location-specific information.
To use this feature, type specific text into a few cells. If you want geographic data, type a country, state, or city into each cell. If you want stock data, write a company name. Next, select the cells and click on either Geography or Stocks under the Data tab. Excel will indicate if the text in the cell matches Microsoft’s online resources. If it doesn’t, you’ll need to click the cell and manually find the location or company in Excel’s built-in search tool. An Insert Data button will appear in the next column, where you can add data sets like population and price.
Use logical functions in Excel
Incorporating logical IFS (if this then that) functions in Excel allows you to automate tasks and calculations. The formula for IFS functions is:
=IFS(Condition,Value if true)
You can apply this formula for various situations. For instance, you can use it to calculate a 15% discount if customers spend above $1,000. The resulting formula will look something like this: =IFS(B2>1000, B2*85%). This formula can then be copied over to other cells, allowing you to compute discounts for all your clients. IFS functions can also be used for verifying overdue payments, grading performance, and so much more. Check out Microsoft’s page for more examples.
Understand your data with Ideas
Ideas in Excel is a data analytics tool that makes it easy to visually represent your data. Instead of manually choosing charts, you can select a range of cells and click on Ideas on the Home tab. This then takes you to a task pane with suggested charts that highlight trends, outliers, and correlations. Excel uses artificial intelligence (AI) technology to recognise patterns in your data and choose the best chart type. It even fills in the labels, axes, and titles for you so you can focus on the analysis.
So far, Ideas generates four different types of insights:
- Trends – Looks for repeating patterns, increases, and decreases
- Rank – Highlights sets of figures that are larger than the rest — usually represented as a bar chart
- Outliers – Identifies any anomalies in your data set
- Majority – Finds items that make up most of a total value — often displayed as a pie chart
Get help from Excel
If you’re having trouble locating features and commands, press Alt + Q. This will bring up a search bar where you can type what you want to do in Excel. For example, if you want to make finance-related calculations, just type ‘financial’ to get a list of formulas.
You can also ask for assistance for which commands to use by typing ‘help’ on the search bar. Excel will usually recommend commonly used formulas, tables and charts, and formatting options. Whatever feature you use, Excel’s built-in search bar will save you hours sifting through layers upon layers of menus.
There’s so much more to Microsoft Excel than what we’ve covered in this article. If you want to learn more about the software and become a spreadsheet master, stay tuned to our blogs. We’ll keep you posted on the latest time-saving Microsoft features. We also provide Office 365 plans that feature online versions of your favourite productivity apps, and so much more. Contact us today to learn more.