• To generate the two sample heat maps shown above, we started with this (details of the number of bonuses each employee received each month):
• And this (how many goals were scored from a certain location on a soccer pitch):

• To create the soccer pitch in Excel, I inserted it as a PNG image, meaning the cells underneath the graphic remained visible. You can do the same with any image outline to create a heat map in Excel.

Apply conditional formatting

• The next step is to apply the color scales to your data. First, select all the cells that will form the heat map. In the example below, I’ve selected all the cells on the soccer pitch, so that any data I might add later on will also be picked up by the color rules I set.

• If you are applying the conditional formatting to cells underneath an image, you’ll need to use your arrow keys to navigate to the correct cell, as you can’t select a cell underneath a graphic using your mouse. Then, hold Shift while using your arrow keys to select the relevant cells.

• Next, click “Conditional Formatting” in the Home tab on the ribbon, and hover over “Color Scales.” From there, you can choose the color scale that works best with how you want to display your data.
• In my case, I’ll choose the “Green To Yellow” scale.
• If none of the preset options pique your fancy, click “More Rules” instead. This will launch the New Formatting Rule dialog box, where you can switch to a three-color scale (rather than the default two colors), with more specific rules about how the values affect the colors to be displayed.

• To change or remove the color scale after you have applied it, select the cells again, click “Conditional Formatting,” and select either “Manage Rules” or “Clear Rules.”

Remove visible values and gridlines (Optional)

• The final step in optimizing your heat map involves hiding the figures and removing the gridlines, if doing so will improve your data visualization.
• To hide the figures, select the cells to which you applied the conditional formatting in the previous step. Then, in the Home tab, click the “Number Format” icon in the bottom corner of the Number group.
• Then, click “Custom” in the Category menu, and type ;;; (three semicolons) into the field box.
• When you click “OK,” the numbers will disappear from the cells, though you can still see them in the formula bar when you select the relevant cells.
• Removing the gridlines is much more straightforward. In the View tab on the ribbon, uncheck “Gridlines” in the Show group.

Like most people, I started with BlueStacks, but quickly grew sick of it (like most people). Its UI is tacky, and riddled with ads and sponsored content. The second most popular option is Genymotion, which emulates an entire mobile phone, much like the emulator that ships with Android Studio. However, booting up a virtual phone just to launch a phone is hugely annoying and a waste of time. You can’t even run games on it properly.

The perfect way to run Android apps should be seamless. By ‘seamless,’ I mean that you should be able to launch it directly from the taskbar, and it should open in its own window, just like any other Windows app. Also, I should be able to install apps directly from the Play Store with a single click, just like I can from the Microsoft Store. That’s exactly what Windows Subsystem for Android (WSA) does.

If you’ve heard of Windows Subsystem for Linux (WSL), it is the same concept at work here. Like WSL, Windows Subsystem for Android is a compatibility layer that allows you to run Android apps ‘natively.’

How to set up WSA on Windows

Before proceeding with WSA, make sure virtualization is enabled on your computer. Check Hyper-V and Virtual Machine Platform features in the Windows Features list.

Start by downloading the Windows Subsystem for Android build from GitHub. The build that reads ‘MindtheGapps’ has both the Play Store and the Amazon Store. If you just want the Play Store, download the build with the flag ‘Removed Amazon’.

Extract the archive and open the extracted folder. Scroll through the files and look for ‘install.ps1.’ Right-click on this file and click ‘Run with PowerShell’.

How to install apps with WSA

On the first launch, two windows should appear: one should be the Settings app for the emulated device and one should be the WSA interface. The WSA interface has tabs for ‘System,’ ‘Apps,’ and ‘Advanced Settings’.

The System tab has a shortcut for launching the file manager, so that’s where you’ll find your documents, downloads, and other files. The Apps section houses all your downloaded apps, as well as the Play Store and the Amazon Appstore. You can tweak performance settings in the Advanced Settings tab. You can also enable Developer Options here.

