Master the way of the spreadsheet with these Excel tips and tricks

Who doesn’t love spreadsheets? Probably a lot of people, actually, but even if the thought of filling rows and columns with various data points doesn’t get your pulse rising, there are a number of industries where spreadsheets are a necessity. However, though there are plenty of third-party alternatives to Microsoft’s iconic office suite, Excel remains the premier application for making and managing spreadsheets. If you’re new to Excel — or an old hand who wants to plumb Excel’s depths — our Excel tips and tricks are sure to help you increase your productivity. Read on for all the details.

If you’re looking for additional Excel pointers, we’ve put together  guides on how to password protect an Excel document and how to make a graph in Excel.

Select All

If you want to highlight a specific grid of cells, you can simply click and drag, but what if you want to select all of them? There are two ways. You can use the keyboard shortcut — Ctrl + “A” in Windows 10, Command + “A” in MacOS High Sierra and earlier iteration of MacOS — or go to the smaller cell in the upper-left corner (marked by a white arrow) and click it.

How to shade every other row

Spreadsheets can be awfully drab, and if you’ve got a lot of data to look at, the reader’s eyes might start to drift aimlessly over the page. Adding a splash of color, however, can make a spreadsheet more interesting and easier to read, so try shading every other row.

To start, highlight the area you want to apply the effect to. If you want to add color to the entire spreadsheet, just select all. While viewing the Home tab, click the Conditional Formatting button. Then, select New Rule from the resulting drop-down menu.

In the Style drop-down menu, choose Classic.

Afterward, select Use a formula to determine which cells to format.

The formula to enter is “=MOD(ROW(),2).”

Choose which fill color you want, then click OK. Now the rows should be shaded in alternating color.

Hide/unhide rows

Sometimes, you may want to hide some rows or columns of data. This can be handy if you want to print copies for a presentation, for instance, but the audience only needs to see the essentials. Thankfully, it’s easy to hide a row or column in Excel. Just right-click it, then select Hide.

The column or row will be noted by a thick border between the adjacent columns or rows.

If you want to unhide a specific row or column, you will need to highlight the columns or rows on either side of it. Once done, right-click and select Unhide from the resulting list of options.

If you have hidden multiple rows and/or columns, you can quickly unhide them by selecting the rows or columns in question, then right-clicking and selecting Unhide. Note: If you’re trying to unhide both rows and columns, you will need to unhide one axis at a time.

How to use vlookup

Sometimes, you may want to ability to retrieve information from a particular cell. Say, for example, you have an inventory for a store, and want to check the price of a particular item. Luckily, you can do so using Excel’s vlookup function.

In this example, we have an inventory where every item has an ID number and a price. We want to create a function where users can punch in the ID and get the price automatically. The vlookup function does this, letting you specify a range of columns containing relevant data, a specific column to pull the output from, and a cell to deliver the output to.

We’re going to write the vlookup function in cell I4; this is where the data will be shown. We use cell I3 as the place to input the data, and tell the function that the relevant table runs from B2 to D11, and that the answers will be in the third column (vlookup reads from left to right).

So, our function will be formatted like so: “=vlookup (input cell, range of relevant cells, column to pull answers from),” or “=vlookup(I3,B2:D11,3).”

How to create a drop-down list

If you want to restrict the range of options a user can put into a cell, a drop-down menu is a good solution. Thankfully, you can easily create one that offers users a list of options to choose from. To start, choose a cell, then go to the Data tab and select Validate.

Under Settings, find the drop-down menu labeled Allow. Then, select List.

Now, highlight the the cells you want users to choose from and click OK.

Now, when users click on the cell, they can choose options from a drop-down menu.

Keep your zeroes visible

Sometimes, you may want to enter strings of numbers beginning with one or more zeroes. Unfortunately, Excel may not show these zeroes, by default. To fix this, add a single quotation mark before the zeroes, e.g. “‘00001” instead of “00001.”

How to concatenate

Sometimes, you may want to reorganize data and combine info from different cells into one entry. You can do this using the concatenate function.

