These shortcuts will work when you are entering a formula. For example, let's assume that you want to have today's date as a string (within quote marks) in a formula. Key in your formula, up to the first quote mark. Then type Ctrl+; and the date appears in the formula. Type a closing quote mark, and you can continue with your formula.
Here is a quick way to enter the current date or time into a cell. Select your input cell and press Ctrl+; (that's the semicolon symbol ). Very similar is entering the time by using Ctrl+: (that's the colon symbol), Ctrl+Shift+;. These shortcuts will work when you are entering a formula. For example, let's assume that you want to have today's date as a string (within quote marks) in a formula. Key in your formula, up to the first quote mark. Then type Ctrl+; and the date appears in the formula. Type a closing quote mark, and you can continue with your formula. Alan Salmon
For those of you who love to use the Format Painter but are a fan of keyboard shortcuts, here are the shortcut keys to use. Excel doesn't provide a single shortcut key you can use for the Format Painter. You can, however, use the following steps:
Alan Salmon
Sometimes when you open an Excel workbook, you may find that Excel "replicates" the workbook. When it opens, two workbooks are displaying in the taskbar. Both contain the same workbook name, but one is followed by a 1 and the other followed by a 2. This is a feature of Excel that allows you to open the same workbook in multiple windows. To do this on the View tab, select New Window. Each time you do this, the file name will have a number added to the end in the title bar. Saving a workbook with multiple windows open will result in all the windows opening the next time you open the file. To have only one window for the file open the next time, first, close all the windows but one and then save the file. Alan Salmon
Strikethrough is one of the character formats you can use within Excel. Strikethrough places a horizontal line through the middle of the character (or characters) to which the attribute is applied. To apply strikethrough:
Alan Salmon
Let’s assume you have values in a range of cells, and you want to use a different format to distinguish the odd numbers from the even numbers. You can use the Conditional Formatting feature in Excel to accomplish this. Follow these steps:
Once this conditional formatting is applied, odd cells will be one colour and even another colour. If the cell contains text, the conditional formatting will not apply. Conditional formatting takes precedence over any other styles you apply to a cell. Alan Salmon
Let’s assume you are working with a data table that has a limited number of categories by which you want a count; you can use the COUNTIF worksheet function to solve this problem. For example, you have a data table that has two columns. Column A could be the names of clients, and Column B could be the names of the staff member responsible for that client. There are only half a dozen staff members, but many clients. In Column E, list the names of your staff, one staff per row starting in row 2, since row one is the column name. In Column F, beside the first staff, enter the following formula: =COUNTIF($B$2:$B$200,E2) Make sure you replace $B$2:$B$200 with the actual range of your original data table. (You could use a named range if desired.) Copy this formula (cell F2) into the other rows of Column F beside each staff member’s name. The information in Column F represents the number of customers for each staff member. Alan Salmon
New Functions Provide Superior Options!
When sorting or filtering data in Excel, most users instinctively click the drop-down arrows at the top of a column to initiate the process. Alternatively, they flock to the Data tab of the Ribbon and click on the Sort or Filter icons there. While there is certainly nothing wrong with either of these approaches, better options exist if you are using Excel provided through a Microsoft 365 or Office 365 subscription. In this tip, you will learn how to take advantage of these better options, namely Excel’s SORT and FILTER functions.
Origins of Excel's SORT and FILTER Functions
For Microsoft 365 and Office 365 subscribers, the SORT and FILTER functions first appeared in the 1907 release of Excel (July 2019.) Neither of these functions is available in Excel 2019 or prior. Assuming you have access to Excel provided through an Office 365 subscription, you should have access to both SORT and FILTER, along with several other functions that create dynamic arrays.
Dynamic arrays are one of the most significant developments in Excel to appear in the past decade. Before dynamic arrays, we had to enter (or copy) a formula into each cell where we needed a calculation. Some refer to this as the one formula, one value paradigm. With dynamic arrays, we can enter one formula that takes action on many cells at the same time, meaning many of our Excel processes just got significantly easier! Using Excel's SORT and FILTER Functions
Excel’s SORT and FILTER functions are relatively simple and easy to use. The syntax for the SORT function appears below.
=SORT(Array, Sort_index, Sort_order, By_col) The array is the range of data you want to sort. The sort_index is the column by which you want to sort the array. By default, the sort_index is the first column, but you can enter a numerical reference to any other column. The sort_order defaults to a value of “1,” representing ascending; you can change this to “-1” to sort in descending order. Finally, the by_col reference defaults to False to facilitate sorting by row. If you need to sort by column instead, change the value to True. To illustrate working with the SORT function, consider the example presented in Figure 1. The SORT function used sorts the data in cells B2 through C51 on the second column (population), and it sorts the data in descending order. Notice that the formula places the results into E2 through F51. The procedure does not alter the source data range (B2 through C51) at all.
|
When you inherit a worksheet from someone, you may want to discover which cells have conditional formatting applied to them. This is rather easy to do using the Go To feature of Excel. Follow these steps:
That's it. Excel selects all the cells in the current worksheet that have conditional formatting applied to them |
Alan Salmon
To move a chart's legend, simply click on it once to select it. (You will know it is selected when handles appear around the perimeter of the legend.) Then use the mouse to click within the legend and drag the legend to the desired position on the chart. When you release the mouse button, the legend stays were you dropped it.
Alan Salmon
Categories
All
2022
Accountant
Accounting Software
Accounting Solutions
Adobe
Advisory
AI
Artificial Intelligence
Automation
Backup
Bitcoin
Blockchain
Business Automation
Business Continuity
Business Intelligence
Business Management
Cloud Computing
Collaborate
Collaboration
Colonial
Computer
COVID 19
COVID-19
CPA
CRM
Cryptocurrency
Customer Relationship Management
Cybersecurity
Cyptocurrencies
Dext
Doc.It
Ecommerce
Emerging Technologies
Entrepreneurs
Excel
Forecasting
Forecast Sheet
Google
Grammarly
HR
Internal Controls
Internal Priorities
KPIs
LET
Links
Management Reports
Microsoft 365
Microsoft Office 2021
Microsoft Teams
Office
Office 365
Online Shop
Outsourcing
Pandemic
Paperless
Personal Computer
PivotTables
Power Automate
Power BI
PowerPoint
Productivity
QuickBooks
Quickbooks Online
Ransomware
Receipt Bank
Remote Work
RPA
Sage 50
Security
Small Business
Small Business Accounting
Small Business Accounting Software
Stockhistory
Technologies
Technology
Tech Update
Windows
Windows 10
Windows 11
Word
Work At Home
Workflow
Work From Home
Xcm
Zoho
Zoho CRM
Authors
Ward Blatch
Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.
Tommy Stephens
Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.
Randy Johnson
Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com
Bernie Smith
Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.