Soluții

How to use the CHOOSECOLS and CHOOSEROWS Functions in Excel to Extract Data

Excel’s CHOOSECOLS and CHOOSEROWS functions are like twins: their DNAs are very similar, but they’re separated by subtle differences. The same can be said for their syntaxes.

Here’s the syntax for CHOOSECOLS:

=CHOOSECOLS(a,b,c,…)

And here’s the syntax for CHOOSEROWS:

=CHOOSEROWS(a,b,c,…)

In both cases,

  • a (required) is the original array that contains the columns (CHOOSECOLS) or rows (CHOOSEROWS) you want to extract,
  • b (required) is the index number of the first column (CHOOSECOLS) or row (CHOOSEROWS) to be extracted,
  • c (optional) represents the index numbers of any additional columns (CHOOSECOLS) or rows (CHOOSEROWS) to be extracted, each of which must be separated by commas.

If an index number represents a column or row in the center of the array (in other words, not the first or last column or row), adding extra columns or rows to the center of the array will change the indexed column or row.

You might be thinking that these functions sound very similar to the TAKE function. However, where TAKE lets you extract the first or last x columns or rows, or a single column or row from elsewhere in a named table, CHOOSECOLS and CHOOSEROWS let you extract any number of columns in any order from anywhere in your data.

Extract specific data from your Excel table.

Example 1: Extracting the First and Last Column or Row From a Table

I’ve already lost count of the number of times I’ve used CHOOSECOLS and CHOOSEROWS to extract the first and last column or row from a table. This is particularly handy if the first column or row is a header, and the last column or row contains totals.

To follow along as you read this guide, download a free copy of the Excel workbook used in the examples. After you click the link, you’ll find the download button in the top-right corner of your screen.

Imagine you’re an administrator for a local sports league, and you’re producing a report that summarizes the points scored by five teams across five games.

The first dataset you want to generate will tell you the total number of points scored overall by each team. To do this, in a blank cell, type:

