Excel Tips for Power Users

It’s been quite a while since I had the time to share some tips with you, my dedicated readers. Tech news was a little slow this week so what we have here are some tips for Excel Power Users and future Excel Power Users.

So here we go – try these tips out and give Excel a workout.

1. Use a shortcut to create a table. Tables are among the most useful features in Excel for data that is in contiguous columns and rows. Tables make it easier to sort, filter and visualize, as well as add new rows that maintain the same formatting as the rows above them. In addition, if you make charts from your data, using a table means the chart will automatically update if you add new rows.

If you’ve been creating tables from your data by going to the Excel ribbon, clicking Insert and then Table, there’s an easy keyboard shortcut: After first selecting all your data with Ctrl-A (command-shift-spacebar for Mac), turn it into a table with Ctrl-T (command-T on Mac).

Bonus tip: Make sure to rename your table to something related to your specific data, instead of leaving the default titles Table1 or Table2. Your future self will thank you if you need to access that information from a new, more complex workbook.

2. Add a summary row to a table. You can add a summary row to a table in the Design ribbon on Windows or the Table ribbon on a Mac by checking “Total Row.” Although it’s called Total Row, you can select from a variety of summary statistics, not just a total sum: count, standard deviation, average and more.

While you could certainly insert this information into a spreadsheet manually with a formula, putting the info in a Total Row means it’s “attached” to your table but will stay in the bottom row regardless of how you then might choose to sort your table data. This can be quite handy if you’re doing a lot of data exploration.

Note that you’ll need to create a total row for each column individually; creating a sum for one column won’t automatically generate sums for the rest of your table (since not all columns may have the same type of data — a sum for a column of dates wouldn’t make much sense, for example).

3. Easily select columns and rows. If your data is in a table and you need to refer to an entire column in a new formula, click on the column name. That will give a reference to the full column by name — useful if you later add more rows to the table, because you won’t have to readjust a more specific reference such as B2:B194.

Note: It’s important to make sure your cursor looks like a down arrow before you click on the column name. If your cursor looks like a cross when you do so, you’ll get a reference to just that lone cell, not to the whole column.

If you want to select an entire column that’s not in a table with just the cells that have data in them, put your cursor in a column next to it, hit Ctrl-down arrow, use the right or left arrow key to move to your desired column, and then hit Ctrl-Shift-up (use command instead of Ctrl on a Mac). This can be handy if your data column is quite long.

4. Filter table data with slicers. Excel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering. However, trying to filter data with that small drop-down when you’ve got a large number of items can be somewhat cumbersome. Several of the presenters at the Data Insights Summit suggest using slicers instead.

5. Create a summary cell that changes when you filter a table. If you create a cell outside a table that summarizes data within a table — the sum of a column, for example — and you’d like that cell to display an updated sum if you filter the table by something, a basic SUM formula won’t work.

Instead of simply using SUM in that cell, use the AGGREGATE function within your cell, and then your cell can be linked to your table filters.

aggregate 1a

Excel’s AGGREGATE function requires three arguments, two of which are numbers. Excel for Windows offers lists of available options.

