Export your Twitter archive – and practical ways to use it

As many feared previously, under Elon Musk’s management Twitter has been in a perpetual state of disarray, to the point of having its former identity erased when Musk decided to rebrand the site as X. Currently there doesn’t seem to be a clear alternative on the horizon, despite numerous attempts with different approaches. The constant erosion of the platform, and various rumors that Musk considers removing free accounts, have made me reflect on my options when this site – inevitably – goes dark. While I obviously can’t do anything to prevent it, like any other user I can at least backup my data. I gather links and various information in my Twitter likes, so this data is particularly important to keep for future reference.

The process is fairly straightforward: on the home page in the left-hand menu under ‘More’ go to ‘Settings and privacy’, then ‘Your account’ and finally ‘Download an archive of your data’. After confirming with your password and a verification code by email, Twitter will start generating the archive and will notify you with an in-app notification when it’s available for download. You receive a .zip file which can be quite large and contains a lot of files – mine is over 900MB and has more than 12,000 files.

Custom number formats in Excel (and their pitfalls)

One of the more powerful Excel features that is relatively underused by most people – at least in my experience – is custom number formatting. It allows end users to change how numeric values are shown in the cell without changing the underlying data. This in turn enables Excel to perform calculations and link formulas to these cells, while at the same time displaying different information that better conveys its purpose.

Basic number formats are front and center in the Excel interface on the ‘Home’ tab, as they power the various options to change numbers to ‘Currency’, ‘Accounting’, ‘Percentage’, even ‘Scientific’ and ‘Date’ and ‘Time’. Ironically, I think this is partially the reason why the feature is somewhat obscure: people are using the basic version so frequently, they never think there’s a more advanced and powerful incarnation waiting to be discovered.

Better AutoFit for Excel with VBA

After my annoyance that Excel couldn’t unhide multiple sheets finally got addressed last year, I discovered another area where Excel could use some improvement. This one concerns the AutoFit feature and can also be easily solved with a short VBA code. In my current job I started working with Excel files with considerable number of columns of various widths. This makes reading and navigating sheets cumbersome, as some columns are too narrow and hide important pieces of text in their cells, while others can be very wide so that users waste time scrolling horizontally to get from one place to the next. Theoretically, the Excel AutoFit feature should help, but this only fixes the first issue by making narrow columns wide enough to fit their entire contents, while at the same time exacerbating the second. What I would actually need is to run the AutoFit algorithm but restrict the maximum width to some value of my choosing, thus preventing columns from getting exceedingly wide.

Being more productive with the Excel Quick Access Toolbar

I have been meaning to share more tips and tricks for working with Microsoft Excel on my blog, since this constitutes a large portion of my daily activity, but there always seems to be something more pressing to write about. One of my favorite productivity features in more recent versions of Excel – if you can still call the Office Ribbon, introduced in Excel 2007, ‘recent’ – is the Quick Access Toolbar. Usually merged with the Title bar, the Quick Access Toolbar comes prepopulated with a small set of tools like Save, Undo and Redo, which you can customize to your needs.

Ars Technica: “Disable the Windows print spooler to prevent hacks, Microsoft tells customers”

An elevation of privilege vulnerability exists when the Windows Print Spooler service improperly performs privileged file operations, Microsoft wrote in Thursday’s advisory. An attacker who successfully exploited this vulnerability could run arbitrary code with SYSTEM privileges. An attacker could then install programs; view, change, or delete data; or create new accounts with full user rights.

Microsoft said that the attacker must first have the ability to execute code on a victim’s system. The advisory rates in-the-wild exploits as “more likely”. Microsoft continues to advise that customers install the previously issued security updates. A print spooler is software that manages the sending of jobs to the printer by temporarily storing data in a buffer and processing the jobs sequentially or by job priority.

The workaround for this vulnerability is stopping and disabling the Print Spooler service, Thursday’s advisory said. It provides several methods customers can use to do so.

Dan Goodin

I went ahead and disabled the print spooler on my personal laptop, thinking I would not need it on a regular basis, but in the process I stumbled upon one of the weirder and more unexpected dependencies in Microsoft software. Apparently, since Excel 2010, charts located on separate chart sheets somehow rely on printer drivers for their dimensions. With the print spooler stopped, the system behaves as if no printer is installed and because of this Excel chart areas default to a fixed size and cannot be resized! The result is a weird looking mini-chart, sometimes with distorted text.

Export Excel charts as SVG files

Speaking of Excel and its numerous use cases, there is a certain missing feature that I have wanted for years: the ability to export charts in SVG format. A vector-based file format, SVG is particularly well suited to render charts and scale them to almost any resolution, because it redraws chart components to match the new size instead of enlarging static pixels, as regular image formats do. There were complicated workarounds involving exporting to PDF first and then extracting charts with third-party software, but I always found them too cumbersome for regular use.

