Working for years in the logistics and supply chain industry, we have seen countless examples of using Microsoft Excel in day-to-day reports, analytics, project timelines and deadlines, and document templates. This has skyrocketed work in many departments. Undoubtedly, Excel and spreadsheets allow us to run complicated calculations, manage heavy volumes of customer information, and track stock levels and reorder points. However, it was challenging for everyone to use Excel's more complicated functions, formulas, and macros to be more proficient in their jobs.
Despite Excel's pros and cons, many people need help to do simple VLOOKUP, IF, or AND formulas to calculate cell values faster. Many logistics professionals need help because it's just impossible to remember all the great functions, filters, calculations, and data pivot methods to go through all tasks smoothly. It becomes even more complicated if you do your reports occasionally, once or twice weekly. Therefore, we prepared these 40 Excel tips and tricks, some Excel hacks, and basic Excel training to give you easy access to tools frequently used in logistics and supply chains.
This set of proposed functions and formulas, macros, can be applied as ready-to-use prompts for Chat GPT to calculate more sophisticated data volumes and copy them directly to the Excel workbook. You'll also learn more about Formatting and Visualization from Excel, an indispensable tool. Chat GPT doesn't provide yet such flexibility as our Excel templates. Also, see how to benefit from one of the best analytics platforms in the world by Gartner.
What is Microsoft Excel? It skyrockets your logistics reports!
Excel is capable spreadsheet software used for organizing business data, calculating demand, and analyzing — or, in general, manipulating — data. This is an indispensable tool for various tasks in logistics operations, administration, and the supply chain. You can run everything from simple calculations to complex data analysis, all within a single tool that helps keep data clean.
We understand that it might be quite difficult to get the right formula, and often, you need to search online or, now with ChatGPT, how to calculate something. Even using AI, you must copy and paste a data table from an online app into an Excel window. This may prevent you from gaining knowledge, but at least your task is accomplished. Well, we offer a bit more here. Below, you can find basic formulas important in Excel, and thanks to this, you can run your daily logistics and supply chain reports smoothly.
Here we provide basic Excel functions and ways to use it to:
Count formulas
The formula COUNT determines the total number of selected cells in a range that contains certain numbers. For example, that can be: =COUNT(A1). Here, you can count the number of cells with numbers in the range A1 to A10.
Another formula is COUNTBLANK, where you can calculate the number of empty cell references within a specified range. By giving you an example, we can reflect this value: =COUNTBLANK(B2). Here, you can find that this counts the blank selected cell in the range B2 to B20.
One more basic count formula is COUNTA, which counts the number of selected cells that contain any type of data, including text, numbers, or even logical values. If you ask me, for example, we can give you this: =COUNTA(C3), which counts all non-empty cells in the range C3 to C15.
Last here but not least, the count formula COUNTIF counts the number of cells within a range that meet a specific criterion. If we refer to a single example, it should look like this: =COUNTIF(D1, "Yes"), and it counts the number of cells in the Range D1 to D20 that contain the word 'Yes.'
SUM formulas
For some of you, it might be obvious that when discussing the SUM formula, you calculate the total of a range of numbers. For some, it isn't so easy. Thus, the simplest example will look like =SUM(B2), which adds up all the numbers in the Range B2 to B15.
MAX and MIN formulas
Focusing on the MAX formula, you can determine the largest value within a specified range of cell contents. An example can be given through =MAX(C1), where it finds the highest value in the range C1 to C30.
The second example, the MIN formula, determines the smallest value within a specified range of cell contents. Therefore, the best example looks like this: =MIN(D2), which finds the lowest value in the Range D2 to D25.
IF, AND, and OR formulas
Here, let's try focusing more on the IF formula; it performs a logical test and returns one true value and another if false. It's time to look at the example, and if you have a value in cell A32 = 1, then the IF formula will certainly look like this: =IF(A32=1, "one," "not one"). This will be useful when you want to get a result based on whether the number matches the condition you are searching for. It simply means that if you're using this formula and A32 equals 1, then in the cell with that formula, you'll see the text 'one.' Here are other variations of using the IF formula:
Check cell A33. If you input the value 'one,' we can try using the formula =IF(A33=1, 1, 0), which will display the single digit '1' in the cell.
Look at cell A34 in your entire worksheet for the value 'one.' Then, we can use the formula =IF(A34= "one," 1, 0) again. It displays one digit from the formula: '1' in the cell.
If cell A35 will reflect the value 'one,' it is worth checking the formula: =IF(A35= "one," "one," "not one") displays 'one' in the cell.
IF function makes decisions based on a single condition, while the AND formula requires all conditions to be true. This can be demonstrated with the example: =IF(AND(A75=1, B75=1), "one," "not one"), where certain cells must reflect exactly these numbers.
OR formula requires at least one condition to be true. The OR function is also used to check multiple conditions, but it returns TRUE if at least one of the conditions is met in that way. It's useful for making calculations in your reports where you need only one condition to be satisfied to get the result. A good example looks like this: =IF(OR(A83=1, B83=1), "one,"" "not one"), and the field/cell contents with that condition yield the text 'one.'".'
VLOOKUP
VLOOKUP means vertical look. Is that clear to you? This is another way of filtering required values, making a huge difference. So, an example can be given when we want to find the value in the Excel table based on a certain value. In this case, we may say that we have a column with several records, such as column A – employee names, column B – badge number, column C – warehouse facility, and column D – city. Now we want to find where the employee named John, with badge number 97491293, is working. So, we can filter VLOOKUP based on the name using this formula: =VLOOKUP(B5, A8:D10, 3, 0). It will scan all columns B to C to give us all the results for "John." You need to know that B5 is your main value, "John," and from column A to column D are all the column values, where "3" means the number of the certain column where you have the specific data you want to VLOOKUP.
HLOOKUP
HLOOKUP means horizontal look. That's how simple it is. So, the general logic should be clearly understandable, as in the example above. It's particularly useful when you have a dataset with multiple columns and want to retrieve information based on a specific value. So again, imagine a table with employee data where row 8 on the entire worksheet includes names, row 9 includes badge numbers, row 10 – warehouse facilities, and row 11 – city. The logic remains the same, and the formula looks like this: =HLOOKUP(F5, F8:H9, 2, 0) when you want to filter the badge number. As you may see, there are again only fields marked as with VLOOKUP and nothing about the rows, but again, the logic remains the same, and you need to add where F5 is your searched value, plus a row number that indicates the value for the badge.
Key points from the Excel basics
Several key points are crucial for Microsoft Excel's basic functions and everything connected to it. We're adding several additional considerations about using its features before we go deeper into this subject and start our top 40 hacks, tricks, and tips — you can call it whatever you wish but we just want to provide you with the best of the best for your work.
So, let's take a look at key points from the Excel basics:
Data types. The formulas can handle numbers and text, allowing for more complex calculations.
Logical operators. The equal sign (=), greater than (>), less than (<), and not equal to (<>) are commonly used in logical tests.
Nested formulas. Most formulas can be nested within each other to create more complex conditions. This means that you can perform extensive calculations across various tabs in spreadsheets.
Error handling. To avoid errors, you can use functions like ISNUMBER and ISTEXT to check data types before performing calculations. This acts as a bug checker, similar to debugging in coding.
Case sensitivity. Text comparisons are often case-sensitive, so you need to be very specific with your data input to avoid incorrect results.
Formatting. This is also crucial to avoid skipping and cell shift and ensure that numbers are formatted correctly to prevent unexpected results.
Excel for supply chain management and logistics
We combined two subjects here, but let's be clear that our Microsoft Excel formulas and other basics we shared above can be applied in any industry. So, we're not talking only about supply chain management, logistics, and transportation, but let's focus mainly on these three here for now:
Inventory management:
Track inventory levels. Maintain accurate records of inventory quantities with Excel sheets and automated calculations.
Calculating reorder points. Determining when to replenish inventory based on demand and lead time.
ABC analysis. Categorizing inventory items based on their value and usage and using them for cycle counting.
Inventory valuation. Calculating the value of inventory using various methods (FIFO, LIFO, JIT for time delivery measuring, average cost).
Demand forecasting:
Sales data analysis. Analyze historical sales patterns to identify trends and annual data. Excel can be very useful during peak periods.
Demand forecasting models. Create simple models using Excel functions such as linear regression.
Inventory planning. Use forecasting to optimize inventory levels to see aging inventory, perhaps to replenish stock, as mentioned above.
Order management:
Procurement processing. You can use Excel to track order status, customer information, and shipping details. You can also fill in track and trace numbers in Excel and use VLOOKUP to see which order has been delivered.
Order fulfillment. Manage the order picking, packing, and shipping processes. Using Excel, you can easily track picking times, even if the warehouse employee is still using paper documentation.
Order analysis. Analyze sales performance by product, customer, or region. To optimize picking paths, you can see which products should be placed closer to incoming goods.
Transportation and logistics:
Route optimization. We recommend a custom TMS for this, but you can also use Excel to calculate the most efficient routes for delivery vehicles.
Transportation cost analysis. Using Excel, you can compare different transportation types and carriers. Again, you can use VLOOKUP to check cost-effective routes.
Loading planning. Excel can help you optimize the loading of a truck or container to maximize space utilization. In Excel, you can create a chart reflecting a container's layout and calculate its capacity by adding new pallets to it.
Supply chain analysis:
Supply chain mapping. You can create a visualization of material and information flow with your Excel. So, see on your spreadsheet various supplier carriers, no matter whether your manufacturing or distribution center.
Performance indicators. Calculating key performance indicators (KPIs) such as lead time, inventory turnover, and fill rate.
Start with a "What-If" analysis. With Excel, you can easily model different scenarios to assess the impact of changes in supply chain parameters.
Financial management:
Cost analysis. Finally, transportation, inventory, and warehousing costs are calculated for the operations departments and the admin clerk's office. So, it's crucial here to have a good command of Excel to smoothly run all calculations that are vital for the business.
Budgeting and forecasting. Developing financial plans for supply chain operations.
Profitability analysis. Assessing the profitability of different products or customer segments.
Top 40 Excel hacks you must know
Finally, we reach the point where we can introduce you to 40 Excel hacks you must know. See below what is important for beginners and more advanced users of Excel, where we share functions and formulas, excellent examples for macros, and a bit about how to use VBA. So, if you're in logistics, working in a supply chain, distribution center, or warehouse, you can benefit from these top Excel hacks. To make it more clear to you, we divided this into several groups highlighted with a header before each one. Let's buckle up!
Hacks for higher efficiency and productivity in Excel spreadsheet
1. Use shortcut keys to speed up your work
It seems like an absolute basic, but you can speed up your work by using certain keyboard shortcut keys. Here are several main shortcut keys that can make your Excel performance much faster:
Ctrl+C - Copy
Ctrl+V - Paste
Ctrl+Z - Undo
Ctrl+S - Save
Ctrl+F - Find
Ctrl+H - Replace
Ctrl+D - Fill down
Ctrl+R - Fill right
Ctrl+Shift+↓ - Select data range down to cell shift
Ctrl+Shift+→ - Select data range right to cell shift
2. Use the flash fill feature to quickly enter data
Type the first few entries in a column, and Excel will automatically try to fill in the rest of the column. Then, you need to press Ctrl+E to confirm.
3. Use conditional formatting to highlight important data
For many of you, conditional Formatting is an Excel tool that allows you to automatically apply specific Formatting. This might be formatting through colors, icons, or data bars to cells based on certain conditions. This visual feature of Excel makes it easy to quickly identify trends and sort out data with important information.
4. Autofill quickly fills in the data by dragging the fill handle
The fill handle is the small square at the bottom right corner. It can also be a selected cell or Range. If you want to use this hack in your Excel, drag this handle down or across it to quickly populate cells with sequential numbers, dates, times, or even text patterns. It just replicates some numbers or figures in a certain order. If you have that date 11-09-2025 and drag this handle down the row, you'll populate the date with the next days, which will be 12-09-2025, 14-09-2025, and so on.
Excel recognizes patterns and continues the series accordingly. For instance, another example might be that if you type "Jan" in one cell and "Feb" in the next, dragging the fill handle will automatically complete the remaining months. But if you mark only a single cell with "Jan," you'll likely copy this value further down the column.
5. Assign names to ranges for easier reference
Instead of using complex cell references in formulas, you can assign descriptive names to ranges of cells. This makes your formulas more readable and easier to understand. For example, you could name a range of sales figures "TotalSales." Your formula would then look like "=SUM(TotalSales)" instead of "=SUM(B2:B100)."
6. Customize the Excel interface
Excel options allow you to tailor the software to your preferences and working style. So you can make changes in your menu bar like in every other part of the MS Office. You can customize everything from the interface's appearance to calculation settings, formulas, and proofing tools. You can add your macros to specific functions available from the buttons in the menu bar. Excel can have custom buttons to pull data automatically from the internet. So, this flexibility ensures that Excel works how you want it to.
7. Customize the toolbar with frequently used commands
To be more specific about these customizations, you can add your commands with the Quick Access Toolbar. This element provides shortcuts to your most used commands. You can add any command to it by right-clicking on the toolbar and selecting "Customize Quick Access Toolbar." Your work can be very easy and save you time by eliminating the need to navigate through menus. You can even add your specific icons to the options in the menu you made.
8. Control what is displayed on the screen
Manipulate your view options to get better control over how your worksheet is displayed. For example, you can freeze panes, show or hide gridlines, headers, and footers, and even adjust the zoom level. This approach helps you focus on the relevant parts of your worksheet and improve readability.
9. Customize the print area by specifying the print-ready range
If you only want to print a specific portion of your worksheet, which is possible at any time, you can define a print area. This prevents unnecessary pages from being printed and saves paper. Furthermore, you can create ready-to-use Excel templates, as we explain below.
10. Create your own logistics Excel templates through reusable worksheet structures
Excel templates are pre-formatted worksheets that are a starting point for new documents. You can create templates for ready-to-print invoices, proof of delivery, packing lists, etc. You can simply create templates for recurring tasks, such as often reprinted instruction sheets for your employee's performance, or use built-in templates for budgets or other common documents. This saves time and ensures consistency in your work. Moreover, Microsoft Excel provides many templates like this that are already embedded into Excel. Just enough if you connect to your Office online services.
Start with more efficient data manipulation for analysis
11. Pivot tables are a great visual hack on how to summarize and analyze large datasets quickly
Everyone loves charts. These are frequently used in logistics and supply chain KPI sheets and in-demand reports. You can create charts with PivotTables, a tool for summarizing and analyzing large datasets by filtering. They allow you to quickly create cross-tabulated reports, calculate subtotals, identify trends, and create clear visual charts later. Just enough if you drag and drop fields, and then you can dynamically reorganize your data to reveal insights.
12. VLOOKUP and HLOOKUP
V-HLOOKUP is one of the basics of retrieving data from a table based on a specific value. We mentioned this above, but it deserves a place in our top 40 Excel hacks list. So, what reflects VLOOKUP and HLOOKUP are functions used to search for a specific value in a table and return a corresponding value from a different column. VLOOKUP looks up values in a vertical table, while HLOOKUP searches in a horizontal table. These two functions are very important for data retrieval and lookup tasks.
13. INDEX and MATCH
A more flexible alternative to VLOOKUP and HLOOKUP is INDEX and MATCH. INDEX function offers a value from a specified range based on row and column numbers, while MATCH finds the position of a value within a range. You can always combine these functions and can create complex lookup formulas. So, if You want to find the price of a carrier using the route direction name instead of the caller ID, you can use this example:
Use the formula =INDEX(C2:C10, MATCH("Carrier", B2:B10, 0))
C2:C10 is the Range containing prices/fees.
MATCH("Carrier, "B2:B10, 0) finds the row number of "Carrier" in column B.
14. SUMIF, COUNTIF, and AVERAGEIF perform calculations based on specific criteria
This set of functions allows you to run calculations based on specific criteria. This is quite obvious, but when you're using SUMIF, it actually adds values that meet a given condition. Next, if you're using COUNTIF, it counts the number of cells that meet a condition. Last but not least, the function AVERAGEIF can calculate for you the average of values that meet a condition. Let's use one more example about John.
Let's assume you're working in the e-commerce warehouse finance department and have a list of sales with columns for Product, Salesperson, and Sales Amount.
So, to calculate smoothly total sales for product "A," you may want to use this: =SUMIF(A2:A10, "A," C2:C10)
If you want to count the number of sales by salesperson "John", please, check this one: =COUNTIF(B2:B10, "John")
If you are thinking about the average value, so to calculate the average sales amount for product "B," it is quite good to use this one: =AVERAGEIF(A2:A10, "B," C2:C10)
15. Check SUBTOTAL to calculate any totals based on filtered data
It's time to explain this one. So, the SUBTOTAL function calculates subtotals for filtered data in your reports or other data spreadsheets. You can perform various calculations with SUBTOTAL, such as SM, AVERAGE, COUNT, etc. This is useful for analyzing data subsets without affecting the overall calculations, so you can cut out some of your results from the main data volumes.
Let's say that you have a filtered list of sales data, and to calculate the total sales for the visible rows you need to use: =SUBTOTAL(9, C2:C10). Keep in mind that 9 represents the SUM function for subtotals.
16. Data validation for restricting data entry to specific values or formats
This one is the one I use almost everywhere on my spreadsheets. Data validation helps ensure that my data accuracy and consistency are secured by restricting data entry to specific values or format cells. Simply put, you can create dropdown lists, input messages, and error alerts to guide users and prevent invalid data from being entered. So, I'm using this every time, even now, when I don't work in logistics for various reports, to help me get ready to use options. Let's say you want to restrict data entry in a cell to a list of predefined values (e.g., product categories).
First, you need to select the cell. Next, you should go to the Data tab, and then see data validation settings. There will be an option to choose "List" from the Allow dropdown. What is more important is the time to select the source field and enter the list of values or select a range containing the list.
17. Remove duplicates by identifying and removing duplicate records
It is very frequent that you have data sources from 2 or more places. When you get 2 reports to compare, it is worth removing duplicates to not replicate the same values and make simply hard errors by having 2 times the same routes on your or 2 the same shipping numbers for the same direction.
To do this efficiently, you need a list of customer names with duplicates. First, select the Range of customer names and move directly to the Data tab, then Remove Duplicates. Excel will identify and remove duplicate entries.
18. Work with text to columns function
This function splits text into multiple columns. Text to Columns allows you to split text within a single cell into multiple columns based on delimiters, which can be commas, spaces, or custom characters. For example, you can split the names and surnames on your list. This is useful for cleaning up data and preparing it for analysis.
Using an example of how to do this, just imagine that you have a column with full names in the format cells "FirstName LastName." Look now to do this step by step:
Select the column.
Go to the Data tab, then Text to Columns.
Choose "Delimited" as the data type.
Use a space as the delimiter.
Specify the destination for the new columns.
19. Power query like a quick way to transform and load data from various sources
This is a less popular function, but Power Query (which means Get & Transform Data in Excel) is also quite often used by me as a tool for importing, again cleaning, transforming, or even loading data from various sources. Even though I prefer macros, Power Query provides a user-friendly interface for data preparation tasks. How does it work? Suppose you have a CSV file with sales data that needs cleaning and transformation. Then you do as follows:
Use Power Query to import the CSV file.
Clean the data by removing errors, filling missing values, and changing data types.
Create calculated columns or new tables as needed.
Load the transformed data back into Excel.
20. Data analysis toolpak for performing statistical analysis
Another Excle hack is the Data Analysis Toolpak, an Excel add-in that offers advanced statistical analysis tools. It includes functions for regression and also features ANOVA, correlation, and other statistical tests.
If you ask me about an example, and let's assume that you want to perform a linear regression analysis to predict sales based on advertising spending, you're doing as follows:
Load the Data Analysis Toolpak add-in.
Use the Regression analysis tool to fit a linear model to your data.
Get visual formatting and visualization
21. Cell styles and apply predefined formatting to cells
Cell Styles allow you to quickly apply predefined Formatting to cells. It is all about ensuring consistency throughout your worksheet, no matter which kind of logistics report or calculation you are busy with. You can create custom styles or use built-in options like headings, titles, or currency formats. So, to summarize, its purpose is to quickly apply consistent Formatting to cells. If I can share a good example, I could advise you to apply a "Heading 1" style to a row of column headers for a bold, centered, or larger font. By getting this visual context highlighted, you can easily differentiate your content.
22. Custom number formats
With custom number formats, you can create specific number displays that provide, we could say, granular control over how numbers are displayed. I used this in logistics reports to define specific decimal places, currency symbols, and date formats or even create custom text representations for numbers. When thinking about a good example, I can say that to format a cell as currency with two decimal places and a dollar sign, use the format cells code "$#,##0.00". This might be an example of 1234.56, which would display as $1,234.
23. Merge and center - combine cells for headings
If you want to combine cells for headings, you can use the Merge and Center feature, which combines multiple cells into a single cell and aligns the content. This is quite often used for creating titles or headings that span several columns.
24. Create a table dialog box
And if I can share an example, then you can create a table dialog box with a title spanning columns A to E, select cells A1 to E1, then merge and center the text "Sales Report" or "Shipping Report" within this combined cell.
25. Wrap text - display text within cell boundaries
To display text within cell boundaries, I can advise you to use the Wrap Text trick. This one allows text to flow to multiple lines within a cell instead of being nearly truncated. I think that this is useful for fitting long text strings within cell boundaries without affecting the layout of other cells. Referring to the life case example, we can think about if a product description is too long to fit in a single line, select the cell, and enable wrap text. Then, the description will automatically adjust to multiple lines within the cell's boundaries.
26. Borders and shading like enhancing cell appearance
Borders and shading enhance a lot of cell appearance. If we think about the visual appearance of your worksheet by adding lines and colors to cells, then this will be a perfect option. You can use this hack, which helps to organize data, highlight important information, and kind of improve readability. It will definitely let you look better with your spreadsheets during the meeting with your co-workers. Look at my example, which can be given by visually distinguishing data sections that apply thick borders around a table of sales figures. This uses light shading to alternate rows and simply makes the data easier to read and analyze.
27. Charts and creating various chart types to visualize data
We were talking about this a bit earlier when it came to Pivot data. I just think that no Pivot table (group pivot table items) should be on the spreadsheet, and putting it into Charts provides a visual representation of your data. This makes it easier to understand trends, patterns, and relationships. Excel offers a variety of chart types, including column, line, bar, pie, and scatter charts, to suit different data types and analysis needs. Just an endless number of examples can be used to compare sales figures across different product categories and create a column chart. The height of each column represents the sales for a specific product, allowing for easy visual comparison.
28. Sparklines
If you want more visual prompts on your spreadsheets, it's worth thinking of ini-charts within cells. I want to highlight here that sparklines charts are embedded in the cells that provide a compact visual data summary in summary rows. They are useful for quickly identifying trends, highs, and lows within a dataset. For example, you can use them on your logistics and supply chain sheets to quickly show sales trends over time for multiple products and add a line sparkline to each product's row. This is the way that provides a compact visual representation of sales performance without creating separate charts.
29. Conditional formatting
Conditional Formatting automatically applies Formatting to cells based on specific conditions. It highlights data based on specific conditions, helps focus on important data points, identifies trends, and makes your worksheet more visually informative. Well, there is no better example than, to highlight sales figures above 100,000 in green, select the sales data range, go to Conditional Formatting, select "Highlight Cells Rules", then "Greater Than". Enter 100000 and choose a green fill color. This is how simple it is!
30. Cell comments and adding notes to cells for explanations
Whenever you want to add notes to cells for explanations, you may want to use Cell Comments. This hack allows you to add notes or explanations to individual cells. What's more, these comments can provide additional context, clarify data points, or document formulas and calculations. If you look at the examples with me, it is worth thinking about how to add a comment explaining a formula. You need to right-click on the cell, select "Insert Comment," and type the explanation. For instance, in a cell containing the formula "=B2*C2"," you could add a comment saying, "Calculates total cost by multiplying a quantity by price."
31. Freeze panes
It's good to keep one row from moving when scrolling. So, the way to lock rows or columns in place while scrolling is by using freeze panes. This hack locks rows and columns in place while you scroll through the rest of the worksheet. This is helpful when working with large datasets to keep headings or important data visible as you navigate the data. For instance, let me give you an example of when you must freeze the top row and first column in a shipping report. This can be about the shipping volumes table that keeps column and row headers visible while scrolling through the data.
Advanced macros hacks and techniques
32. Add serial numbers with macro
We know that macros are a bit more advanced and quite difficult to use, but they can really make your Excel life easier. You can start small by adding a single macro to your spreadsheets that sends a single command. For instance, you can automatically add sequential numbers to column A using macro Add Serial Numbers. Check a ready to use an example from below:
VB.Net
Sub AddSerialNumbers()
Dim I As Long
For i = 2 To Range("A" & Rows. Count).End(xlUp).Row
Cells(i, 1).Value = i - 1
Next i
End Sub
33. Insert multiple columns
Do you know you can connect macro with the manually added buttons on your spreadsheet? You can add there one command, like Insert Multiple Columns, which can insert two columns at columns C and F, like in the example below:
VB.Net
Sub InsertColumns()
Columns("C: C"). Insert Shift:=xlToRight
Columns("F: F"). Insert Shift:=xlToRight
End Sub
34. Insert multiple rows
An option with a macro can help you save a lot of time when doing jobs manually frequently. You save time when clicking, skipping between tabs, and so on. The Insert Multiple Rows macro can help you insert two rows at rows 4 and 7. In this example, you may need this hack to extend your report automatically when you need to populate huge volumes of data.
VB.Net
Sub InsertRows()
Rows("4:4"). Insert Shift:=xlDown
Rows("7:7"). Insert Shift:=xlDown
End Sub
35. Autofit all the columns
With this macro, you can adjust the width of columns A to Z to fit the content. This is quite helpful as you can automatically format your cells into a more suitable size.
VB.Net
Sub AutoFitColumns()
Columns("A: Z").AutoFit
End Sub
36. Autofit rows
When you want to adjust the height of rows 1 to 10 to fit the content or any other rows, you can use the macro from below:
VB.Net
Sub AutoFitRows()
Rows("1:10").AutoFit
End Sub
37. Remove text wrap
This simple macro can help you remove text wrapping from a specified range. It is quite useful as here, you don't have to manually mark all the cells. You have your cell range vast in the macro, so the code will know what you exactly mean. This is another way to potentially save quite a lot of time if you want to see more or less data at once.
VB.Net
Sub RemoveTextWrap()
Range("A1:Z100").WrapText = False
End Sub
38. Unmerge cells
Let's say you want to unmerge cells A1 to D1, so Unmerge Cells macro is the best. We think here is small, about one or two cells, but I frankly believe that you'll apply this to bigger sets of data. So, again, start small and go big😊
VB.Net
Sub OpenCalculator()
Shell "calc.exe"
End Sub
39. Open calculator
There are many ways of making calculations in Excel. You can just use the SUM function to see how much is in the cell, but you can also use any Microsoft app on your computer with just one click using a macro. If you want to Open the Windows Calculator in your Excel sheet, you can use that macro:
VB.Net
Sub OpenCalculator()
Shell "calc.exe"
End Sub
Here, you have just a simple command to run your application from the .exe file. It is easy to apply and suitable to use for various apps you may need along with your Excel.
40. Delete blank cells in row 1
Do you want to clean up your data on a spreadsheet or make it look more suitable before a presentation? You can use the macro deletes blank, like the one below, which cleans the cells in row 1:
VB.Net
Sub DeleteBlankCells()
Dim c As Range
For Each c In Range ("1:1").Cells
If IsEmpty(c) Then c.Delete Shift:=xlToLeft
Next c
End Sub
Extra bonus Excel hack: all the details on your plate
41. Sort out single column
So, this is the last macro on our list, but definitely not least. Macro Sorts column A in ascending order can be very useful
for various reports, but you can change conditions based on your needs and use more columns and variables
VB.Net
Sub SortColumn()
Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub
Are you in search of a reliable tech partner?
Adexin can help with advanced logistics solutions
Contact usSummary: no Google Sheets can beat Excel
Well, if you're ready to start with your Excel automation or using this more efficiently we advise you to use these formulas, functions, and macro examples. You can explore the vast capabilities of Visual Basics for Excel and go deeper into advanced techniques to become a spreadsheet expert in logistics and supply chain management.
Adexin helps companies transfer their Excel spreadsheets into custom software. We develop custom features that integrate with your system and help replace many of the elements done so far in Microsoft Excel. We can help you keep data clean and up to date. There are many ways we already helped with translation from typical Excel spreadsheets to more custom solutions for logistics, supply chain, and transportation. Use our resources and tutorials, and refine your Excel with our custom software development skills. Get in touch with us to learn more about how to switch!