In this example, we have a basketball fantasy league roster, with fields for players’ last names, first names, and positions. If we want to combine all that information in one field, we will need a formula that reads “=concat(first cell, second cell, third cell),” or, in this case, “=concat(C6,B6,D6)” without the quotes.

Unfortunately, concatenate doesn’t automatically put spaces between text from different cells. To make a space appear, you will need to add quotation marks with a space in between. Now the function should read “=concat(C6,” “,B6,” “,D6).”

To apply this formula to all the rows we need, click the blue box in the corner of the cell and drag down.

Excel will automatically copy the formula and fill in appropriately.

How to wrap text

Got a lot of text in a single cell? It will probably spill into other cells, which might not look as nice as you’d like. Thankfully, it is easy to make text wrap within a cell.

Start by selecting the cell with the excessive text in it. Right-click and select Format cells.

Under the Alignment tab, check the box beside Wrap text.

Adjust the width of the cell to your liking.

How to see the developer tab

If you want to do more advanced work in Excel — such as create macros — you will need to access the Developer ribbon. Sadly, this tab is hidden by default. To view it, click Excel in the upper-left corner and select Preferences.

Next, click the Ribbon button.

You should see list of buttons, and checkboxes that determine whether you can see the various components. Here, scroll down to the Developer box and check it.

Now you should see the Devolper ribbon at the top.

Here’s what is coming to the Gear VR in August!

Are you ready for more Gear VR apps?.intro

The biggest mobile VR library is getting a lot bigger this month! Oculus is planning a big surprise party for the Gear VR near the end of the month, with a bunch of games launching with support for the new Controller! Adding a special Controller you can see from inside the headset is going to be a lot of fun, but it’s far from the only thing coming to the Gear VR this month.

Read more at VR Heads!

Ben Heck’s portable Raspberry Pi photo booth

Karen has an idea for a project: With Felix’s help, she’ll create a portable Raspberry Pi photo booth, useful for weddings and other events. This isn’t just a hardware project, though — it also needs Linux software to allow the system to save and upload files. Felix takes this as an opportunity to give everyone a crash course in how to run Linux on the Raspberry Pi. After they finalize the design, Karen talks us through her Python logic underpinning the built-in camera. How’s your Python programming? What Pi Cam projects have you made? And what would you have done differently here? Let us know over on the element14 Community.

Nanoparticles fool your body into allowing organ transplants

Organ transplants are frequently life-saving, but they remain a gamble when your body can reject the new organ well after the initial surgery. Yale researchers have discovered a clever solution to this: prevent your body from noticing the organ until it settles in. They’ve developed a drug delivery system that uses nanoparticles to slowly supply small interfering RNA (siRNA) that stops your body’s white blood cells from attacking the organ as a foreign presence. Instead of lasting mere days, the siRNA lasts as long as 6 weeks. This won’t necessarily eliminate the rejection response, but it should be far easier to control if and when it kicks in.

You can customize the nanoparticles to achieve a specific effect, such as carrying more of the drug. It’s also specific to the organ you’re targeting, so there shouldn’t be inadvertent damage.

The team’s next focus is on applying their nanoparticle system to kidney transplants, and it’s going to be a long time before this method is useful in the field. If it does pan out, though, it could dramatically reduce the risks associated organ transplants. Scientists would want to develop artificial organs more to keep up with demand and less as a backup for those times when natural organs don’t take.

Source: Yale News

Chicago police see less violent crime after using predictive code

Law enforcement has been trying predictive policing software for a while now, but how well does it work when it’s put to a tough test? Potentially very well, according to Chicago police. The city’s 7th District police report that their use of predictive algorithms helped reduce the number of shootings 39 percent year-over-year in the first 7 months of 2017, with murders dropping by 33 percent. Three other districts didn’t witness as dramatic a change, but they still saw 15 to 29 percent reductions in shootings and a corresponding 9 to 18 percent drop in murders.

It mainly comes down to knowing where and when to deploy officers. One of the tools used in the 7th District, HunchLab, blends crime statistics with socioeconomic data, weather info and business locations to determine where crimes are likely to happen. Other tools (such as the Strategic Subject’s List and ShotSpotter) look at gang affiliation, drug arrest history and gunfire detection sensors.