To install a new app, go to the Apps section and click the three dots next to ‘Play Store’ and click ‘Open.’ You’ll be asked to sign in with your Google account. Once you do that, the Play Store should load normally, and you should be able to install and update apps with one tap.

How to run and manage apps with WSA

Running installed apps is simple. You can either launch it directly from the Play Store page or find it in the Apps section of the WSA interface. They’ll also be available in the Start menu and the Windows app list. Click the three dots and wait for the app to launch in a separate window with its own icon on the taskbar. You can then close or minimize or maximize the app. Or, you can pin it to the taskbar by right-clicking on the icon.

It works just like any regular Windows app. You can use your mouse to point, click, and drag and use your keyboard for input control.

To remove an app, click the three dots next to its name and click ‘App Settings.’ It should open another window where you can change the app’s permissions, manage its storage, or remove it entirely.

Assuming you already have a Home Assistant server humming away quietly, the most important thing you’ll need for this to work is a smart plug that doesn’t rely on Wi-Fi. Because we’re restarting your router, plugs that depend on a wireless network connection won’t be able to receive the trigger to switch back on when your automation runs.

I’ve got a Zigbee network that uses IKEA smart plugs, but you could also use Matter over Thread, Z-Wave, or even a Bluetooth smart plug as long as it’s within range of a nearby adapter. We’ll be using the smart plug to power cycle our network equipment, and any non-Wi-Fi smart plug should work as long as your server remains up and running.

Though it’s not necessary, it’s also handy to have both your router and modem (assuming they’re separate devices) connected to the same smart plug via a power strip. This allows you to power cycle both with a single trigger, and means you only need to sacrifice a single smart plug. Alternatively, you can plug both devices into separate plugs and make sure you target both with your automation.

With your smart plugs paired, correctly labeled, and attached to your network equipment, it’s time to set everything up.

Home Assistant Connect ZBT-2

Detecting when your internet connection goes down

There are a few ways you can detect when your internet connection is not online using Home Assistant. Some prefer to use the Healthchecks.io cron job monitoring service with its associated integration (available via the Home Assistant Community Store). Personally, I keep it a little simpler than that.

If you’re lucky enough to have a router that is detected by Home Assistant, you can use that. My TP-Link Archer AX72 router was automatically detected by the platform’s core UPnP/IGD integration and offers two handy “WAN status” triggers that I can use in automations.

For the purpose of this guide, I ignored that in favor of a simple ping operation that should work with pretty much any router. As long as your Home Assistant server is connected to the internet, you can use this to detect your connection status. To be safe, we’ll use two separate ping requests to different servers to account for any remote outages.

To get started, head to Settings > Devices & services and on the “Integrations” tab, click “+ Add Integration” at the bottom of the screen. Search for the “Ping (ICMP)” integration and add it. When prompted, enter 8.8.8.8 as the host, then finish up.

Finally, head back to the Ping (ICMP) integration and click “Add service,” then enter 1.1.1.1 as the host name, then “Submit” and “Skip and Finish.” We now have Home Assistant sending a ping every 30 seconds to Google and Cloudflare’s DNS servers.

Build your reboot automation

Finally, let’s build our automation. Click on Settings > Automations & scenes, then click “+ Create automation” at the bottom of the screen. Hit “Create new automation” when prompted, then click “+ Add trigger” and select the “Device” trigger.

You can choose to use your router’s WAN connection status here, where available. Otherwise, use the “Select a device” menu to choose one of the two DNS entries we created (let’s say 1.1.1.1). Under the “Trigger” drop-down, choose “1.1.1.1 disconnected” and then under “Duration” add the amount of time you’d like to elapse before everything is rebooted. I chose two minutes to account for any brief drop-outs.

Now, under the “And if” section, click “+ Add condition” and choose “Device” from the list of conditions. Now add the other DNS entry we created (in this instance, that’s 8.8.8.8). Change the condition to “8.8.8.8 is disconnected” and leave the duration blank. The automation now requires that both services be unreachable before anything will happen.

