There are two types of people, those who love Excel and those who still do not know it that much. Sorry to start off being so blunt, but I’ve really thought, for a long time now, that spreadsheets are the most useful type of application out there. And no, this is not an argument against the rest: word processors, databases, audio and video editing software, collection managers… the list of types of software that I consider sensational is very, very long, but if If I had to stick with just one (plus the web browser, okay, I’ll admit it), I think the choice would be Excel.
A few years ago, Satya Nadella, CEO of Microsoft, stated that Excel is the best Microsoft product ever created, a rather bold statement considering the entire history of the company, but also a fairly clear sign of the importance attached to the spreadsheet in Redmond that it was able to dethrone Lotus 1-2-3, just as Word did WordPerfect (which you might be surprised to learn still exists and is marketed by Corel), thus making Microsoft Office the main (although not unique, of course) exponent of office automation solutions.
Excel is an application that It aims to offer a countless number of options but, at the same time, be friendly to practically all types of users., a challenge that in general meets quite easily. However, this results in many functions being unknown to many users, who resort to alternative and less practical means to obtain results that, in reality, are much more readily available.
If you are a pro Excel user, it is quite likely that you already know some or all of them, but if not, I trust that you will find between these three Excel trickswhich in reality are but a small part of the enormous number of functions that are included in what, both for Satya Nadella and for me, is the best Microsoft product to date.
Get tables from a web
A relatively common operation when working with Excel is to transfer data from a table published on the Internet to a workbook. The most common, if this is small, is to transcribe the values manually. However, as soon as its size grows, the most common, what is usually seen almost always, is to copy (Control + C) the table in the source web and, later, paste it into Excel (Control +V), although on many occasions the data is not pasted well, some may be lost, etc., which requires a careful review and manual editing afterwards.
The good news is that, in fact, Excel has a tool to import data from the web, with which you can carry out these operations much more easily. Let’s see it with an example:
Imagine that you want an Excel document with the tables of the elements of the periodic table. An Internet search takes you to Wikipedia which, as you’d expect, has such information (you can find the tables grouped by their natural state at the bottom of the article).
Well, copy the URL of the page with the table that you want to take to Excel, then go to the application and, once in the destination spreadsheet, select “Data” in the upper menu, and then click on “From the web”, so that a new dialog identified as “From web” opens.
In the box in the central part of it, paste the URL of the data source page and then click on the “Accept” button. Next, if this is the first time you’re getting data from that source, the wizard will ask you what kind of access you want to use. Since we are talking about a public website, you can leave it in the default mode, anonymous, and continue. Then Excel will start to analyze the web page you have chosen and, at the end, it will show you a window like this:
As you can see, all the tables found on that page are shown on the left and, if you select any of them, a preview of it will be shown on the right:
As you can see, a selector is shown on the list of web tables in which you can indicate if you want to import more than one table. If so, mark it and, in this way, you can choose all the ones you want.
If you want to make changes to the way the data is imported, clicking on the “Transform data” button will open an editor where you can define these changes. But if you don’t want to apply changes, if you want to bring them from the web to Excel as they are, just click on the “Load” button and voila! You will already have the imported data in your Excel sheet, along with a set of tools that will offer you multiple functions related to said import:
Fix columns or rows
Excel allows you to work with large, very large, monstrously large, and inhumanly large tables. And as a general rule, any table worth its salt has headers in the top row and entries that are defined in the first column and are developed in the following ones. The problem is that, as a general rule, the view of Excel does not contemplate that the first row and the first column must always remain visible. And this, in many cases, means that we are constantly scrolling to the left and top of tables to confirm that we are editing the correct cell. Wrong.
Let’s say you are working on a table like this (don’t worry, the data shown in it is not real, it has been randomly generated in GenerateData):
As soon as you start to scroll down to the lower records, the headers indicating what type of data is displayed in each column will no longer be visible. And if you scroll to the right, you will see more data for each record, but you will lose sight of the name that allows you to identify which person each data corresponds to:
To end this problem, you will only have to go, in the upper menu, to the “Data” section and, when its options are displayed, click on “Freeze”, so that the menu is displayed
As you have already deduced, you will only have to select the second or third entry of it, depending on what you want to be displayed permanently (you can only choose one of the two) and, in this way, you will be able to move around the sheet without losing sight of the headings or the data in the first column.
And what happens if you don’t want to choose between row or column, but always keep both visible? Don’t worry, there is also a solution. Instead of using the freeze function, you will see that in the same function box there is also “Split”. Click on it, and at that moment you will see how the view of the sheet is divided into four sections:
Then put the mouse pointer right on the division of the four quadrants, so that its appearance becomes that of a cursor with four points, click and drag until the division looks like this:
Then click on any of the cells in the central quadrant and you will be able to move around the entire sheet without losing sight of either the first row or the first column of the document.
Change axis data
Sometimes, when we start designing a table in Excel or adding data, we are not entirely clear about its organization, that is, how we want to organize it horizontally and vertically. This gives rise, if we do not take into account the evolution that said document may have as it grows, that at a certain moment we see that we are wrong. Let’s see it with an example.
Imagine that you are the president of your community (I already feel sorry for you in that case) and that you create a document to keep track of the payment of the residents of the community. At first, it seems to you that the most practical thing is to put the dwellings in the first column, and that the payments (or the lack of them) are recorded in the following ones. The end result would be something like this:
Seen this way it looks good… the problem is that, as time goes by and new months are added, the result will be much worse, right?
No, don’t start thinking about copying the data to a new sheet with the proper orientation, that would be a fairly heavy process and in which it is relatively easy to make mistakes. Don’t worry, Excel has a specific function to transpose the data. To use it, select all the data whose orientation you want to modify and press Control + C to copy them to the Windows clipboard.
Then place the mouse pointer in the place where you want to paste them (in the upper left corner of the range of cells for this purpose) already with the new orientation, click on that cell with the right mouse button, click on the entry “Paste special” from the context menu and, in the window that will be displayed
Make sure to check the “Transponder” entry, and click “OK”. In this way, the data will be copied automatically, already with the new orientation: