Many people would claim that Excel’s desktop app is far more useful than its web-based counterpart, and they would be right: it has more tools, is much more powerful, and works well without an internet connection. However, over the past year, Excel for the web has improved in many ways.
1
Easily Insert Rows and Columns
To add rows and columns between existing data in the Excel desktop app, you need to select and right-click a row or column and click “Insert.” However, this method can cause confusion if you don’t know that Excel inserts columns to the left and rows above by default.
This is where Excel for the web has got your back. If you decide that you need to add another row or column, simply hover over the row or column header (in other words, the letters along the top of your spreadsheet or the numbers down the left-hand side). As well as your cursor changing to a down arrow, you’ll see tiny control circles appear on either side of the row or column header.
When you hover over one of those circles, it turns into a “+” symbol, which you can click to insert a new row or column in that position.
That’s not to say you can’t use the traditional right-click method. However, this new technique makes things much quicker for those who add rows and columns often.
If you click the “+” to add a column to the right, the new column will adopt any formatting from the column to its left. Similarly, clicking “+” to add a new row underneath the active row will duplicate the formatting from the row above.
2
Export Excel Files as a CSV
Exporting Excel worksheets as a plain-text comma-separated valuesÂÂ (CSV) file is often preferred because this format is widely compatible with most spreadsheet software, makes data transferring straightforward, and is easy to export and import.
Previously, to save an Excel sheet in this simplistic format, you would have to use the Excel desktop app. However, Microsoft’s decision to allow you to export your Excel file as a CSV when using the free online version of the program means many more people can now take advantage of this tool.
By default, Excel for the web saves your work as an XLSX file, which is an improvement on the older XLS file format in terms of security, capacity, data integrity, and accessibility. However, despite these benefits, XLSX files don’t offer the universality, simplicity, and efficiency that we get when we use CSV files.
To export your Excel workbook as CSV, click “File” in the ribbon, choose “Export,” and select “Download As CSV.”
Your plain text Excel file will then instantly appear in your Downloads folder.
3
Use the Date Picker
If you often work with spreadsheets containing lots of dates, or if you’re sharing a spreadsheet that requires others to insert a date, make the most of the hidden date picker tool in Excel for the web.
To activate this feature, you need to change the number formatting for the cell or cells where the date or dates will be inserted. Select those cells, and in the Number group drop-down menu in the Home tab on the ribbon, click “Short Date” or “Long Date,” depending on the type of date display you want to achieve.
To further customize how the date will appear in the selected cells, click “More Number Formats” in the Number Format drop-down menu, and choose from the more extensive list in the All Formatting Options pane that appears.
Now, double-click those affected cells to launch the date picker and select a date.
4
Copy and Paste Improvements
One of the biggest set of changes to Excel for the web in the last 12 months relates to the program’s copy-and-paste tools.
First, there are many more options to choose from when pasting data into your Excel worksheet. For example, rather than pasting everything associated with the last item you added to your clipboard, you can paste values only, formulas only, formatting only, or a link to the source, as well as keep the source column widths or paste the data with the rows and columns transposed (switched). These options are available via the Paste drop-down menu in the Home tab on the ribbon once you’ve copied the data you want to duplicate in your spreadsheet.
Second, instead of copying and pasting whole rows or columns when you want to reposition them, Excel for the web lets you drag them. All you need to do is hover over the header of the row or column you want to move, and click and drag the six-dot handle that appears to move it to a new place.
Finally, the AutoFill feature in Excel for the web now aligns with how it works in the desktop version of the app. After typing data into one or more cells, select that range, and click and drag the fill handle in the bottom-right corner of the last cell. You’ll know you’re doing the right thing when your cursor turns to a “+” icon.
Related
How to Automatically Fill Sequential Data into Excel with the Fill Handle
The fill handle in Excel allows you to automatically fill in a list of data (numbers or text) in a row or column simply by dragging the handle.
5
Share Sheet Views
Sheet views in Excel let you share a specific layout of an Excel worksheet with others. As a result, when you share a sheet view with someone else, they only see the view you have created, saving them from having to trawl through large volumes of data that would otherwise only get in their way.
After creating the view you want to share by adding filters, hiding rows and columns, or freezing panes, in the Sheet View group of the Data tab on the ribbon, click “New.” In my example, I’ve filtered the Age column to show only those aged 11 or 12.
You’ve now created a template that defaults to the spreadsheet view you created. You know this is the case because the row and column headers turn black, and you can see the Temporary View label in the Sheet View group of the Data tab.
To rename the view you just created, click the field containing the words “Temporary View,” and replace those words with a name that aligns with what the view displays. In my case, I’m calling my new view “Age 11 and 12”. When you’re done, press Enter.
After creating different sheet views, remember to reset the default sheet view. To do this, click the down arrow next to the field where you name each view, and click “Default.” Then, remove all filters, unhide any hidden rows or columns, and unfreeze any frozen panes. You can then jump between each sheet view as required, safe in the knowledge that your default view is the version that displays all the data.
Now, with the new view opened, right-click any cell, and click “Copy Link To Sheet View.”
Finally, in the dialog box that appears, click “Copy” to place the link on your clipboard, ready to be pasted into an email, a Slack message, or a Teams chat.
Click “Anyone With The Link Can Edit” to add a password, define who can access the spreadsheet view, determine whether others can edit the data, and set a link expiration date.
Excel’s improvements aren’t going to stop there. Indeed, Microsoft’s roadmap involves many further changes to its spreadsheet program, such as expanded Copilot integration, closer integration with Microsoft Forms, more tools for advanced data analysts, and, inevitably, several new functions.