Lastly, it’s time to create a reboot sequence. Under “Then do,” click “+ Add action” and choose “Device” as an action. Now select the smart plug that’s connected to your router and modem (or add both separately, if that’s the route you’re taking) and make sure the “Action” is “Turn off” (for both, if necessary). You’ll need to use the “+ Add action” button again to add a second plug.

Now, click “+ Add action” and search for “delay” and add the “Wait for time to pass” action. Specify how long you’d like your equipment to remain off before the reboot sequence is triggered. I went for 10 seconds, but you might prefer to wait around 30.

Lastly, use the “+ Add action” button to add the same smart plug (or plugs) with a “Turn on” action (for both). This will restart your router.

Avoiding boot loops

A boot loop could occur where your Home Assistant server keeps power cycling for as long as things appear offline. In testing this automation (which depends on the Ping (ICMP) integration), this didn’t happen. My automation required that the servers come back online before it would trigger again.

If you find that you’re getting a boot loop, you could edit your automation and use the “+ Add action” button in the “Then do” section to disable the automation after you send the “Turn on” command at the end. You can then create another automation that turns the reboot automation back on when both ping requests report a “Connected” status.

If your router or modem is routinely causing problems, it’s probably time for a new one. I find that most consumer-grade routers tend to burn themselves out after a few years, especially routers provided by ISPs.

One of the few useful upgrades that Microsoft included in modern versions of Windows is Phone Link. It’s a built-in app that lets you sync an Android phone or tablet with a Windows PC and control it from there. The setup is pretty simple.

Start by looking up ‘Phone Link’ in the Start menu search and launch the Phone Link app. On the first launch, Phone Link will ask which type of device you want to sync. I’ll choose Android. Make sure you’ve installed Phone Link on the target Android device.

Phone Link might ask you to verify the connection with the PC’s password before proceeding. Next, Phone Link will present you with a QR code that you need with the Android device. Open the camera app or a dedicated scanner app to scan this QR code.

If everything goes well, you should see a welcome message and a checkbox to automatically launch the Phone Link app on startup. Uncheck this box if you don’t want the PC to automatically start a Phone Link connection on boot.

By default, Phone Link will show you the battery status, recent notifications, and media playback controls (if something is playing on your phone). You can grant additional permissions to view and send text messages, make and receive calls, and browse recent photos. You can also ping the phone from your PC which plays a sound on your phone for 20 seconds (helpful if you can’t find it)

The Phone Link app on your Android also has a dedicated button for sending files which should appear in the Downloads folder of your PC.

There is a cross-device copy-paste feature available in Phone Link, but it doesn’t work with every Android phone. Only Samsung and a handful of Chinese manufacturers support this feature. If you want a more reliable way to sync the clipboard between devices or if you want even more features, read on.

Try KDE Connect for better support and more features

I have Phone Link connected with my Windows PC, but I use KDE Connect the most. It’s available on other platforms too, and it supports clipboard sync for any Android phone or tablet. KDE Connect also offers more controls than Phone Link.

Start by installing KDE Connect from the Microsoft Store or grab the installation package from KDE’s website. Next, open Google Play Store and install KDE Connect on your phone. The package is also available on F-Droid.

Open KDE Connect on both devices. You should see a list of devices available on your network on both devices. On either the phone or the PC, select the target device and hit ‘Pair.’ You’ll see a pairing request on the target device, grant it, and you’re good to go.

The KDE Connect interface provides a bunch of tiles on the PC and the phone. It’s less visually appealing than Phone Link, but you can do more. For example, you can send text you’ve copied between the two devices with the ‘Send Clipboard’ tile. Also, KDE Connect automatically sends any text you copy to the phone for quick pasting. If you use Gboard, the copied text instantly shows up as a chip on the top row.

You can also send files back and forth with ease. There should be a ‘Send File’ button on both devices, which lets you share multiple files with your PC. You can also send files from the PC to the phone, but only one at a time.

