Lydon Solutions

Construction Project Management Software Solutions

Excel

Tips from the Field: Speed Up Your Excel Workflow

Tips from the Field |

Excel is probably the most heavily used software program in every industry, including construction. Across every discipline and every stakeholder, Excel is king. Over the years of being in the construction field and delivering Excel-based solutions such as Cost Reports and Project Monthly Reports to clients, I found some tips to help speed up your workflow.

Want to copy data without copying the formula?

If you want to copy the values in fields and not the formulas, then there is a quick way of doing this. Everyone is familiar with copy and paste. But if you select your data range, click copy, paste special, and choose values, then you only paste the data, not the formulas.

Picture1

How can you avoid re-typing values from columns to rows?

If you want to copy rows and make the values display as columns or vice versa, select your data range, click copy, paste special, and check the transpose box

Picture2

Looking for a simple way to look up a set of values in your worksheet?

You can insert data validation into your worksheet if you want users to pick from a list of pre-defined values. Start by creating a list of the values to choose from. Then click on a cell where you want users to select the value. From the Data tab, click on Data Validation, select List under Allow, and highlight the list of values you created for the Source. When a user clicks in the cell with the data validation, they will have to choose from the pre-defined values you defined.

Picture3

There are many more of these tips in Excel that can save you time and frustration. If this article is helpful and you would like to see more Excel tips, please like and share. Also, if you have any tips and tricks, please add to the comments and help others in the community.

Lydon Solutions is an IT solutions provider in the construction industry specializing in Microsoft Azure, 365, Power Platform, SharePoint, and SQL. If you need help with your Microsoft products, you can reach out here for a free one-hour consultation.

What’s the difference between a single-line vs. multi-line text column in SharePoint?

Tips from the Field |

Learn the key differences between single-line and multi-line text columns in SharePoint – and how to select the right column type for your needs.

There are two types of text columns – single-line or multi-line – that you can add to a SharePoint list or library. It may seem like the difference is obvious. However, the behavior of these two column types in SharePoint is drastically different.

Refer to the table below for a comparison.

Single-line vs. Multi-line Text Column in SharePoint

Single-line Text Column Multi-line Text Column
Maximum Length 255 characters • List = 63,999 characters
• Document library = Unlimited (optional)
Filter and sort in a view Yes No
Reference in a lookup column Yes No
Use in a calculated field column Yes No
Change column type (e.g. choice, number, currency, date and time, single-,or multi-line text.) Yes No
Enforce unique values Yes No
Define default value Yes No
Add rich text (e.g. bold, italics, text alignment, or hyperlinks) No Yes
Enter enhanced text (e.g. pictures, tables, or hyperlinks) No Yes

Which is better: single-line or multi-line text columns in SharePoint?

As you can see, there are significant differences between the two text column types in SharePoint. For example, once you add a multi-line text column, you cannot switch back to a single-line text column. You must instead create a new column.

So which text column in SharePoint is better? We recommend using a single-line text column over a multi-line text column whenever possible. Single-line text fields are the most flexible – so you can do things like later change the information type to add a lookup column, build calculated fields and so on.

Sometimes, however, multi-line text columns are necessary. One example would be a contractor response field in a Submittal Form. Be aware that getting that data back out (e.g. for use in reports, calculations, workflows, etc.) is challenging without custom coding.

Read more about list and library column types and options in SharePoint.

Get more tips and tricks for construction project management professionals

Our Tips from the Field blog series features handy information for construction project managers. Subscribe to our newsletter to get our latest tips and tricks delivered directly your inbox each month.

And be sure to check out our previous tip to learn how to configure required fields in SharePoint.

Export to Excel – Tips from the Field

Tips from the Field |

Learn how to export your project data from SharePoint to Excel and keep it automatically up-to-date in this installment of Tips from the Field.

You and your project team probably share Excel lists and forms all the time as email attachments. But hunting out attachments in your inbox is a pain. And you never can be sure everyone is seeing the latest version.

Here’s a way to stop drowning in email attachments.

Use SharePoint to export a list or form to Excel and share it with your team for reporting or analysis. The spreadsheet now automatically has a connection back to SharePoint. This means that users can easily see the latest data from Excel with a simple click. Read on to see how it works.

Export to Excel from SharePoint

First, use the Export to Excel function in SharePoint. Here’s how:

1.       Select an item in a SharePoint List

2.        Click on the Library tab and then select Export to Excel in the Ribbon

3.       A New Workbook will be created in Excel. You can save the spreadsheet and share the file with your team.

Keep your spreadsheet up-to-date

The added value of the Export to Excel function is that the spreadsheet maintains the connection back to SharePoint. A user can simply refresh the data connection in Excel and all of the SharePoint List data will be updated automatically with the latest data.

Here’s how:

  1. Open the spreadsheet in Excel.
  2. Go to the DATA tab in the ribbon.
  3. Click Refresh All to update the spreadsheet with all the latest data from SharePoint.

