Monday, August 2

The best Excel tricks that will make your life easier | Digital Trends Spanish

Some people are proficient in Excel. Not only do they know all its secrets, but they even enjoy using it. However, the rest of the mortals try (we try) to avoid it at all costs. Still, with some practice, Microsoft’s spreadsheet can be an ideal companion for some simple tasks. These are some of the best Excel tricks that will make your life easier.

It may interest you:

Select all

If you want to select a specific grid of cells, just click and hold the mouse while dragging in the range you need. But what happens when you want to select all cells? Excel offers you two alternatives:

  • Shortcut: press the keys Ctrl + E on Windows 10
  • With the mouse: press the small cell in the upper left corner (between column A and row 1), as shown in the image below.

Create a drop-down list

If you want to restrict the range of options that a user can enter in a given cell, creating a drop-down list is a good alternative.

To get started, choose a cell, then head to the tab Data and select Validate.

On Setting, look for the label Allow in the drop-down menu. Then select Ready. Highlight the cells where the data is that you want users to choose and click To accept.

When users click on the cell, they will only be able to choose the options from the drop-down menu that you specified.

Keep the zeros visible

Sometimes you may need to enter strings of numbers that start with one or more zeros. However, since the leading zeros have no value, Excel does not display them by default.

To solve this problem, you just need to add a single quote before the zeros. For example, if you can write »’0001» instead of »0001«. Just consider that the entered value will be considered as text and not as a number, so you will not be able to add or subtract it.

Highlight and remove duplicate data

There are times when having repeated data is useful. However, other times it just makes the data difficult to understand. If you face this situation, you can use conditional formatting to identify them. That way you can review them and decide if you delete them.

To check for duplicate data, select the cells you want to check. Click on Home> Conditional Formatting> Highlight Cell Rules> Duplicate Values.

In the dialog box choose the option Double and click To accept.

If you decide to remove them, select the range of cells with duplicate values ​​that you want to remove. Click on Data> Remove Duplicates and then below Columns, enables or disables the columns where you want to remove duplicates.

When you use the option Remove duplicates, repeated data will be permanently deleted. In addition, you should consider that the one that is located in the first place will be preserved.

Hide / show cells

Need to print a spreadsheet, but only want the essentials to show up? A good idea is to hide some rows or columns. Fortunately, it is one of the simplest processes in Excel.

You just need to right click on the cell where the column or row you want to hide is and check the option Hide. The column or row will be displayed with a thick border between adjacent ones. In addition, you will notice how the respective number or letter disappears.

To show it again, you will only need to highlight the contiguous columns or rows, press the right button and choose the option Show.

If you have hidden multiple rows or columns, you can show them all at once by selecting the rows or columns in question, pressing the right button and selecting Show.

Freeze rows or columns

Excel freeze dashboards

If you have an eternal spreadsheet and you need to keep an area visible while you move to another area, what you need is to know how to freeze rows or columns in Excel. To do this, you must go to the tab Sight, where you will find the buttons Immobilize and Divide.

  • Immobilize: the option Freeze panels allows you to set the visibility of the area of ​​the spreadsheet located on the left and over the active cell. This way, the columns will stay fixed when you scroll to the right; or the rows will be static when you scroll down. This option also allows you to freeze the first row or the top column.
  • Divide: although it looks a bit like the previous function, the option Divide create separate windows within the same spreadsheet. In addition, it allows you to adjust the work area can by dragging the divider panel.

How to separate names or data in Excel

Excel split data
You received a database with the full names of the participants in the same cell, but you need to organize them by surname. What a challenge, right? Although in the past separating data in a column required complex formulas, Microsoft has made our lives easier in the latest versions of Excel and today offers a wizard to separate data.

To do this, you only need to select the cell or column that contains the text you need to divide, select the tab Data and the option Columnar text.

In the Wizard, select Delimited> Next, and choose the delimiter to separate the data. It can be a tab, a comma, a semicolon, a space, or any character that allows you to divide the data.

Once you have decided, select Destination, which is the place on the spreadsheet where the divided data will appear, and Finalize.

As you will see, not all the bases are homogeneous, so it will surely require a manual process to correct some data. But it is a function that will save you a lot of time.

Editor’s Recommendations

var stage = decodeURIComponent(0); var options = JSON.parse(decodeURIComponent('')); var allOptions = {};

if (stage > 0 && window.DTOptions) { allOptions = window.DTOptions.getAll();

Object.keys(options).forEach(function(groupK) { if (options[groupK] && typeof options[groupK] === 'object') { Object.keys(options[groupK]).forEach(function(k) { if (!allOptions[groupK] || typeof allOptions[groupK] !== 'object') { allOptions[groupK] = {}; }

allOptions[groupK][k] = options[groupK][k]; }); } }); } else { allOptions = options; }

var getAll = function () { return allOptions; };

var get = function (key, group, def) { key = key || ''; group = group || decodeURIComponent('qnqb92BhrzmkpqGx'); def = (typeof def !== 'undefined') ? def : null;

if (typeof allOptions[group] !== 'undefined') { if (key && typeof allOptions[group][key] !== 'undefined') { return allOptions[group][key]; } }

return def; };

var set = function (key, group, data) { key = key || ''; group = group || decodeURIComponent('qnqb92BhrzmkpqGx'); data = data || null;

if (key) { if (typeof allOptions[group] === 'undefined') { allOptions[group] = {}; }

allOptions[group][key] = data; } };

var del = function (key, group) { key = key || ''; group = group || decodeURIComponent('qnqb92BhrzmkpqGx');

if (typeof allOptions[group] !== 'undefined') { if (key && typeof allOptions[group][key] !== 'undefined') { allOptions[group][key] = null; } } };

window.DTOptions = { get: get, getAll: getAll, set: set, del: del, }; }());

Leave a Reply

Your email address will not be published. Required fields are marked *