AGGREGATE requires three arguments: A function number, a desired option number and the range of cells you want to operate on. Type =AGGREGATE( in Excel for Windows and you’ll see the available functions and options; in Excel for Mac, you’ll have to click on the AGGREGATE help function in order to see available function and option numbers.

SUM is function number 9; ignore hidden rows is option 5. So, a cell with the following code:

=AGGREGATE(9,5,Table1[Expenditures])

gives you the sum of all visible rows only. If a filter changes which rows are visible, your sum will change accordingly.

aggregate2

AGGREGATE offers the option of summarizing only visible rows.

6. Sort data in a pivot table. Sometimes you’d like to sort data by a specific column in a pivot table — just as with a regular table. But unlike regular tables, pivot tables don’t have dropdown menus on each column offering the ability to sort. However, if you choose the lone dropdown arrow on the first column, you’ll get a menu allowing you to sort by any column.

7. “Unpivot” data. Some call this reshaping data from “wide” to “long”. In the database world, it’s known as “fold”: Taking data from individual columns and moving them into rows. Basically, it’s the opposite of creating a pivot table — in a pivot table, you pull categories within one column up into their own columns.

To unpivot columns, you need to use the Query Editor in Excel 2016. Access the Query Editor via the Data ribbon: In the Get & Transform section, choose From Table.

Once the Query Editor comes up (if your data isn’t already in a table, you’ll be asked to confirm a data range first), select the columns you want to unpivot, click on the Transform tab and chose Unpivot Columns.

unpivot1

Excel’s Query Editor provides users with the option to unpivot columns.

That will create two new columns at the right of your spreadsheet, Attribute and Value, with the columns you unpivoted. You can rename those columns to something that makes more sense, such as “Product” and “Price” or “Quarter” and “Revenue.”

To save your work, select File > Close & Load (to the default destination) or File > Close & Load To in order to be asked where you’d like to save your results. If you try to close without saving, you’ll be asked whether you want to keep your changes; say Yes and they’ll be saved on a new worksheet.

unpivot2

Unpivoting data turns a wide table into a longer one, combining multiple columns into two: attribute (category) and value.

The Microsoft Office website has more information on unpivoting.

8. Make multiple pivot tables for one column of categories. If you have a pivot table and add a filter for one column that contains categories, you can generate copies of that pivot table, one for each category in your filter, by going to Analyze > Options > Show Report Filter Pages and then selecting the filter you want. This can be handier than having to click through each category in your filter manually.

(On Excel 2016 for Mac, go to the PivotTable Analyze tab on the Ribbon and choose Options > Show Report Filter Pages.)

9. Look up data with INDEX MATCH. While VLOOKUP is a popular way to find data in one Excel table and insert it into another, INDEX combined with MATCH can be more powerful and flexible. Here’s how to use them.

Let’s say you have a lookup table where column A has computer model names, column B has price information, and column D also the name of a computer model where you want to add price info. Create a formula using this format:

=INDEX(ColumnToSearchForValue, MATCH(CellWithLookupKey, ColumnToSearchForLookupKey, 0)

A sample might look like:

=INDEX(B2:B73, MATCH(D2, A2:A73, 0))

This is how/why INDEX MATCH works (if you don’t need to know, skip to the next tip): INDEX selects a specific cell by numerical location. You first give it a range of cells, either within a single column or a single row, and then tell it the specific number of the cell you want.

For example, you could pick the 6th item in column B with:

=INDEX(B2:B19, 6).

You’d be using the following format:

=INDEX(ColumnOrRowToSearch, ItemNumberInThatColumnOrRow)

However, using INDEX alone isn’t much help if you want to find a value based on some condition in another column. That is, you don’t want the 6th item in your Price column B; you want the item in your Price column that matches something in column A, such as a certain computer model.

That’s where MATCH comes in. MATCH searches for a value in a range of cells and returns the location of what’s matched, using the following format:

=MATCH(SearchValue,RangeToSearch,MatchType)

(Match type can either be 0 for exactly equal, 1 for largest value less than or equal to what you’re searching for or -1 for the smallest value that is greater than or equal to your lookup value.)

So, if you wanted to find the location of a cell in column B that was exactly 999, you could use:

=MATCH(999, B2:B79, 0).

And, so the combination: MATCH, looking for a specific value based on a search term, returns a cell location; and INDEX needs a location as its second formula argument.

0. Watch a formula be evaluated step by step (for Windows only). Have a complicated formula? If you want to see how it gets evaluated, go to Formulas > Evaluate Formula to see the calculations run step by step.

11. Import and refresh data from the Web into Excel.This works best when you’ve got well-formatted HTML tables on a Web page; with more free-form text (or even poorly formatted tables), you’ll need to do a fair amount of additional editing to get your data into a form you can analyze.

With that warning in mind, if you want to pull an HTML table from the Web into Excel, head to the Data tab on Excel for Windows and select: New Query > From Other Sources > From Web

Enter the URL of the appropriate Web page. Excel will look for and list available HTML tables on that page. Click on a table to see a preview; when you find the one you want, click Load.

Why not just copy and paste a well-formatted HTML table into Excel? If the data updates frequently, you can easily refresh it by right-clicking in the table and selecting Refresh instead of having to copy and paste new data.

These Excel Tips and more can be found at computerworld.com.

Stopping Windows 10

First I must say I am a fan of Windows 10 however there are many out there who are simply not ready to replace the excellent and comfortable Windows 7 with 10. Windows 10 is going to be an excellent operating system but there is no mistaking that many are simply not ready for it. If you are among these PC users check this out.
A new free tool, dubbed Never10, provides the user a one-click solution to disable Windows 10 upgrade until the user explicitly gives permission to install Windows 10.
Never10 has been developed by Steve Gibson, the well-known software developer and founder of Gibson Research, which is why the tool is also known as “Gibson’s Never10.”
How to Disable Windows 10 Upgrade on Your PCs
  1. Go to Gibson’s Never10 official site and click on the Download.
  2. Once downloaded, the program detects if the upgrade to Windows 10 is enabled or disabled on your system and then shows a pop-up. If enabled, Click ‘Disable Win10 Upgrade’ button.
  3. You’ll again see a pop-up that now shows Windows 10 upgrade is disabled on your system, with two buttons to ‘Enable Win10 Upgrade’ and ‘Exit.’ Click on Exit button.
That’s it, and you have successfully disabled Windows 10 Upgrade on your PC.
Here’s the kicker:
The best part of this tool is that you don’t have to install an application on your PC to do this. Gibson’s Never 10 is an executable. So you just need to run it, and it doesn’t install anything on your computer. You can delete it when you’re done.

“The elegance of this ‘Never 10’ utility is that it does not install ANY software of its own. It simply and quickly performs the required system editing for its user,” Gibson writes on his page about the new utility.

According to Gibson, Never10 will be a great help to inexperienced users while advanced users will likely appreciate the fact that no additional software is installed and will be able to refer their family and friends to this easy-to-use utility.

For more technical details on how this tool works, you can head on to this link.

Unlike other available Windows 10 blocker tools, Never10 blocks the Windows 10 upgrade, but at the same time, the tool allows you to start the update process in case you change your mind, according to Windows watcher Paul Thurrott.
However, the primary purpose of Gibson’s Never10 is to prevent Windows 7 and Windows 8.1 operating system from being upgraded to Windows 10. As Gibson says:

“Many users of Windows 7 and 8.1 are happy with their current version of Windows and have no wish to upgrade to Windows 10.”

“There are many reasons for this, but among them is the fact that Windows 10 has become quite controversial due to Microsoft’s evolution of their Windows OS platform into a service which, among other things, aggressively monitors and reports on its users’ activities.”

Moreover, just a month ago, Microsoft was caught displaying unsolicited advertisements on its Windows 10 users’ desktops.
These reasons are enough for many users to stay on their previous versions of the Windows operating system.

Microsoft Word Flow Arrives on iOS

Until voice-to-text texting is perfected, Microsoft is hoping that iOS users will be content with one-handed texting. Microsoft has released a new keyboard as an iOS app that not only enables one-handed texting but also provides the ability to predict the next words users want to write.

The Word Flow app was developed by Garage, Microsoft’s in-house experimental projects division, and is now available in Apple’s App Store for users in the U.S. It was made available for beta sign-ups earlier this month.

Word Flow was originally developed for Microsoft’s Windows Phone platform and is also available for desktop users via the Windows 10 operating system. It comes on the heels of the Hub keyboard, another recent offering from the Garage division. The Hub keyboard, which is available now for iOS devices, acts as more of a multitasking and productivity tool, letting Office 365 users share and search for documents from OneDrive, SharePoint and other Microsoft services.

Game Changer?

Independent technology analyst Jeff Kagan told us that if Word Flow is successful, it could be a game changer for mobile device owners. “Users love the iPhone, but hate it being so large they now need to use two hands to do anything,” said Kagan. “Texting used to be so easy, then devices enlarged, making texting a two-hand activity.”The Word Flow app differs by promising users faster texting thanks to the ability to swipe input instead of typing it. The app’s one-handed typing mode is displayed by way of an arc-shaped keyboard, allowing users to employ only their thumbs to type as they hold their devices. Users can customize Word Flow’s background with one of the images included in the app, or use an image of their own. Microsoft plans to add more images, including arc-shaped images to fit with the keyboard’s design.

Kagan said he was surprised Apple didn’t find a way to bring its own one-handed texting app to its customers before Microsoft had the chance. Although Apple initially kept developers from offering system-wide keyboards on iOS devices, the tech giant was slow to add advanced features to the native iPhone keyboard, leaving a need for others to fill.

SwiftKey Integration

Apple opened up its mobile operating system to outside developers in 2014 with iOS 8. Around the same time, Microsoft began using app development as a way to gain traction in the mobile market. In February, Microsoft bought London-based keyboard maker SwiftKey, with plans to integrate SwiftKey’s next-word prediction technology into Word Flow.

Microsoft plans to continue offering SwiftKey’s own apps. When Microsoft bought SwiftKey, it had been installed on about 300 million devices across Android, iOS and other platforms.

If Microsoft’s Word Flow can be switched from one side to the other for right-handers or left-handers, and if it really works well with one hand, this could be…. a game changer!

Avoiding Email Scams with 10 Easy Tips

Recently I have been asked about a couple of suspicious email messages, which were both of course not legitimate messages but scams in which the sender, a truly bad guy was “phishing” in order to steal money from the receiver.

Every day countless phishing emails are sent to unsuspecting victims all over the world. While some of these messages are so outlandish that they are obvious frauds, others can be a bit more convincing. So how do you tell the difference between a phishing message and a legitimate message? Unfortunately, there is no one single technique that works in every situation, but there are a number of things that you can look for.

1: The message contains a mismatched URL

One of the first things I recommend checking in a suspicious email message is the integrity of any embedded URLs (or website addresses). Often the URL in a phishing message will appear to be perfectly valid. However, if you hover your mouse over the top of the URL, you should see the actual hyperlinked address (at least in Outlook). If the hyperlinked address is different from the address that is displayed, the message is more then likely fraudulent or malicious.

2: URLs contain a misleading domain name

People who launch phishing scams often depend on their victims not knowing how the DNS naming structure for domains works. The last part of a domain name is the very telling. For example, the domain name info.brienposey.com would be a child domain of brienposey.com because brienposey.com appears at the end of the full domain name (on the right-hand side). Conversely, brienposey.com.maliciousdomain.com would clearly not have originated from brienposey.com because the reference to brienposey.com is on the left side of the domain name.

I have seen this trick used countless times by phishing artists as a way of trying to convince victims that a message came from a company like Microsoft or Apple. The phishing artist simply creates a child domain bearing the name Microsoft, Apple, or whatever. The resulting domain name looks something like this: Microsoft.maliciousdomainname.com.

I have found that sadly this often works because most people trust companies like “Microsoft” and “Apple” so when long standing names like this are used people often let their guard down. The lesson here is to never let your guard down when it comes to email messages.

3: The message contains poor spelling and grammar

Whenever a large company sends out a message on behalf of the company as a whole, the message is usually reviewed for spelling, grammar, and legality, among other things. So if a message is filled with poor grammar or spelling mistakes, it probably did not come from a major corporation’s legal department.

4: The message asks for personal information

No matter how official an email message might look, it’s always a bad sign if the message asks for personal information. Your bank does not need you to send it your account number. It already knows what that is. Similarly, a reputable company should never send an email asking for your password, credit card number, or the answer to a security question.

5: The offer seems too good to be true

There is an old saying that if something seems too good to be true, it probably is. That holds especially true for email messages. If you receive a message from someone unknown to you who is making big promises, the message is probably a scam.

6: You didn’t initiate the action

Just yesterday I received an email message informing me I had won the lottery! The only problem is that I have never-ever bought a lottery ticket. If you get a message informing you that you have won a contest you did not enter, you can bet that the message is a scam.

7: You’re asked to send money to cover expenses

One telltale sign of a phishing email is that you will eventually be asked for money. You might not get hit up for cash in the initial message. But sooner or later, phishing artists will likely ask for money to cover expenses, taxes, fees, or something similar. If that happens, you can bet that it’s a scam.

8: The message makes unrealistic threats

Although most of the phishing scams try to trick people into giving up cash or sensitive information by promising instant riches, some phishing artists use intimidation to scare victims into giving up information. If a message makes unrealistic threats, it’s probably a scam. Let me give you an example.

Just recently a workmate received an official looking email that was allegedly from a co-worker. The email went on to ask for our “account number” and “routing number”. Although it appeared to be an email from one staffer to another staffer the email originated from a hidden domain and as I mentioned in Tip #3 the spelling and grammar was poor.

Also – As I mentioned in Tip #4 – legitimate companies will not ask for sensitive information by email and you – of course should never-ever send this type of information via email.

9: The message appears to be from a government agency

Phishing artists who want to use intimidation don’t always pose as a bank. Sometimes they will send messages claiming to have come from a law enforcement agency like the IRS, the FBI, or just about any other entity that might scare the average law-abiding citizen.

I can’t tell you how government agencies work outside the United States. But here, government agencies do not normally use email as an initial point of contact. That isn’t to say that law enforcement and other government agencies don’t use email. However, law enforcement agencies follow certain protocols. They do not engage in email-based extortion.

10: Something just doesn’t look right

In Las Vegas, casino security teams are taught to look for anything that JDLRjust doesn’t look right, as they call it. The idea is that if something looks off, there’s probably a good reason why. This same principle also applies to email messages. If you receive a message that seems suspicious, it is usually in your best interest to avoid acting on the message.

Amazon Prime Gets Even Better

In many ways Amazon Prime has become my go-to streaming service. Of course Netflix has some outstanding original content (which I enjoy) however Amazon Prime has continued to regularly improve its service since it’s launch just a few short years ago.

Amazon Prime is the ultimate product bundling deal imaginable because it combines several service including physical free shipping digital subscriptions for music, videos, and more. This week Amazon took an aggressive step to better compete with standalone subscription services (like Netflix & Hulu) by offering a new variety of Prime offerings by the month.

I have happily subscribed to Amazon prime for years. With this subscription, you can get most Amazon.com purchases delivered to your home in two days for free plus an astonishing array of digital services, which include:

Amazon Video. Unlimited access to movies and TV shows in SD, HD, Ultra HD & HDR (where possible).

Amazon Prime Music. Unlimited streaming from Amazon’s music service, which includes ad-free and personalized radio stations. This is replace Apple Music & iTunes for me.

Free Kindle books. As a Prime subscriber, you get access to over 1 million Kindle e-books for free, plus the Kindle Lending Library, which lets you borrow one free e-book per month.

But the problem with Prime is that it is only available in a one-year subscription, whereas competing standalone services are all available month-to-month. To close this gap, and convince potential customers that the wide swath of Prime perks is worth consideration, Amazon is now offering two monthly Prime subscriptions. They are:

Amazon Prime (Monthly). This is the annual plan, but month-to-month. That is, you get all of the perks of the yearly Amazon Prime membership, but pay $10.99 per month instead of $99 per year. (So it’s cheaper to get the annual subscription if you can afford it and know you’ll use it all year long.)

Amazon Prime Video. Finally available as a standalone subscription service that can take on Netflix and Hulu, Amazon Prime Video costs $8.99 per month, undercutting those services when you compare functionality. Netflix costs $7.99 to $11.99 per month (where only the most expensive versions offers HD and or Ultra HD) and Hulu is $7.99 (with limited commercials) to $11.99 per month (no commercials).

prime-choices

If you go with the yearly Amazon Prime subscription, and then take advantage of the shipping perk, that is still the best choice. But this move with a monthly subscription service signals that Amazon is serious about keeping its digital media services both competitive and desirable.

RIP Quicktime for Windows

QuickTime for Windows has dangerous security vulnerabilities that let attackers take over your computer, but Apple refuses to provide updates to fix them. Because of this it is time to uninstall it.

Annoyingly, Apple only announced this in a statement to Trend Micro. Apple hasn’t announced this on its own website, and doesn’t seem to be making a real effort to warn people about this outdated program. Apple is still offering it for download on their website with no warning which is inexcusable.

If you’re using a Mac, don’t worry. Apple’s QuickTime for Mac is still supported with security updates. Only the Windows version is dead and dangerous.

Why You Should Uninstall Quicktime for Windows… Now

QuickTime for Windows is vulnerable to two security attacks that would allow an attacker to run code on your computer if you visited a web page or played a downloaded file. It’s particularly exploitable thanks to its browser plug-in. If you’re using Internet Explorer or Mozilla Firefox, which still support the plug-in, you can be compromised just by visiting a web page. Google Chrome no longer does supports these old plug-ins, but Chrome users shouldn’t get too complacent. Even downloaded video files could exploit the desktop version of Apple’s QuickTime.

Unfortunately, Apple is no longer updating QuickTime for Windows, so these flaws–and any future ones–will never be fixed. QuickTime for Windows will just become less and less secure over time.

This is the same stunt Apple pulled with Safari for Windows. Apple simply stopped updating its Windows application without properly informing its users. Although Apple hasn’t asked directly asked you to stop using QuickTime for Windows, you definitely should. Even the US government is advising this.

Follow These Steps

To uninstall QuickTime, open the Control Panel, click “Uninstall a program” under Programs, select “QuickTime” in the list, and click “Uninstall.” The QuickTime uninstaller will remove both the QuickTime desktop application and the QuickTime browser plug-in. If you don’t see QuickTime in the list here, you don’t have QuickTime installed. Problem solved!

A Bad Apple

Apple’s Windows security has been beyond bad. While Apple is battling the US government over the security of its iPhones, Apple can’t even be bothered to inform users of QuickTime for Windows–and Safari for Windows before it–that they’re using old, out-of-date software that won’t receive security updates. Apple should be handling this a lot better.

Verizon Workers On Strike

This past Wednesday nearly 40,000 East Coast communications and electrical workers from Massachusetts to Virginia went on strike. Because of this Verizon has put a temporary hold on new fiber optic network installations and activated non-union employees and business partners to help with customer service.

Negotiations have been going on for 10 months and have gone nowhere between members of the Communications Workers of America and the International Brotherhood of Electrical Workers. Verizon of course already had contingency plans in place and were “ready” when workers called a strike this week. The striking unions say they have been unable to reach agreements with Verizon over issues including the offshoring of jobs, use of lower-wage contractors over union employees and extended job transfers that require workers to spends months away from their families.

A Changing Infrastructure 

This strike and the failed negotiations (to this point) really demonstrates the changing  telecommunications infrastructure. The change from a wireline legacy to fiber (fios) is bringing incredible changes to the way we communicate with each other and of of course the Verizon workforce is struggling to adapt.

As mobile and wireless services have grown, telecoms have struggled with their legacy wireline businesses. In 2014, for instance, Verizon reduced its wireline business workforce by 2,300 with the aim of improving efficiency.

Despite changes in the industry, however, Verizon has continued to see wireline revenues grow. Fueled in part by its Fios fiber-to-the-home network service, Verizon’s wireline business revenues totaled $16.1 billion in 2015, compared to $15.6 billion in 2014.

The Strike’s Impact – Today

Rather than putting in equipment for new customers now that union installers and other workers are on strike, Verizon has stated that their current focus is on “serving our existing customer base.” This more then likely means new fios installs will be on hold – or delayed at the very least.

A Matter of Politics

To make this situation even worse the dispute between Verizon and the unions has become a political issue. Last month, 20 members of the U.S. Senate sent a letter to Verizon chairman and CEO Lowell McAdam urging the company to “work toward progress in the current round of collective bargaining with its unions.” And this past week, both contenders for the Democratic presidential nomination — Bernie Sanders and Hillary Clinton stopped at several strike locations to voice their support for the workers.