Want more tips and tricks for construction project management professionals?

Our new Tips from the Field series will feature handy information for construction project managers.

Subscribe to our newsletter to get our latest tips and tricks delivered directly your inbox.

PMIS the Right Way

How-To |

Considering a new Project Management Information Systems (PMIS) for your construction company?

We covered the reasons PMIS implementations typically fail in our previous blog post. Below we share the best practices we’ve learned implementing successful PMIS solutions for our clients over the years. Follow these tips and you will save yourself and your company headaches.

In a future blog post, we’ll explain why we believe Microsoft SharePoint, a tool already in place at most organizations, is the best PMIS option for many companies.

10 Tips for a Successful Project Management Information System Implementation

Do not go into an Enterprise PMIS rollout with your eyes shut.  A PMIS needs a scope, schedule and a budget just like any other construction project.  Buying software is the last step in the process not the first.  Do your homework and follow these best practices:

  1. Take stock of what you already have.  Analyze what you have before going out and buying an expensive new PMIS. You may find you already have tools that will meet your needs. For example, most companies already have Microsoft SharePoint. We have been able to provide 95% of our clients’ requirements by supplementing SharePoint with P6 and Arc GIS integration. Be sure to read our next blog article to learn why SharePoint may be the best PMIS solution for your company.
  2. Take time to do requirements.  We cannot say this emphatically enough. Taking time to do a proper requirements gathering phase is the difference between project success and failure. Requirements are independent of a PMIS, so spend the time defining your process before you even look at platforms. Understanding what your organization needs will make it easier to pick the right tool, prioritize features, avoid bloatware and ultimately save you time and money.
  3. Shoot High, Aim Low.  Plan the deployments so that functionality is released in phases.  The shotgun approach does not work.  Everybody on the project team is busy and their ability to change is very limited.  Small wins build support and foster empowerment of project teams.
  4. Assign Subject Matter Experts. A PMIS doesn’t build itself and magically know how you do things. You have to commit internal resources to collecting requirements, testing, training, encouraging adoption and managing feature requests. Establish a team of internal subject matter experts (SMEs) to support the PMIS throughout the entire Software Development Lifecycle (SDLC). These should be experienced SMEs from each discipline that will use the PMIS, not “newbies.”
  5. Get the right team.  Ensure your PMIS deployment is successful by entrusting the project to a team that is expert on the platform. Many construction companies hire one-off contractors or consultants from staffing agencies to develop functionality and deploy solutions. Companies do this because they believe they will save money. But hiring IT generalists often leads to wasted time, incomplete solutions and ballooning project costs. A PMIS solution should be deployed by a team that knows the product – both hardware and software – inside and out. Hire a team that specializes in PMIS deployments that can efficiently deliver a complete solution.
  6. Build grassroots support for your project.  PMIS systems are successful when users embrace them, not when they are forced to use them by upper management. You can increase adoption by ensuring the PMIS makes things easier and more efficient for all stakeholders. Otherwise you are just creating more work for other teams. Find internal champions in each department who know the business and can influence the rest of the organization. Work closely with them to gather their group’s requirements.
  7. Focus on core competencies.  A successful PMIS implementation requires the help of technology experts with experience deploying project management systems. You are probably thinking that this is convenient advice coming from a consulting company. That doesn’t make it any less true. You know your business backwards and forwards, but you are probably not an IT expert. Your internal IT department specializes in software and hardware refreshes. But they are not experienced with the intricacies of PMIS deployments. Bottom line: you will end up spending double the cost and tenfold the time trying to develop and roll out a PMIS without expert help.
  8. Embrace the cloud.  Companies spend millions maintaining data centers that will never keep up with the latest technology and security requirements.  We realize moving to the cloud seems daunting, But if you want to save cost, improve efficiency and foster innovation, your best bet is to get out from behind your firewall.
  9. Make your IT partner part of the construction team.  Your ideal IT partner should become part of your construction team to understand your business and your requirements. This will allow them to respond quickly to project needs and build solutions as they are needed.
  10. Expect to spend money.  Software is only part of the cost you need to consider. A bigger cost is the labor to develop and support your implementation.  A PMIS should be treated just like rolling out an enterprise financial system or CRM.  You don’t buy a system and then walk away. A successful long-term implementation requires careful initial planning followed by expert installation, maintenance, and support. Make sure your budget takes into account the full lifecycle of the PMIS.

Why Microsoft SharePoint is an ideal PMIS platform

In a future post, we’ll pull everything together to discuss why we recommend Microsoft SharePoint to our clients as the ideal foundation for a robust PMIS solution.

Excel as a Construction Project Management Tool

Reviews |

Learn how you can unleash Excel’s hidden superpowers for your construction project team