If the performance holds, It’ll suggest that predictive policing can save lives when crime rates are particularly high, as they have been on Chicago’s South Side. However, both the Chicago Police Department and academics are quick to stress that algorithms are just one part of a larger solution. Officers still have be present, and this doesn’t tackle the underlying issues that cause crime, such as limited access to education and a lack of economic opportunity. Still, any successful reduction in violence is bound to be appreciated.

Source: Reuters

Harvard’s tiny robotic arm can make endoscopic surgery safer

Minimally invasive surgery is already amazing as it is, but a team of Harvard scientists have created a device that expands its possible applications even further. They designed a tiny robotic arm that lays flat while the endoscope is moving through narrow passages and pops up to reveal tools surgeons can use for the procedure. Unlike the typical surgical tools doctors use today, which are completely rigid, the team’s creation has a hybrid design featuring a rigid skeleton surrounded by soft materials. Even better, it has sensing capabilities that can give the surgeon a sense of where the arm is, what it’s touching and how it’s moving.

By using a combination of the soft and rigid components — connected to each other by chemical bond instead of adhesive — the device is more flexible than completely rigid tools. Its soft parts include a suction cup that can handle tissues more gently than a hard and sharp tool can. The device also has a water-powered actuator, which is how it pops up for use after traveling through the body while lying flat on the endoscope.

Harvard says the fabrication method the team used is simple enough for bulk manufacturing. Further, the design can be scaled down to one millimeter for incredibly complex procedures in the lungs or the brain that would require an endoscope to travel through tiny spaces. The team already performed an ex-vivo (outside the body) test on a pig’s stomach, but that’s just the beginning. Before doctors can use it on actual patients, the team still has more tests to go: in fact, their next goal is to use it on a living animal.

Team member and paper co-author Robert Wood said:

“We are focused on some of the more challenging endoscopic techniques where tool dexterity and sensor feedback are at a premium and can potentially make the difference between success and failure.”

Source: Harvard School of Engineering and Applied Sciences

Tesla fans reach a symbolic long-distance EV driving milestone

There’s been a relentless quest to set electric car driving records in 2017, and a team of Italians just managed to smash a couple of those records in style. Tesla Owners Club Italia drove their Model S P100D 1,078km (669.8 miles) on one charge, setting a new long-distance record (the previous was ‘just’ 901.2km) and becoming the first to drive a production EV more than 1,000km before plugging in. They did have to make some major sacrifices to hit their target, but it’s still a symbolic victory.

Like many past record-holders, the Italian team had to hypermile (that is, optimize their driving behavior) to keep the P100D going for as long as possible. They drove around Salerno at a pokey 40km/h (24.9MPH) with low rolling resistance tires, the air conditioning turned off and an emphasis on smooth driving techniques (such as the use of Autopilot) that made the most of the battery. All told, the feat took 29 hours — you wouldn’t want to try this if you were in a hurry to get anywhere.

You likely won’t see EVs achieving this kind of mileage in everyday driving any time soon. Tesla officially rates the P100D’s range at less than half this figure for a good reason, since the realities of the road are going to shrink the usable distance. However, the fact that it’s achievable at all is important. It suggests that truly long-range EVs aren’t that far away.

Via: Elon Musk (Twitter)

Source: Tesla Owners Club Italia (PDF)

Insurers increasingly use apps and drones instead of agents

Hobbyists aren’t the only ones using the phone and drone cameras to explore the world in new ways, as insurance companies are increasingly opting for “virtual” or “touchless” handling of claims. The Wall Street Journal cites the 2017 Future of Claims Study survey by LexisNexis Risk Solutions (PDF) which found that 38 percent of insurers don’t send employees out for physical inspections in at least some situations. One story mentioned says that Lemonade Insurance settled and paid out a claim in just three seconds using the AI bot connected to its app.

