Are you using Numbers on your Mac to track, log, modify, and analyze data? You may not know, but this application offers several built-in tools to help you better process data.
With Numbers, you can completely ditch the calculator and stop wasting time on manual work. We’ll try to show you how to use formulas and functions in Numbers to make your data easier.
♥ BY TOPIC: Mac Finder Quick Steps: How to Use.
How to open the formula editor in Numbers on Mac
Before you start inserting formulas and functions in Numbers, it’s worth reading Formula editor…
It’s easy to use, just click on the cell where you want to add a formula or function and enter an equal sign there =…
This is already enough to open the editor. You can also click on the tab Insert -> New Formula on the toolbar, which will also open Formula editor…
You can move the formula editor by dragging on the left side. Resizing is possible by dragging the double-headed arrow to the left or right.
If you click the button while the formula editor is open Format, will open Feature Browser, which contains many different functions with descriptions.
As an example, let’s create a table of monthly expenses, in which you want to add the rows and columns to the total for the month and year. For this:
1. Place the cursor in an empty cell and enter the symbol = to call Formula editor…
2. Specify the cells to be summed. They will appear in Formula editor…
3. Press the green button in Formula editor to automatically count and write the answer to the cell.
You can also use arithmetic operators for subtraction, multiplication, and division. Numbers can be entered with or without decimal places.
If the formula is entered incorrectly or is not needed, then click the red cross to close this window without saving the changes.
Plus, for convenience, Numbers provides some basic formulas that you don’t even need to call Formula editor…
Let’s create the same action in our table, but with different data and using the button Paste, which is used to quickly apply a formula without using an editor.
4. Select the cell in which you want to use the formula.
5. Press the button “Paste” on the toolbar and select one of the options: Amount, The average, Minimum, Maximum, number, Composition or Quotation…
In our case, we choose the option Amount…
6. Numbers tries to automatically make a correct guess for a formula based on your spreadsheet data. Quite often Numbers isn’t wrong, but it doesn’t always happen. If the program has made an incorrect count (the selected cells are not suitable for use), use Formula editor from the previous instruction.
♥ BY TOPIC: 10 Safari for Mac (macOS) Hotkeys You May Not Know
How to copy formulas and functions to apply to other rows and columns of a table
With the examples we’ve shown above, you most likely want to apply a formula or function to more than one cell. This way, you can easily use the formula in adjacent cells, rows or columns depending on the layout of the data.
Select the cell with the formula you want to copy. When you see a yellow dot on the border of a cell, click on it and drag it to the cells where you want to copy the formula. Release the pointer.
The required formula or function is not only copied into these cells, but also immediately applied to the corresponding data.
♥ BY TOPIC: How to schedule your Mac to sleep and turn it on automatically.
Additional useful formulas and functions
If the basic formulas or functions already mentioned are simply not enough to accomplish your actual tasks, there are several other useful ways to work with your data.
Removing extra spaces
If the data in your spreadsheet was taken from a database, it may not always be formatted the way you want. Using the example customer table above, you can get the customer’s first and last name with a lot of spaces in between. This can cause problems when automatically sending emails using these names or merging them.
Using the function TRIM (Trim) in Numbers, we can automatically remove those extra spaces in a few simple steps.
1. Select the cell in which you want to use the formula, and then open Formula editor and Feature Browser…
2. Find the function TRIM (you can use the search), select it in the list and click “Insert function“.
3. In the formula editor, click on original line, and then click on the data cell.
4. Click on the green checkmark to apply the formula.
Now we have formed a correct and correct list of customer names without unnecessary spaces. In this format, the data will not cause crashes in other software.
♥ BY TOPIC: How to save Pages files as Word (doc, docx) on Mac, iPhone, iPad, or Windows.
Converting to plain text
Another feature in Numbers that can be very useful, especially when retrieving data from various sources, is SIMPLE TEXT… Perhaps your spreadsheet has text and numbers that are formatted in some way. These can be clickable links, or underlined, bold, or italicized names.
Using the function SIMPLE TEXT you can get rid of unnecessary formatting.
1. Select the cell where you want to place the formula and open Formula editor and Feature Browser…
2. Find the function SIMPLE TEXT, select it in the list and click “Insert function“.
3. Click on original line, and then click on the data cell.
4. Click on the green checkmark to apply the formula.
Now you should be able to see your text data without overlaying effects and fonts, URL links or other formatting.
ON THIS TOPIC: Continuity: How to get started on an iPhone or iPad, and continue on a Mac, and vice versa.
Easier ways to analyze data in Numbers
You can easily manage and analyze data in Numbers spreadsheets using formulas and functions. For processing numbers and text, try using formulas and explore some of the other built-in functions Numbers offers. You just never know which one might be useful until you test them.