KDE Connect also features a bunch of other utilities you might appreciate. You can use your phone as a presentation remote to control slides on a PC. The KDE Connect phone app can serve as a control for media playback. You can use your phone as a cursor and keyboard for the PC. You can also sync notifications between devices, sync contacts, and send or receive text messages from your PC.

• First, open the “Settings” app from the “Home” tab on the home screen
• Now go to “Security & Privacy”
• Select “Apps From Unknown Sources”

Find “Silk Browser” and then toggle on “Allow From This Source.” This is what will allow us to install an app from outside of the Amazon app store. With that out of the way, we can start downloading the Play Store files. There are four APK files we will need to get the Play Store up and running, and they’re specific to your Fire Tablet.

To find out which Amazon Fire Tablet model you have, go to Settings > Device Options > About Fire Tablet. You’ll see your “Device Model” name here. To see your Fire OS version, go to Settings > Device Options > System Updates.

With the device model in mind, we can download the appropriate files below. Simply copy and paste the links from the tables below into the Silk Browser on your Amazon Fire tablet. We’re just downloading the files at this point, don’t open them yet.

Google Account Manager

Google Services Framework

Google Play Services

Google Play Store

Install the Play Store

With all the downloaded APK files to your Amazon Fire Tablet, we can begin installing them one by one. Open the “Files” app from the home screen.

• Select “Downloads” from the side menu and switch to the list view for the files. You should see the four files we just downloaded.
• It’s important to install these APKs in a specific order. For each APK, follow this process: Tap the file > select “Continue” > tap the “Install” button. After it installs, tap “Done.” Don’t open the Play Store yet.

Install the files in this order (the file names on your device will be longer):

1. com.google.android.gsf.login
2. com.google.android.gsf
3. com.google.android.gms
4. com.android.vending

With all the APKs installed, it’s time to reboot the tablet. Hold down the power button and select “Restart.”

If you’re using the Fire 7 2022 (12th Gen) or a newer Fire tablet running FireOS 8, you may need to manually grant permissions before using the Play Store. Tap and hold the Play Store icon and select “App Info.”

From the App Info page, select “Permissions”.Select both “Phone” and “SMS” and switch them to “Allow.” You can now proceed with the rest of the tutorial.

After following the above instructions, open the Play Store from the home screen and sign in with your Google account. Once you’re signed in, you’ll have a functional Google Play Store, just like on any other Android device. Go forth and download Telegram, Chrome, Gmail, and any other app you can’t find in the Amazon Appstore.

In the Start menu, you’ll find a Recommended section featuring ads for different apps. The last time I checked, it was showing an ad for a password manager. Microsoft is probably aware that not every Windows user will appreciate this new addition to the Start menu, which is why there’s also an option to remove the Recommended section entirely.

To do this, press Win+i to open the Settings app. Choose “Personalization” from the left sidebar, then select “Start” on the right. Turn off the toggle next to “Show recommended files in Start, recent files in File Explorer, and items in Jump Lists.” After that, restart your system, and you’ll see that the Recommended section is gone from the Start menu.

Remove ads from File Explorer

There’s plenty of vacant space in Windows File Explorer, and perhaps that’s why Microsoft thought it would be the best place to display ads in Windows. The first time Microsoft tested this idea was in 2017, when a large OneDrive banner appeared at the top of File Explorer in Windows 10. However, for some reason, it was later removed from Windows.

Since 2022, when an X user named Florian Beaubois spotted ads in the Windows 11 File Explorer, Microsoft has been constantly pushing ads in its file manager.

If you want a completely ad-free File Explorer experience, you need to change one setting in the Folder Options. To do this, open File Explorer, click the ellipsis icon at the top, and choose “Options” from the menu that appears. Switch to the “View” tab, uncheck the “Show Sync Provider Notifications” box, and then click “Apply” and “OK” to save the changes.

Disable tips and suggestions from Windows

In addition to ads, Windows also shows a lot of suggestions and tips to improve your overall experience. While these tips can be helpful for someone who’s just getting started with Windows, if you’ve been using Windows for a while, you might find them as annoying as the ads. That’s why it’s best to turn them off completely for a clutter-free Windows experience.