Companies like DJI are tweaking their drones for property surveying that works for construction and insurance, while auto insurance companies are leaning on self-service apps to make estimates. According to the WSJ, this is a part of rising customer satisfaction with insurance claims over the last few years as measured by JD Power. It’s also something I unexpectedly got a closer look at earlier this year when I ran into a deer.

Using my insurance company’s app I sent in pictures (including the one shown above) the next day and within a few hours had an estimate that I could take to repair shops. It didn’t do much to speed up the process, unfortunately, as part shortages kept my car in the shop for over a month. Also, my photos of the outside of the car didn’t reveal the damage to internal parts like the radiator and turbo mount, which may have caused more back and forth later, but weren’t much of a factor in how much time the repairs took. It was more convenient than needing to arrange an appointment with an agent, but it still shows that some things are better done by an expert.

Source: Wall Street Journal, LexisNexis (PDF)

Don’t let the tech you love stress you out

You really can have too much of a good thing.

A side effect of having the world at your fingertips is information overload. With every gadget and gizmo connected to each other, you can literally find any information you need with just a few clicks or taps or swipes. That’s pretty amazing and a fine way to make the world a better place with all the understanding and critical thinking it brings. But we’re not computers, and it’s easy to get overloaded.

My phone can tell me whatever stupid thing some politician said this morning (as well as the stupid things everyone else had to say about it) or how the next phone we’ll be buying is missing a headphone jack or might not have 3D touch or that World of Warcraft gold is worth more than Venezuelan currency. It can also tell me what a great time friends had at the park or that the movie I wanted to see really sucks according to some guy I don’t know who is paid to tell me movies suck. Then it gives me an avenue to add to the noise and voice my level of approval and/or astonishment about it all.

My phones tells me what I need to know as well as a ton of stuff I don’t.

This is great stuff. Whether we find it all informative or entertaining, or both, it’s pretty cool to be part of a worldwide discussion about politics or movies or World of Warcraft gold and its economic impact. But eventually, we all get stressed because it becomes too much. This can and will have an effect. We’ve all seen that person we know have a spectacular meltdown over the dumbest shit. Sometimes, we’ve been that person melting down. Everyone has a breaking point when the noise becomes more than we can filter. But there’s an easy way to break the cycle.

The power button.

We can’t get away from stress. It’s easy to say that these are stressful times and things will get better, but that’s a lie. Times have always been stressful and we’ve been told things will always get better, but that ain’t happening. Tomorrow will bring new problems and worries to go along with them, and we’ll always have good reason to be concerned about the world around us. People in the U.S. are worried about health care and people in Venezuela are worried about economic collapse and people in Syria are worried about getting killed. These are important issues we all should be thinking about and discussing, and if you’re directly affected it’s natural to be afraid. It’s also natural for anyone to feel hopeless or compassionate or angry. Or a mixture of the three.

Just don’t get so consumed from the sidelines that you stop making things better. And when the unimportant noise tries to suck you in, know when to say enough is enough and turn it off for a while.

Information can be addictive. So can the technology that delivers it. Do yourself a favor and shut it all down once in a while. Facebook will still be there when you come back.

The beginning of augmented reality [#acpodcast]

This week, Daniel Bader and Jerry Hildenbrand are joined by VR expert Russell Holly to talk about Moto Z2 Force reviews and several other newly announced Moto phones. Thanks to some recent leaks, we know even more about the Galaxy Note 8 and upcoming LG V30.

A major topic this week is the ASUS ZenFone AR. Russell guides us through the device and where Asus along with Tango fit into the Augmented Reality world. Also discussed is Apple’s ARKit, and how hot does your phone get in a heavy duty case?

Show Notes and Links:

• Moto Z2 Force review
• The Moto E4 Plus is a cheap phone with a 5000mAh battery
• Where do the Moto G5S and G5S Plus fit into Motorola’s phone lineup?
• This is the Galaxy Note 8
• Samsung Galaxy Note 8 specs
• This is the LG V30
• ASUS ZenFone AR comes to Verizon for $650
• Where do Asus and Tango fit into the world of augmented reality?
• Apple ARKit – What you need to know

Podcast MP3 URL: http://traffic.libsyn.com/androidcentral/androidcentral347.mp3