=CHOOSECOLS(T_Games[#All],1,-1)

where

  • CHOOSECOLS is the function you want to use, since you’re extracting the data from the Team and Total columns,
  • T_Games is the name of the table where the array is stored, and [#All] tells Excel that you want to include the header and total rows in the result,
  • 1 is the first column (in this case, the column named “Team”), and
  • -1 is the last column (in this case, the column named “Total”)

and press Enter.

By default, CHOOSECOLS counts from left to right, and CHOOSEROWS counts from top to bottom. To reverse this, place the minus symbol (-) in front of the relevant index numbers. Here’s the result you get when you press Enter, and this data can be duplicated on another sheet in the same workbook, such as a dashboard tab, or copied and pasted as text in an email or Word document.

Now, even if more data is added, the result—including the Total row—updates accordingly.

You could also use the same method with unformatted tables by using direct cell references rather than structured references. However, if you add more rows to the right or columns to the bottom of your array, the formula won’t pick these up unless you change the cell references manually. Also, generally speaking, tables offer better tools and adaptability than unstructured cells.

The next report you want to generate will show the number of points scored in each game (rows 1 and 8).

So, in a blank cell, type:

=CHOOSEROWS(T_Games[#All],1,-1)

and press Enter. Remember that adding [#ALL] after the table name in the formula forces Excel to count the header and total rows when addressing the index numbers.

Example 2: Extracting Columns from more than one Range

Now, let’s say you have this spreadsheet, and your aim is to produce a list containing the total scores of each team across Leagues 1 (green), 2 (blue), and 3 (gray).

This time, you don’t want Excel to extract the column headers and row totals, since you’re going to nest the VSTACK function within the CHOOSECOLS formula to stack the three tables directly on top of each other. So, in cells I1 and J1, create the column headers manually.

How to Combine, Reshape, and Resize Arrays in Excel

Take control of the arrays in your spreadsheet and arrange them as you please.

Then, in cell I2, type:
=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)

where

  • CHOOSECOLS is the function you’ll use to extract columns,
  • VSTACK lets you combine the results vertically,
  • League_1,League_2,League3 are the names of the tables that represent the arrays, and the absence of [#ALL] after the table names tells Excel not to include the header and total rows in the result,
  • 1 tells Excel to extract the first column (“Team”) from each array, and
  • -1 tells Excel to extract the last column from each array (“Total”)

and press Enter.

At this point, you could go one step further and sort the result in descending order by typing:

=SORTBY(I2#,J2:J16,-1)

into cell L2, and pressing Enter.

So far, I’ve shown you the benefits of using CHOOSECOLS and CHOOSEROWS to extract the first and last columns and rows from an array. However, this time, I’ll show you how you can use CHOOSECOLS to extract other columns, and combine this with additional Excel tools to make your spreadsheet stand out.

This spreadsheet shows five teams’ scores over five games, including the total points scored in each game in the total row. Your aim here is to produce a result that shows each team’s score and the overall total in cells A11 to B15 for a game number you will enter into cell B9.

First, type a game number into cell B9, so that you have something to work with when you generate your CHOOSECOLS formula. Then, in cell A11, type:

=CHOOSECOLS(T_Scores[[#Data],[#Totals]],1,B9+1)

where

  • CHOOSECOLS is the formula used to extract columns,
  • T_Scores is the name of the table, and [[#Data],[#Totals]] tells Excel to include the data and the totals in the result, but not the header row,
  • 1 represents the first column (“Totals”), and
  • B9+1 tells Excel that the second index argument is represented by the value in cell B9, plus one. The reason you need to include +1 here is because the game numbers start in the second column of the table. As a result, typing 3 into cell B9 extracts the data from the fourth column of the table, which is game 3.

Although typing the game number into cell B9 works well, if you or someone else accidentally enter an invalid number, the CHOOSECOLS function will return a #VALUE error.

Excel returns a #VALUE error if any of the index numbers are zero or exceed the number of columns or rows in the array.

This is a good case for using data validation in cell B9 to create a drop-down list of the available game numbers. It beats typing in the same options 200 different times manually.

My preferred way to do this for a header row in an Excel table is to first select all the relevant cells, plus a few extra to allow for growth, and name the range in the name box in the top-left corner of the Excel window. Notice how I’ve not selected the column header “Team,” because I don’t want this to appear in the game drop-down list in cell B9.

  • Next, select cell B9, and click the top half of the “Data Validation” button in the Data tab on the ribbon.
  • Now, in the Data Validation dialog box, select “List” in the Allow field, make sure “Ignore Blank” is checked, type an equal symbol (=) followed by the name you just gave to the column header range, and click “OK.”

You can now click the drop-down arrow in cell B9 to select a valid game number, safe in the knowledge that even if you add extra columns to your table, CHOOSECOLS and the named range in data validation will work together to update the available options accordingly.

Finally, to visualize your data even further, select cells B11 to B15, and in the Home tab on the ribbon, click “Conditional Formatting,” hover over “Data Bars,” and choose a solid fill color.

As a result, not only have you extracted specific columns from your data using CHOOSECOLS, but you’ve also made the result dynamic by adding a drop-down list, and you’ve formatted the data to add visual clarity.

[mai mult...]

How to fix missing Recovery Partition on Windows 11 using existing WinRE.wim

This set of instructions assumes that a copy of the “winre.wim” file is available on C:\Windows\System32\Recovery while the hidden and protected system files are set to be visible. If the recovery image is missing, you will have to continue with the alternative set of instructions below.

To create the missing Recovery Partition with an existing WinRE.wim file, follow these steps:

[mai mult...]

How to maximize your phone’s battery health

To understand how to preserve your phone’s battery health, it helps to know the basics of how a lithium-ion battery works. Every second you use your phone, tiny chemical reactions occur inside the battery to release stored energy. A lithium-ion battery has two terminals: a positive one (cathode) and a negative one (anode).

When your phone is in use (a.k.a. discharging), lithium ions move from the negative side to the positive side through a gel-like substance called the electrolyte, while electrons flow through the circuit outside in the same direction, powering your device.

When the battery is fully discharged, all the usable energy has been depleted, and your phone shuts off. Plugging in your phone reverses this process—electricity drives the lithium ions and electrons back to their original positions, recharging the battery.

Of course, the actual chemistry is more complex, but this covers the core idea. What matters is this: batteries don’t like staying fully charged or empty for long. These extreme states place more stress on the internal chemistry, which can speed up long-term degradation. In a perfect world, you’d keep your battery hovering around 50% all day, but that’s not realistic, so experts recommend keeping it between the 20-80% range whenever possible.

This doesn’t mean you should never fully charge your phone even when you know you’re going to need that extra 20%. The degradation escalates when you make it a habit to leave your phone empty or fully charged for extended periods of time.

Even overnight charging is safer these days, as modern phones can learn your charging habits to stop the charging at 80% while you’re sleeping and only resume it minutes before you wake up, so you start your day at 100%, but your phone doesn’t sit at 100% all night. It’s pretty clever. You should err on the side of caution if your phone doesn’t have this feature, though.

If you want to maximize your battery health, you can manually cap the charging at 80%. It’s a trade-off: you’ll need to charge your phone more often, which is a bit inconvenient, but you’ll have that peace of mind that you’re doing the right thing for your battery. Different companies label this feature differently in their UI, but you can easily find it in the battery menu.

A charge cycle isn’t just “one plug-in.” It’s defined as using the equivalent of 100% of your battery’s capacity, whether that happens in one go or spread out over time. For example, say you charge your phone from 0% to 50%, use it for a bit, then later charge it from 40% to 90%. You’ve plugged it in twice, but you’ve only used 100% total, so that’s one full charge cycle.

If your phone’s battery is able to retain 80% of its original capacity after 800 charge cycles, that’s usually considered normal by most manufacturers. Apple says the batteries of iPhone 15 (and presumably later) models are “designed to retain 80 percent of their original capacity at 1000 complete charge cycles under ideal conditions.” Xiaomi makes much bolder claims and says its 14T Pro can retain 80% capacity after up to 1600 charge cycles.

So, don’t stress about short charging sessions. In fact, frequent partial charges are healthier than letting your battery drain to zero and charging it all the way back up. It puts less strain on the battery chemistry and helps extend its lifespan.

One of the easiest ways to protect your battery is to keep it out of extreme temperatures. You probably already know that heat is bad for batteries as it messes with their internal chemistry; that’s very true, but being too cold isn’t good for them either. Some people assume that because heat is harmful, cold must be better. That’s not true.

Even if your phone overheats, you should never chuck it inside the refrigerator. The rapid change in temperature can permanently damage the internals far more than overheating ever could, and the humid environment inside the refrigerator can lead to condensation, which can result in water damage, which is usually not covered under warranty.

The correct, foolproof way to cool down your phone is just to stop using it, close all apps, or temporarily power it off. According to Apple, the ideal temperature range for smartphones is between 62° to 72° F (16° to 22° C), and beyond 95° F (35° C), there’s a risk of permanent damage. If you’re traveling someplace where you know the temperatures are harsh, get an insulated phone pouch like this PHOOZY phone case.

Fast charging isn’t as harmful as you might think, especially if you use it mindfully, but it’s not completely risk-free either. The faster your phone charges, the more current flows through it, and that generates a not-insignificant amount of heat. But there’s a nuance here: this heat does not start manifesting as soon as you plug in your phone.

Why? Because heat is essentially unabsorbed energy and lithium-ion batteries accept charge more easily when they’re running low. As the battery fills up—especially past 70–80%—internal resistance increases, and it becomes harder to force more energy in, and that’s when heat starts to spike.

Think of your battery like a dry sponge. At first, it soaks up water quite easily. But the wetter it gets, the harder it becomes to absorb more, until eventually, you’re just spilling water everywhere and making a mess. With batteries, that “mess” is heat.

Now, if you start using your phone while it’s charging, you’re adding to the problem. Your phone pulls energy from the battery to power its components, while the charger is trying to push energy into it. This tug of war generates more heat and leads to slower charging, higher wear, and long-term battery stress. So, if you want to extend your battery’s lifespan, try to avoid using your phone while it’s charging, especially when it’s already past that 70% mark.

If you’re using a budget or older phone, chances are it wasn’t built with high-end gaming in mind. Sure, there are exceptions—some Chinese brands offer solid specs at surprisingly low prices—but even then, it’s best to play within your device’s limits. Don’t push max settings unless your phone can handle it.

I don’t usually rely on synthetic benchmarks, since they can be (and have been) manipulated and often don’t reflect real-world use. But if you’re going to check them anyway, an AnTuTu score of 700,000 or more is a decent baseline for running most modern games on medium settings without serious heating, at least for short sessions.

Of course, that’s a rough estimate. Performance isn’t just about the chip; thermal management and software optimization matter just as much. If you’re planning to upgrade, test the phone yourself to see if it gets uncomfortably warm fast after intense gaming. If it does, it’s wiser to stick to lighter games.

If you’ve had a bad experience with Android updates in the past, it’s understandable why you might want to avoid them. But doing so means your phone might not get the bug fixes and optimizations that can prolong battery life.

Android 14, for instance, closed a loophole that allows apps to run in the background indefinitely, resulting in reduced background activity by 50%, said Dave Burke, former Google VP of Engineering, in an interview. The less battery consumed, the less often you need to recharge, and hence, the slower you will exhaust your limited charge cycles.

[mai mult...]

How to organize your Digital Cards in Apple wallet for Quick Access

The Wallet app is a great place to store your credit and debit cards, loyalty cards, and event passes, but it can quickly become disorganized. With how easy it is to import compatible cards and passes, your digital wallet can turn into a chaotic, card-filled mess.

The good news is that there are steps you can take to organize it, making it easier to navigate and quicker to access the card or pass you need. Here are a few ways to manage your digital wallet and keep it neat.

Before organizing your digital wallet, you must know how to add and remove different cards and passes. Fortunately, both are easy to do, but adding them depends on where it is coming from.

If you are adding a debit, credit, transit, or ID card, tap the plus icon in the upper right corner of the Wallet app, select the card type, and follow the on-screen instructions. Remember that digital ID cards and driver’s licenses are only available in select states, so check your state’s eligibility before trying to add one.

When it comes to importing loyalty and membership cards from stores, gyms, and food chains, you’ll need to get them from the company’s native app—meaning you will have to download their app first. However, not all companies offer compatibility with Apple Wallet, so if you’re looking to digitalize all your membership and loyalty cards, you can use a third-party app as a workaround.

While you can manually add a credit or debit card by entering your information, some card providers—like Discover and Chase—let you add your card directly to the Wallet app from their apps. This can save time and even allow you to use your card through Apple Pay before the physical version arrives.

Event passes, such as movie or concert tickets, will usually be imported from the app where you purchased them—like AMC or Ticketmaster—but you might also receive an email with a link to add them directly to your digital wallet.

If you need to remove a card, ticket, or pass, the steps are similar across the board. For passes or tickets, tap the three-dot icon in the upper right corner and select “Remove Pass.” For cards, tap the same three-dot icon, choose “Card Details,” and then tap “Remove Card” at the bottom.

Reordering Cards and Passes for Easy Access

When you add a new pass or card to the Wallet app, it typically appears at the top of the stack; however, this may not always be the most efficient location, depending on how frequently you use it. For this matter, you can easily change the order of your cards and passes.

After adding a card or pass, find it in the Wallet app, then tap and hold it; the item will lift slightly to show that it’s selected. From there, drag it up or down to reposition it, and once you find the right spot, release it, and it will stay in place.

However, please note that you cannot mix cards with passes and tickets or vice versa. Each type of item stays within its section. Digital house keys, car keys, credit cards, debit cards, and gift cards will remain at the top of the Wallet app. Loyalty cards, event passes, and tickets will be grouped separately toward the bottom.

While some may think that organizing your digital wallet is not a big deal, it can be pretty handy. For example, you could move your gym membership card to the top of the section for easy access or move your favorite-looking card to the bottom so you can see its whole design. Regardless of how you choose to organize your digital wallet, setting it up with accessibility in mind will save you time and hassle when you need to find what you’re looking for quickly.

Setting a Default Payment Card

The Wallet app’s best feature is its connection with Apple Pay, but there’s more to it than just adding cards. You can also select which card you want to use as your default.

Setting a card as your default allows it to automatically appear when you double-click the power button to bring up Apple Pay. This can save you time by eliminating the need to select your most frequently used card every time you check out manually.

To set your default card, open the Settings app and scroll down to “Wallet & Apple Pay.” Once there, scroll to the “Transaction Defaults” section, tap “Default Card,” and select the card you want to use.

Another way to set a card as your default is by dragging it to the bottom of your stack of cards, where a prompt will then appear, notifying you that it has been set as your default card of choice. Moving that card up will then make the next card inline the default, and the same message will appear.

Having your Passes and Tickets Show on the Lock Screen

While people may use the Wallet app primarily for their credit and debit cards, passes and tickets offer an advantage over them because they can be suggested on your lock screen based on the date, time, and current location.

To enable this feature, open the Wallet app, tap on the pass you want to show automatically, then tap the three dots in the upper right and select “Notifications.” From there, toggle on “Suggest on Lock Screen.”

This is great for easy accessibility, as you don’t have to dig through the Wallet app—the right pass will automatically appear on your lock screen, ready for use. For example, when you’re checking out at a grocery store, your loyalty card will appear on the lock screen for quick access and scanning.

The same applies to event tickets: if you’re near a venue like an AMC theater, your movie ticket will appear on the lock screen, and you can tap it to display your information without needing to search for it.

If you don’t want this happening, you can disable the feature by repeating the same steps as enabling it and toggling “Suggest on Lock Screen” off. Another way to organize your digital wallet is to group various passes by category. However, Apple does not offer this feature natively.

To do this, you will need to use a third-party app, such as Pass2U. With this free app, you can create personalized passes to import into the Wallet app and organize them into groups. However, you can only group passes you have made within the app, not ones already in there from other apps.

This is a good idea if you want to group all your loyalty cards in one section and all your membership cards in another. It won’t make your digital wallet look too cramped and allow you to organize it like a file system.

Until Apple adds this feature natively, this is the best way to organize your passes further. Just like you would group cards into different pockets in your physical wallet, you should do the same for your digital one.

As Apple continues to expand what you can do in the Wallet app, it is not too far off to think that other card types will become compatible with it. Additionally, as more states offer digital IDs, more people may add them to their digital wallets for convenience.

With this in mind, knowing how to organize your wallet and having a clear understanding of where each card, pass, or ticket is located can help you navigate it promptly when you need a specific one. Regardless of whether you have five cards or twenty cards in the Wallet app, it’s always a good idea to keep it organized and familiarize yourself with your digital wallet.

[mai mult...]