To do this, open the Settings app, select “System” from the left sidebar, and click “Notifications” on the right. Under the Notifications From Apps and Other Senders section, click “Additional Settings.” Turn off the boxes next to “Get tips and suggestions when using Windows” and “Suggest ways to get the most out of Windows and finish setting up this device.”

After disabling these boxes, you will no longer see occasional pop-ups from Windows asking you to switch to Microsoft Edge, join Microsoft Rewards, or use any other Microsoft services.

Remove ads from the Lock Screen

I’ve long been an admirer of the Windows Spotlight feature. It dynamically changes lock screen images, ensuring that I’m not looking at the same picture every time. However, there’s one major drawback to using the Windows Spotlight feature.

You’ll see ads on your Windows lock screen if you have Windows Spotlight set as the display option. These ads often promote different games, and sometimes Microsoft also shows tips on various topics. The only way to remove ads from the lock screen is to configure Windows to show either a picture or a slideshow instead of Windows Spotlight.

To do this, open the Settings app, choose “Personalization” from the left sidebar, and click the “Lock Screen” option on the right. Click the drop-down icon next to “Personalize Your Lock Screen,” and then choose either “Picture” or “Slideshow.” If you select Picture, make sure to turn off the “Get fun facts, tips, tricks, and more on your lock screen” box.

Remove ads from the Settings app

The very place where you’ve been disabling ads till now ironically shows plenty of them itself. Yes, you guessed it right; I’m talking about the Settings app. You’ll often find ads for different Microsoft services appearing at the top of various pages in the Windows Settings app.

To remove ads from the Windows Settings app, open the Settings app, select “Privacy & Security” from the left sidebar, and click “Recommendations & Offers” on the right. Turn off the toggles next to these options: Personalized Offers, Improve Start and Search Results, Recommendations and Offers in Settings, and Advertising ID. Disabling the Advertising ID also ensures that third-party apps don’t show personalized ads based on your Windows activity.

Remove personalized ads from the Microsoft Store

When you search for an app on Microsoft Edge, you’ll often see recommendations and similar apps in the Microsoft Store. This happens because of the Microsoft Store’s personalized experiences setting, which allows the store to monitor your online activity to show relevant ads and app suggestions. Fortunately, you can disable this setting to stop the Microsoft Store from displaying personalized ads and recommendations.

To do this, open the Microsoft Store, tap the profile icon at the top, and choose “Store Settings” from the menu that appears. Turn off the toggle next to “Personalized Experiences.” Select “Confirm and Restart Store” from the pop-up window that appears.

Remove ads from Windows search

I always prefer to hide the Windows search from the taskbar. But if you’re someone who uses it regularly, you must have noticed that it shows a lot of ads.

Open Windows Search, and on the right panel, you’ll see ads for different games, a trending searches section, and a dedicated block highlighting what’s special about the current date. If you find these elements taking up too much space in Windows Search, you can completely remove them.

To do this, open Windows Search, click the ellipsis icon at the top, and choose “Search Settings” from the menu that appears. Turn off the toggle next to “Show Search Highlights.” Restart your computer, and you will no longer see ads in Windows Search.

Clear the Widgets panel

Although the widget panel doesn’t show any ads, it contains a long list of news stories. There are also plenty of other blocks with data related to the stock market and other content that may not be relevant to you. If you find all this distracting, you can either clear the widget feed or disable the widget panel altogether. However, I suggest doing the former, as the widget panel can be quite helpful in various situations.

To get rid of an irrelevant widget feed, click the cog icon at the top-right corner of the widget window and select “Show or Hide Feeds.” Then, disable the “Feeds” toggle.

Big datasets can be difficult to read, interpret, and analyze. Indeed, it can be difficult to track specific information and understand relationships between data points if you have to scroll through thousands of rows and columns, even if you use the Freeze Panes or Focus Cell tools.

