Removing spaces or hidden characters There are several ways to remove spaces from workbook data once it’s been imported. We can use the TRIM and CLEAN functions, SUBSTITUTE, or even the Flash Fill command. The following table lists these functions and some syntax examples: Table 9.2 – Cleaning data by removing spaces from data Figure […]
Removing spaces or hidden characters Read More »
Removing duplicate data To locate duplicates in a worksheet, follow these steps: To remove duplicates, follow these steps: Figure 9.52 – The Remove Duplicates feature Figure 9.53 – The Remove Duplicates dialog box Figure 9.54 – The results are displayed in the notification popup Another way to remove duplicates will be explained in the next
Removing duplicate data Read More »
Importing, cleaning, joining, and separating data When we import data from different sources, cell data may contain extra spaces or invisible characters. It may be necessary to remove any unwanted characters if issues arise when working with formulas or formatting in the workbook. After importing and cleaning your dataset, data may need to be joined
Importing, cleaning, joining, and separating data Read More »
Setting up a conditional formatting rule Now that we have the rule in place, we would like to ensure that every time a user selects an item from the drop-down list from a single cell in column L, the color of the row changes accordingly. Remember that you don’t need to set the validation rule
Setting up a conditional formatting rule Read More »
Conditional formatting functions Conditional formatting is a format, such as a cell shading or font color, that’s automatically applied to cells if a specific condition is met (true). When the condition is met, a specific cell format is applied to the cells to answer any queries you may have about your data. Finding duplicate worksheet
Conditional formatting functions Read More »
Extracting using wildcard characters Wildcard operators are useful for extracting data according to a partial match within a cell. For instance, if the Winery column listed names of wine farms and we wanted to filter all the cells containing the text wine, we could search the Winery column for the partial match using *wine. The
Extracting using wildcard characters Read More »
Using operators to refine filters We can further broaden our filters using comparison operators. Previously in this book, we concentrated on the greater than and less than operators. Now, let’s look at a few scenarios where we can encompass the use of two operators, as well as the NOT criteria. We will also explore how
Using operators to refine filters Read More »
Filtering using Advanced Filter In the previous edition of this book, we learned how to set up and manage sorts and filters. Filtering data in Excel allows you to locate data very quickly, as well as eliminating certain choices (text, dates, or values) from a list’s results, or specifying categories of data to get the
Filtering using Advanced Filter Read More »
Updating Excel on the web The following table highlights some of the new and/or updated features in Excel for the web. As it is extremely seamless working from the desktop version to the web (when a workbook is saved to OneDrive or SharePoint) and vice versa, these features enhance productivity as it is just a
Updating Excel on the web Read More »
Generating maps In the previous edition of this book, we explored the ins and outs of chart creation and formatting. Let’s add to this by learning about the new 3D geography Maps feature: Figure 9.13 – The new Maps feature Importing data from a PDF At times, we may need to copy data that has