Excel is currently the mostly widely used project management tool for construction professionals. You may not want to hear that ­­– especially if you are a VP who just implemented a multi-million dollar project management system­­ – but it’s true. Many of those overseeing programs in your company turn to Excel to handle day-to-day project management and reporting. They do this even if you have multiple in-house project management software packages at their disposal.

The widespread reliance on Excel for construction project management might surprise some readers. After all, using Excel for real project management tasks used to be a taboo in the construction industry. But most, if not all, of the construction project managers we talk with admit that Excel is their go-to tool for multi-million – even billion – dollar projects because it is easy to use.

Pros & Cons of Using Excel for Construction Project Management

Excel is a flexible and easy project management tool because it is simple for project teams to update data and create and share reports. Collaboration is easy, since everyone has Excel and is familiar with the software. This is not the case with the limited licenses and steep learning curve of proprietary project management tools.

But the same construction professionals who told us they relied on Excel were also quick to complain about its shortcomings. The most frequently mentioned limitation on using Excel to manage projects include:

• Files are stored locally so sharing and backups becomes a problem

• Consistency and control across spreadsheets isn’t enforceable

• There are often user errors and version control issues

• Excel isn’t a database

It is a classic love/hate story. The construction project managers we meet love Excel for its simplicity and ease of use, but dislike where that simplicity causes the tool to come up short.

The thing is, Excel in recent years has gained some lesser known capabilities – we consider them superpowers really – that make it an even better construction project management tool than most realize.

Excel Power Maps
Power Maps

Power Pivot

Power View

 

 

Unleashing Excel’s Superpowers for Construction Project Management

The good news for our fellow Excel-loving construction industry professionals is that recent enhancements to Excel enable it to be a more effective construction management software package.

So what’s changed?  With the release of Microsoft’s Excel PowerPivot Add-in, which is already built into to Excel 2010 and 2013 and can be turned on from the COM Add-ins menu, Microsoft has morphed Excel into a relational DAX database.  Combined with SharePoint Power Pivot Services, the entire project team can connect, report and share Excel files across the organization.

Imagine the possibilities. You can take existing Excel cost reports and other tracking spreadsheets and use Excel Power Pivot and SharePoint Power Pivot Services to turn them into full blown web-enabled applications that leverage SharePoint permissions.  It’s now possible to connect Excel to many different data sources such as SharePoint Forms and Lists, SQL tables, Access tables, and even other Excel files.  No longer do you need to purchase or build an expensive custom coded solution. These capabilities turn Excel in a project management superhero for construction professionals.

And this evolution of Excel and SharePoint places the power of building and managing solutions in your hands.  Users can straight away leverage their existing Excel files that they know and love and immediately create robust cost management tools. No long and expensive development cycles, no coding and no complicated proprietary tools required.

Ready to unleash Excel’s superpowers for your team? All of these functions and more are incorporated into Lydon Solutions Program Management Portal for Construction. Contact us for your free demo or download our brochure.

AACE 2015 Annual Meeting Photos

Events |

Pictured: The Lydon Solutions Team — Andy Much, Lead SharePoint Developer; Victor Birky, SharePoint Developer; Melinda Michael, SharePoint Administrator; Paul Bedford, SharePoint Practice Director/Architect; and Rusty Ostboe, VP

Thanks to all of the attendees who stopped by our booth, presentation, and hospitality suite at this year’s AACE Annual Meeting in Las Vegas.  This was a great event, and we met a lot of engaging and accomplished construction owners and managers looking for better and easier ways to manage their projects.

Lydon Solutions was pleased to present our scalable project and information management solutions at the event. Visitors to our booth were able to see firsthand the powerful features of the Lydon Solutions Construction Program Management Portal, including custom dashboards, collaboration features, and easy reporting tools. Our integration with Oracle Primavera P6 generated quite a bit of buzz among construction professionals at the AACE event because it enables users to easily generate reports and interact with P6 data from within the Microsoft SharePoint portal. This provides a much needed option for teams to collaborate and run key reports outside of P6 – a definite time and cost saver! Read our “Oracle Primavera P6 hosting & SharePoint integration” blog post to learn more.

We also showed off a web-enabled Excel solution combining the rich functionality of the Excel Power Pivot add-on with the power of Microsoft SharePoint. This demo was a big hit with many AACE attendees because it demonstrated a simple way to use existing Excel cost reports, connect them to different data sources (including other Excel files), and utilize these reports as a robust cost-tracking application that is web-enabled. In fact, this demonstration was so popular, we will have more to say about web-enabling Excel to manage major projects in an upcoming blog post.

In the meantime, if you missed this year’s meeting or are interested in learning more about our products and services, make sure to contact us to schedule your free consultation.

Here are a few more photos from the AACE Annual Meeting.

AACE Annual Meeting
Paul Bedford, SharePoint Practice Director/Architect; Jeff Lydon, President of Lydon Solutions

Melinda Michael, SharePoint Administrator
Microsoft SharePoint for Construction

SharePoint Construction Management Portal
Andy Much, Lead SharePoint Developer