However, converting a dataset into a PivotTable allows for simplified data exploration and analysis, instant insights, and quick comparisons. What’s more, PivotTables let you perform calculations without the need for complex formulas, and you can use slicers and timelines to only show the data you need at any given point.

You can create a PivotTable in Excel from an external data source, a table in your worksheet, or a pre-built data model. To access these options, in the Insert tab on the ribbon, click the “PivotTable” down arrow.

In my case, I want to analyze an Excel table (named T_Sales) I created in an Excel worksheet, and I want to do so in a new worksheet to keep my workbook tidy and organized. So, after selecting a cell in the table, I’ll click “From Table Or Range” in the PivotTable drop-down menu. Then, after making sure the correct range is selected, I’ll check “New Worksheet,” and click “OK”.

Opening the PivotTable Fields pane

The PivotTable Fields pane is where you decide what goes in the PivotTable. This pane usually opens by default on the right of your screen as soon as you initiate the PivotTable-building process.

The PivotTable Fields pane closes as soon as you click away from the PivotTable area.

If the PivotTable Fields pane doesn’t appear by default when you select a cell in the PivotTable area, or if it disappears at any point when you’re working on your PivotTable, click “Field List” in the PivotTable Analyze tab on the ribbon. You can also click the same button if you don’t want the PivotTable Fields pane to be displayed by default.

To move the PivotTable fields pane, hover your cursor over the top of the pane until it turns into a four-arrow move pointer, and click and drag the pane to a more convenient position on your screen as a floating window.

To turn it back into the default pane, click and drag it to the right of your screen until it clicks back into position.

Understanding the PivotTable Fields areas

When I first started using PivotTables in Microsoft Excel many years ago, I found the PivotTable Fields pane confusing, not only because the default layout is overly condensed, but also because what goes in the different fields isn’t immediately obvious.

By default, the fields (labeled A in the screenshot below) are stacked on top of the areas (labeled B in the screenshot below). If your source dataset is substantial, this layout makes everything appear overcrowded, especially if you’re working on a small screen.

• To fix this, click the “Tools” cog, and select “Fields Section And Areas Section Side-By-Side”
• This vertical layout makes the pane appear less cluttered and, thus, easier to use.

Before you go ahead and start building your PivotTable, take a few moments to understand what each area means:

• Rows: Fields inserted into the Rows area appear as row labels down the left-hand side of the PivotTable. In the screenshot below, the Product field being in the Rows area means the products in the source data are listed in column A.
• Columns: Fields added to the Columns area appear as column labels across the top of the PivotTable. In the example below, you can see countries in the column headers.
• Values: Fields placed into the Values area are usually variables with numeric values that form the main part of the PivotTable, with each value acting as an intersection between a column and a row. In the PivotTable below, each cell where a product and country intersect contains a profit value.
• Filters: Insert a field into the Filter area if you want to filter the entire PivotTable based on a given variable. In this case, I can filter the PivotTable by department.

Adding and removing fields to and from a PivotTable

There are two ways to add fields to an area in the PivotTable Field pane.

When you check a checkbox next to a field, Excel automatically moves it to one of the areas according to the type of data the field contains. Usually, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. Useful as this automation may be, fields sometimes go in the wrong areas. What’s more, when you use this method, there’s no way for Excel to know which field to add to the Filters area.

Alternatively, dragging and dropping a field into an area gives you more control over what goes where. If you want to change which area a field is added to, simply drag and drop it to the appropriate place. For example, to remove the Department filter, click and drag “Department” from the Filters area to the left of the PivotTable Fields pane.

To remove a field from a PivotTable area, either uncheck the corresponding checkbox in the fields section or drag and drop a field from the areas section to anywhere outside the PivotTable Fields pane.

Now that you have used the PivotTable Fields pane to build your PivotTable, use the PivotTable Analyze tab on the ribbon to manipulate and customize the data. For example, you can rename the PivotTable, group different fields, or insert slicers and timelines for dynamic filtering. On the other hand, you can change the PivotTable’s visual properties via the Design tab, such as adding subtotals, formatting the data into banded rows, or changing the colors of the headers.