But during the last quarter of 2019, the process became a lot simpler after Office 365 added an option to convert PowerPoint slides to SVG files. To convert an Excel chart instead, all you need to do now is to copy that chart into a blank PowerPoint presentation and save that slide as SVG! I have used this method several times for my blog, both for the articles related to the coronavirus pandemic, as well as for other articles with charts and visualizations.

Show Excel AutoComplete list with a keyboard shortcut

Microsoft Excel is an immensely complex piece of software with so many features that sometimes even basic actions can confuse people. Despite working in Excel for almost two decades, I still manage to uncover new tricks that I never imagined were possible before. In this case it is a keyboard shortcut I picked up from a colleague, but I find it very useful and easy to use.

When entering data in cells, Excel can suggest values from the same column in the form of AutoComplete: start typing and after the first few letters Excel will show a matching suggestion which you can fill in the cell by typing Enter. Sounds great in theory, but in practice you will find many situations where suggestions fail to show up, especially in large sheets with multiple similar entries or blank rows. What I discovered from my colleague was that the keyboard shortcut Alt + ⬇ (down arrow) opens a drop-down list below the cell containing unique values from the table, and you can simply pick a value either with the mouse or the keyboard.

Adding labels to Excel scatter charts

Despite the impossibly long list of features and settings in Microsoft’s Excel, the most popular spreadsheet suite still lacks some features that seem trivial, or they require complicated workarounds and possibly some knowledge of VBA. One omission that annoyed me several times is the fact that in XY scatter (and bubble) charts you can’t automatically label data points with text from a third column; it’s only possible to show the value of the X or Y point. Until recently, custom labels required either a lot of manual work to change the default text, which was not sustainable for large amounts of data, or the use of macros or third-party add-ons. But, while working on the chart for my previous article, I discovered that, at least in Office 2013, this option has finally been added to the user interface!

Use the current sheet name in Excel formulas

A couple of weeks ago I was working on a series of reports at work when I came across an interesting Excel problem: the worksheets – and they were many, a couple of dozen at least – each contained the name of the sheet as title for the current report. So each time I created a new sheet and started a new report I had to change the title in a cell and to rename the sheet with that title. It would seem like a minor thing, but doing that dozens of times is tedious, not to mention you could forget to change the title on some sheets. I soon started to wonder if there is a way to do this automatically…

Naturally, there is no built-in function to copy the name of a sheet in a cell in Excel, but you can build one using Visual Basic. At first I tried using the property ActiveSheet.Name, but I soon discovered that it has an interesting side-effect: because it uses the active sheet, if you were to link to the result from another sheet it would show the name of the sheet you are currently viewing instead of the sheet where the original formula was placed. After some more digging around online I found a solution that works properly. The final code is below:

Unhide multiple sheets in Excel

Microsoft Excel has hundreds of functions and commands, but even so there are some things the Microsoft engineers failed to include. One of my recurrent annoyances is the lack of a command to unhide more than one sheet at a time. Hiding several sheets at once is very easy: you just select them by holding down the Ctrl key and then hide the group from the Format menu. The unhide option on the other hand presents you with a list of hidden sheets where you can only select one item to display it again.

When working with large files and lots of sheets, I prefer to hide the sheets where data is stored or where intermediary calculations are performed, and leave only the final results and charts visible to improve the readability of the file. But Excel makes it hard to bring back these sheets if you want to edit them again.

Control when links are updated in Excel

In a corporation, working with large data sets usually means working with Microsoft Excel. But things get a little tricky when you begin splitting the data into multiple files, located on the corporate intranet. My biggest annoyance here is how Excel stubbornly insist on asking me every time if I want to update the fields linked to other files. I have learned from experience it’s best not to do that; it’s often slow, keeping you from doing what you first set up to modify in that file, and secondly most of the time it reports not finding the right files. In short, a big waste of time!

Google Search features links to Spreadsheets

When you search for non-web based files, like documents, presentations, spreadsheets, etc., the Google results feature a link to a web-friendly version of the data. I noticed that this page is enhanced with several useful links, at least for Excelfiles (.xls and .xlsx).

Insert multiple blank rows between existing rows in Excel

Microsoft’s Excel is an indispensable tool for the work place, thanks to it's multitude of features, and will no doubt remain so at least for the near future. I also use it on a regular basis and generally consider myself an advanced user. Even so, given it’s complexity, it never hurts to find out new tricks to speed up your workflow or to change it altogether.

One of my sources of information is the Microsoft Office blog at TechRepublic. One of their recent articles detailed a workaround for adding several blank rows between rows already filled with data at once, without repeating the right-click-Insert routine for each new row. While the idea is ingenious, it’s by far not the easiest way to do this in Excel.