I recently had a long term collaboration with a number of colleagues, where part of what we did was produce, edit, and agree upon finalized versions of text. We applied various formatting such as color, cursive, and bold to track the status of specific text blocks. At the end of it all, we needed to collate the disparate pieces of text into a single document.
As a union steward, one of the many things I do on a regular basis is evaluate key indicators of the company financials. I do this by plotting a few numbers from the accounting reports into Excel, and then run them through some standardised analysis to identify the performance of key indicators. These indicators are evaluated on a five-point scale ranging from “unsatisfactory” to “very good”, and I have set up a total of seven rules using Conditional Formatting to allow me to read the indicators at a glance. Here’s how it’s set up:
If I’ve been there once, I’ve been there a thousand times; I’ve got a spreadsheet in excel with varying widths and heights that don’t fit the data. Now, sure, you could go in and correct it manually – but that’s not only boring, it’s a pain it the behind. Luckily, there are keyboard shortcuts to accomplish it. Here’s how:
If you’ve ever attempted to merge cells that hold data in Excel, you will almost certainly have encountered this warning:
I need to calculate what percentage one number is of another on a relatively regular basis. Luckily, it is relatively easily done using functions in Excel. Here’s how:
While analysing an Excel sheet the other day, I needed to combine two columns so that I could get names in the format
firstname lastname (the list had been provided as
lastname,firstname). As it happens, doing so is relatively easy. You do it on a cell by cell basis, by using basic excel commands.
If you do a lot of work in Excel, odds are you’ve needed to calculate a percentage change. Luckily, Excel does this for you quite readily if you simply apply a basic formula of
(New Value - Old Value)/Old Value. There is, however, a pitfall that you’ll not get the values you expect if the cell where you’re entering the value is not set to percentage formatting. Hence, here’s a step to step way to make the calculation:
I have previously written about one of my many responsibilities; contacting users whose login credentials have shown up in breach lists. While the pertinent results of the breach files are delivered to me in a flat file, I use Excel’s Text to Columns feature to separate logins (usually email addresses) from the password. While this might take a little fiddling with the delimiters, it is worth it to ensure that you have a good basis on which to work.
I work a lot with text files containing data which is, to some degree or another, structured. Whether a breach file from a published breach, or the result of a powershell query such as Get-ADUser, Get-ADComputer, or Get-ADDirectReports, I need to separate the data into columns so that I can work with it. This is where the Text to Columns feature in Excel comes in handy.
Among my many areas of responsibility at work, is contacting all the users found in various data breach lists that our InfoSec team get their hands on (typically, these are the same lists that eventually make their way to HIBP). Not unsurprisingly, there is a significant amount of overlap between some of these lists, and one of the things I do is to ensure that I do not contact users about passwords I’ve already talked to them about.
Many companies, my employer included, uses spreadsheets on a more or less consistent basis in order to track such things as vacation planning and KPI reporting. For me, as an employee, keeping track of these spreadsheets can, from time to time, be a bit annoying. Much to my surprise, at some point in the past few years, Microsoft added a pin item feature to MS Office.